SQL Server에서 JSON으로 일반적인 문제 해결Solve common issues with JSON in SQL Server

이 항목은 다음에 적용됩니다. 예SQL Server(2016부터)예Azure SQL Database아니요Azure SQL Data Warehouse아니요병렬 데이터 웨어하우스 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

JSON SQL Server에서 기본으로 제공되는 JSON 지원에 대한 일반적인 질문에 대한 대답을 여기에서 확인하십시오.Find answers here to some common questions about the built-in JSON support in SQL Server.

FOR JSON 및 JSON 출력FOR JSON and JSON output

FOR JSON PATH 또는 FOR JSON AUTO?FOR JSON PATH or FOR JSON AUTO?

질문.Question. 단일 테이블에서 수행한 간단한 SQL 쿼리의 JSON 텍스트 결과를 만들려고 합니다.I want to create a JSON text result from a simple SQL query on a single table. FOR JSON PATH 및 FOR JSON AUTO는 동일한 출력을 제공합니다.FOR JSON PATH and FOR JSON AUTO produce the same output. 이러한 두 옵션 중 어느 옵션을 사용해야 하나요?Which of these two options should I use?

대답.Answer. FOR JSON PATH 사용Use FOR JSON PATH. JSON 출력에는 아무런 차이가 없지만 AUTO 모드에서는 열이 중첩되어야 하는지를 확인하는 일부 추가 논리를 적용합니다.Although there is no difference in the JSON output, AUTO mode applies some additional logic that checks whether columns should be nested. PATH를 기본 옵션으로 고려합니다.Consider PATH the default option.

중첩된 JSON 구조 만들기Create a nested JSON structure

질문.Question. 동일한 수준에 여러 배열이 있는 복합 JSON을 작성하려고 합니다.I want to produce complex JSON with several arrays on the same level. FOR JSON PATH는 경로를 사용하여 중첩된 개체를 만들고 FOR JSON AUTO는 각 테이블에 대한 추가 중첩 수준을 만듭니다.FOR JSON PATH can create nested objects using paths, and FOR JSON AUTO creates additional nesting level for each table. 이러한 두 옵션 모두 원하는 출력을 생성할 수 없습니다.Neither one of these two options lets me generate the output I want. 기존 옵션에서 직접 지원되지 않는 사용자 지정 JSON 형식을 어떻게 만들 수 있나요?How can I create a custom JSON format that the existing options don't directly support?

대답.Answer. JSON 텍스트를 반환하는 FOR JSON 쿼리를 열 식으로 추가하여 데이터 구조를 만들 수 있습니다.You can create any data structure by adding FOR JSON queries as column expressions that return JSON text. JSON_QUERY 함수를 사용하여 JSON을 수동으로 만들 수도 있습니다.You can also create JSON manually by using the JSON_QUERY function. 다음 예제에서는 이러한 방법을 보여 줍니다.The the following example demonstrates these techniques.

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 하위 개체로 형식이 지정되고 기본 결과에 포함됩니다.Every result of a FOR JSON query or the JSON_QUERY function in the column expressions is formatted as a separate nested JSON sub-object and included in the main result.

FOR JSON 출력에서 이중 이스케이프 JSON 방지Prevent double-escaped JSON in FOR JSON output

질문.Question. 텍스트 열에 저장된 JSON 텍스트가 있습니다.I have JSON text stored in a table column. 이것을 FOR JSON의 출력에 포함하려고 합니다.I want to include it in the output of FOR JSON. 하지만 다음 예제에서 보이는 것과 같이 FOR JSON은 JSON에서 모든 문자를 이스케이프하므로 중첩 개체 대신 JSON 문자열이 반환됩니다.But FOR JSON escapes all characters in the JSON, so I’m getting a JSON string instead of a nested object, as shown in the following example.

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

이 쿼리는 다음 출력을 생성합니다.This query produces the following output.

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

이 문제를 어떻게 방지할 수 있나요?How can I prevent this behavior? {"day":23} 을(를) 이스케이프된 텍스트가 아닌 JSON 개체로 반환하고 싶습니다.I want {"day":23} to be returned as a JSON object and not as escaped text.

