SQL Server에서 JSON과 관련된 일반적인 문제 해결

  • 아티클

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics(서버리스 SQL 풀에만 해당)

SQL Server의 기본 제공 JSON 지원에 대한 몇 가지 일반적인 질문에 대한 답변을 여기에서 찾을 수 있습니다.

FOR JSON 및 JSON 출력

FOR JSON PATH 또는 FOR JSON AUTO?

질문. 단일 테이블의 간단한 SQL 쿼리에서 JSON 텍스트 결과를 만들려고 합니다. FOR JSON PATH 및 FOR JSON AUTO는 동일한 출력을 생성합니다. 이러한 두 옵션 중 어느 옵션을 사용해야 하나요?

대답. FOR JSON PATH를 사용합니다. JSON 출력에는 차이가 없지만 AUTO 모드는 열을 중첩해야 하는지 여부를 검사 몇 가지 추가 논리를 적용합니다. PATH를 기본 옵션으로 고려합니다.

중첩된 JSON 구조 만들기

질문. 동일한 수준에서 여러 배열을 사용하여 복잡한 JSON을 생성하려고 합니다. FOR JSON PATH는 경로를 사용하여 중첩된 개체를 만들고 FOR JSON AUTO는 각 테이블에 대한 추가 중첩 수준을 만듭니다. 이 두 옵션 중 하나도 원하는 출력을 생성할 수 없는 경우 기존 옵션이 직접 지원하지 않는 사용자 지정 JSON 형식을 만들려면 어떻게 해야 하나요?

대답. FOR JSON 쿼리를 JSON 텍스트를 반환하는 열 식으로 추가하여 모든 데이터 구조를 만들 수 있습니다. JSON_QUERY 함수를 사용하여 JSON을 수동으로 만들 수도 있습니다. 다음 예제에서는 이러한 기술을 보여 줍니다.

SELECT col1, col2, col3,  
     (SELECT col11, col12, col13 FROM t11 WHERE t11.FK = t1.PK FOR JSON PATH) as t11,  
     (SELECT col21, col22, col23 FROM t21 WHERE t21.FK = t1.PK FOR JSON PATH) as t21,  
     (SELECT col31, col32, col33 FROM t31 WHERE t31.FK = t1.PK FOR JSON PATH) as t31,  
     JSON_QUERY('{"'+col4+'":"'+col5+'"}') as t41  
FROM t1  
FOR JSON PATH

FOR JSON 쿼리 또는 열 식의 JSON_QUERY 함수의 모든 결과는 별도의 중첩된 JSON 하위 개체로 형식이 지정되고 기본 결과에 포함됩니다.

FOR JSON 출력에서 이중 이스케이프 JSON 방지

질문. 테이블 열에 JSON 텍스트가 저장되어 있습니다. 이것을 FOR JSON의 출력에 포함하려고 합니다. 하지만 다음 예제에서 보이는 것과 같이 FOR JSON은 JSON에서 모든 문자를 이스케이프하므로 중첩 개체 대신 JSON 문자열이 반환됩니다.

SELECT 'Text' AS myText, '{"day":23}' AS myJson  
FOR JSON PATH

이 쿼리는 다음 출력을 생성합니다.

[{"myText":"Text", "myJson":"{\"day\":23}"}]

이 동작을 방지하려면 어떻게 해야 하나요? 이스케이프된 텍스트가 아닌 JSON 개체로 반환하려고 {"day":23} 합니다.

대답. 텍스트 열 또는 리터럴에 저장된 JSON은 텍스트처럼 처리됩니다. 즉, 큰따옴표로 둘러싸여 이스케이프됩니다. 이스케이프되지 않은 JSON 개체를 반환하려면 다음 예제와 같이 JSON 열을 인수로 JSON_QUERY 함수에 전달합니다.

SELECT col1, col2, col3, JSON_QUERY(jsoncol1) AS jsoncol1  
FROM tab1  
FOR JSON PATH

선택적인 두 번째 매개 변수가 없는 JSON_QUERY는 첫 번째 인수만 결과로 반환합니다. JSON_QUERY는 항상 유효한 JSON을 반환하므로 FOR JSON은 이 결과가 이스케이프될 필요가 없다고 인식합니다.

WITHOUT_ARRAY_WRAPPER 절로 생성된 JSON은 FOR JSON 출력에서 이스케이프됩니다.

질문. FOR JSON 및 WITHOUT_ARRAY_WRAPPER 옵션을 사용하여 열 식의 형식을 지정하려고 합니다.

SELECT 'Text' as myText,  
   (SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) as myJson  
FOR JSON PATH