대답.Answer. 텍스트 열 또는 리터럴에 저장된 JSON은 텍스트로 처리됩니다.JSON stored in a text column or a literal is treated like any text. 즉, 큰따옴표로 묶여 이스케이프됩니다.That is, it's surrounded with double quotes and escaped. 이스케이프되지 않은 JSON 개체를 반환하려는 경우 다음 예제에서 보이는 것과 같이 JSON 열을 JSON_QUERY 함수의 인수로 전달합니다.If you want to return an unescaped JSON object, pass the JSON column as an argument to the JSON_QUERY function, as shown in the following example.

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

선택적인 두 번째 매개 변수가 없는 JSON_QUERY는 첫 번째 인수만 결과로 반환합니다.JSON_QUERY without its optional second parameter returns only the first argument as a result. JSON_QUERY는 항상 유효한 JSON을 반환하므로 FOR JSON은 이 결과가 이스케이프되지 않아야 한다는 것을 알고 있습니다.Since JSON_QUERY always returns valid JSON, FOR JSON knows that this result does not have to be escaped.

WITHOUT_ARRAY_WRAPPER 절로 생성된 JSON은 FOR JSON 출력에서 이스케이프됨JSON generated with the WITHOUT_ARRAY_WRAPPER clause is escaped in FOR JSON output

질문.Question. FOR JSON 및 WITHOUT_ARRAY_WRAPPER 옵션을 사용하여 열 식을 포맷하려고 합니다.I’m trying to format a column expression by using FOR JSON and the WITHOUT_ARRAY_WRAPPER option.

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

FOR JSON 쿼리에서 반환된 텍스트가 일반 텍스트로 이스케이프된 것 같습니다.It seems that the text returned by the FOR JSON query is escaped as plain text. 이 문제는 WITHOUT_ARRAY_WRAPPER가 지정된 경우에만 발생합니다.This happens only if WITHOUT_ARRAY_WRAPPER is specified. 왜 JSON 개체로 처리되지 않고 결과에서 이스케이프되지 않나요?Why isn't it treated as a JSON object and included unescaped in the result?

대답.Answer. 내부 FOR JSONWITHOUT_ARRAY_WRAPPER 옵션을 지정하는 경우 결과 JSON 텍스트가 유효한 JSON이 아닐 수 있습니다.If you specify the WITHOUT_ARRAY_WRAPPER option in the inner FOR JSON, the resulting JSON text is not necessarily valid JSON. 따라서 외부 FOR JSON은 이것을 일반 텍스트로 간주하고 문자열을 이스케이프합니다.Therefore the outer FOR JSON assumes that this is plain text and escapes the string. 다음 예제에서 보이는 것과 같이 JSON 출력이 유효한 경우 JSON_QUERY 함수로 래핑하여 적절한 형식의 JSON으로 수준을 올립니다.If you are sure that the JSON output is valid, wrap it with the JSON_QUERY function to promote it to properly formatted JSON, as shown in the following example.

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 and JSON input

OPENJSON로 JSON 텍스트에서 중첩된 JSON 하위 개체 반환Return a nested JSON sub-object from JSON text with OPENJSON

질문.Question. 명시적 스키마가 있는 OPENJSON을 사용하여 두 스칼라 값, 개체 및 배열을 포함하는 복합 JSON 개체 배열을 열 수 없습니다.I can't open an array of complex JSON objects that contains both scalar values, objects, and arrays using OPENJSON with an explicit schema. WITH 절에서 키를 참조하는 경우 스칼라 값만 반환됩니다.When I reference a key in the WITH clause, only scalar values are returned. 개체와 배열이 NULL 값으로 반환됩니다.Objects and arrays are returned as null values. 개체 또는 배열을 JSON 개체로 어떻게 추출하나요?How can I extract objects or arrays as JSON objects?

대답.Answer. 개체 또는 배열을 열로 반환하려면 다음 예제에서 보이는 것과 같이 열 정의에서 AS JSON 옵션을 사용합니다.If you want to return an object or an array as a column, use the AS JSON option in the column definition, as shown in the following example.

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을 사용하여 긴 텍스트 값 반환Return long text value with OPENJSON instead of JSON_VALUE

질문.Question. 긴 텍스트를 포함하는 JSON 텍스트에 설명 키가 있습니다.I have description key in JSON text that contains long text. JSON_VALUE(@json, '$.description') 이(가) 값 대신 NULL을 반환합니다.JSON_VALUE(@json, '$.description') returns NULL instead of a value.

대답.Answer. JSON_VALUE는 작은 스칼라 값을 반환하도록 설계되었습니다.JSON_VALUE is designed to return small scalar values. 일반적으로 함수는 오버플로 오류가 아닌 NULL을 반환합니다.Generally the function returns NULL instead of an overflow error. 긴 값을 반환하려면 다음 예제에서와 같이 NVARCHAR(MAX) 값을 지원하는 OPENJSON을 사용하십시오.If you want to return longer values, use OPENJSON, which supports NVARCHAR(MAX) values, as shown in the following example.

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

JSON_VALUE 대신 OPENJSON을 사용하여 중복 키 처리Handle duplicate keys with OPENJSON instead of JSON_VALUE

질문.Question. JSON 텍스트에 중복 키가 있습니다.I have duplicate keys in the JSON text. JSON_VALUE는 경로에서 발견된 첫 번째 키만 반환합니다.JSON_VALUE returns only the first key found on the path. 동일한 이름을 가진 모든 키를 어떻게 반환해야 하나요?How can I return all keys that have the same name?

대답.Answer. 기본 제공 JSON 스칼라 함수는 참조된 개체의 첫 번째 일치 항목만 반환합니다.The built-in JSON scalar functions return only the first occurrence of the referenced object. 다음 예제에서 보이는 것과 같이 둘 이상의 키가 필요한 경우 OPENJSON 테이블 반환 함수를 사용하십시오.If you need more than one key, use the OPENJSON table-valued function, as shown in the following example.

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

OPENJSON은 호환성 수준 130을 필요로 함OPENJSON requires compatibility level 130

질문.Question. SQL Server 2016에서 OPENJSON을 실행하려는 데 다음 오류가 발생했습니다.I’m trying to run OPENJSON in SQL Server 2016 and I’m getting the following error.

Msg 208, Level 16, State 1 ‘Invalid object name OPENJSON’

대답.Answer. OPENJSON 함수는 호환성 수준 130에서만 사용할 수 있습니다.The OPENJSON function is available only under compatibility level 130. DB 호환성 수준이 130보다 낮은 경우 OPENJSON은 숨겨집니다.If your DB compatibility level is lower than 130, OPENJSON is hidden. 다른 JSON 함수는 모든 호환성 수준에서 사용할 수 있습니다.Other JSON functions are available at all compatibility levels.

기타 질문Other questions

JSON 텍스트에 영숫자가 아닌 문자가 포함된 참조 키Reference keys that contain non-alphanumeric characters in JSON text

질문.Question. JSON 텍스트의 키에 영숫자가 아닌 문자가 포함되었습니다.I have non-alphanumeric characters in keys in my JSON text. 이러한 속성을 어떻게 참조할 수 있나요?How can I reference these properties?

대답.Answer. JSON 경로에서 따옴표로 묶어야 합니다.You have to surround them with quotes in JSON paths. JSON_VALUE(@json, '$."$info"."First Name".value'))을 입력합니다.For example, JSON_VALUE(@json, '$."$info"."First Name".value').

SQL Server의 기본 제공 JSON 지원에 대한 자세한 정보Learn more about the built-in JSON support in SQL Server

많은 특정 솔루션, 사용 사례 및 권장 사항은 Microsoft 프로그램 관리자인 Jovan Popovic이 제공하는 SQL Server 및 Azure SQL Database의 기본 제공 JSON 지원에 대한 블로그 게시물을 참조하세요.For lots of specific solutions, use cases, and recommendations, see the blog posts about the built-in JSON support in SQL Server and in Azure SQL Database by Microsoft Program Manager Jovan Popovic.