FOR JSON 쿼리에서 반환된 텍스트가 일반 텍스트로 이스케이프된 것 같습니다. WITHOUT_ARRAY_WRAPPER 지정된 경우에만 발생합니다. 왜 JSON 개체로 처리되지 않고 결과에서 이스케이프되지 않나요?

대답. 내부 FOR JSON옵션을 지정 WITHOUT_ARRAY_WRAPPER 하는 경우 결과 JSON 텍스트가 반드시 유효한 JSON은 아닙니다. 따라서 외부 FOR JSON 는 일반 텍스트라고 가정하고 문자열을 이스케이프합니다. JSON 출력이 유효하다고 확신하는 경우 다음 예제와 같이 함수로 JSON_QUERY 래핑하여 올바르게 서식이 지정된 JSON으로 승격합니다.

SELECT 'Text' as myText,  
      JSON_QUERY((SELECT 12 day, 8 mon FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) as myJson  
FOR JSON PATH

OPENJSON 및 JSON 입력

OPENJSON을 사용하여 JSON 텍스트에서 중첩된 JSON 하위 개체 반환

질문. 명시적 스키마가 있는 OPENJSON을 사용하여 두 스칼라 값, 개체 및 배열을 포함하는 복합 JSON 개체 배열을 열 수 없습니다. WITH 절에서 키를 참조하면 스칼라 값만 반환됩니다. 개체 및 배열은 null 값으로 반환됩니다. 개체 또는 배열을 JSON 개체로 추출하는 방법

대답. 개체 또는 배열을 열로 반환하려면 다음 예제와 같이 열 정의에서 AS JSON 옵션을 사용합니다.

SELECT scalar1, scalar2, obj1, obj2, arr1  
FROM OPENJSON(@json)  
    WITH ( scalar1 int,  
        scalar2 datetime2,  
        obj1 NVARCHAR(MAX) AS JSON,  
        obj2 NVARCHAR(MAX) AS JSON,  
        arr1 NVARCHAR(MAX) AS JSON)

JSON_VALUE 대신 OPENJSON을 사용하여 긴 텍스트 값을 반환합니다.

질문. JSON 텍스트에 긴 텍스트가 포함된 설명 키가 있습니다. JSON_VALUE(@json, '$.description') 는 값 대신 NULL을 반환합니다.

대답. JSON_VALUE 작은 스칼라 값을 반환하도록 설계되었습니다. 일반적으로 함수는 오버플로 오류 대신 NULL을 반환합니다. 더 긴 값을 반환하려면 다음 예제와 같이 NVARCHAR(MAX) 값을 지원하는 OPENJSON을 사용합니다.

SELECT myText FROM OPENJSON(@json) WITH (myText NVARCHAR(MAX) '$.description')

JSON_VALUE 대신 OPENJSON을 사용하여 중복 키 처리

질문. JSON 텍스트에 중복 키가 있습니다. JSON_VALUE 경로에 있는 첫 번째 키만 반환합니다. 이름이 같은 모든 키를 반환하는 방법은 무엇입니까?

대답. 기본 제공 JSON 스칼라 함수는 참조된 개체의 첫 번째 일치 항목만 반환합니다. 키가 두 개 이상 필요한 경우 다음 예제와 같이 OPENJSON 테이블 반환 함수를 사용합니다.

SELECT value FROM OPENJSON(@json, '$.info.settings')  
WHERE [key] = 'color'

OPENJSON에는 호환성 수준 130이 필요합니다.

질문. SQL Server 2016에서 OPENJSON을 실행하려고 했는데 다음 오류가 발생합니다.

Msg 208, Level 16, State 1 'Invalid object name OPENJSON'

대답. OPENJSON 함수는 호환성 수준 130에서만 사용할 수 있습니다. DB 호환성 수준이 130보다 낮으면 OPENJSON이 숨겨집니다. 다른 JSON 함수는 모든 호환성 수준에서 사용할 수 있습니다.

기타 질문

JSON 텍스트에 영숫자가 아닌 문자가 포함된 참조 키

질문. 내 JSON 텍스트의 키에 영숫자가 아닌 문자가 있습니다. 이러한 속성은 어떻게 참조할 수 있나요?

대답. JSON 경로에서 따옴표로 묶어야 합니다. 예: JSON_VALUE(@json, '$."$info"."First Name".value').

SQL Server 및 Azure SQL Database의 JSON에 대해 자세히 알아보기

Microsoft 비디오

참고 항목

이 섹션의 일부 비디오 링크는 현재 작동하지 않을 수 있습니다. Microsoft는 이전에 Channel 9에 있던 콘텐츠를 새 플랫폼으로 마이그레이션하고 있습니다. 비디오가 새 플랫폼으로 마이그레이션되면 링크를 업데이트할 예정입니다.

SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 시각적 소개는 다음 비디오를 참조하세요.