OPENJSON을 사용하여 JSON 데이터 구문 분석 및 변환

적용 대상: SQL Server 2016(13.x) 이상 Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

OPENJSON 행 집합 함수는 JSON 텍스트를 행 및 열 집합으로 변환합니다. JSON 컬렉션을 OPENJSON을 사용하여 행 집합으로 변환한 후 반환된 데이터에 대해 SQL 쿼리를 실행하거나 SQL Server 테이블에 삽입할 수 있습니다.

OPENJSON 함수는 단일 JSON 개체 또는 JSON 개체 컬렉션을 사용하여 하나 이상의 행으로 변환합니다. 기본적으로 OPENJSON 함수는 다음 데이터를 반환합니다.

• JSON 개체에서 함수는 첫 번째 수준에서 찾은 모든 키/값 쌍을 반환합니다.
• JSON 배열에서 이 함수는 모든 요소 및 해당 인덱스를 반환합니다.

선택적 WITH 절을 추가하여 출력의 구조를 명시적으로 정의하는 스키마를 제공할 수 있습니다.

옵션 1 - 기본 출력이 있는 OPENJSON

결과에 대한 명시적 스키마를 제공하지 않고, 즉 OPENJSON 뒤에 WITH 절 없이 OPENJSON 함수를 사용하는 경우 함수에서 다음 세 개의 열이 있는 테이블을 반환합니다.

1. 입력 개체의 속성 이름(또는 입력 배열에 있는 요소의 인덱스)입니다.
2. 속성 또는 배열 요소의 값입니다.
3. 형식(예: 문자열, 숫자, 부울, 배열 또는 개체)

OPENJSON 은 JSON 개체의 각 속성 또는 배열의 각 요소를 별도의 행으로 반환합니다.

다음은 기본 스키마(선택적 WITH 절 없이)와 함께 OPENJSON을 사용하고 JSON 개체의 각 속성에 대해 하나의 행을 반환하는 빠른 예제입니다.

예제:

DECLARE @json NVARCHAR(MAX)

SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';

SELECT *
FROM OPENJSON(@json);

Results:

기본 스키마를 사용하는 OPENJSON에 대한 추가 정보

자세한 정보 및 예제는 기본 스키마와 함께 OPENJSON 사용(SQL Server)을 참조하세요.

구문 및 사용법은 OPENJSON(Transact-SQL)을 참조하세요.

옵션 2 - 명시적 구조가 있는 OPENJSON 출력

OPENJSON 함수의 WITH 절을 사용하여 결과에 대한 스키마를 지정하면 함수는 WITH 절에서 정의한 열만 있는 테이블을 반환합니다. 선택적 WITH 절에 출력 열 집합, 해당 형식 및 각 출력 값에 대한 JSON 원본 속성의 경로를 지정합니다. OPENJSON 은 JSON 개체의 배열을 반복하고, 각 열에 대해 지정된 경로의 값을 읽고, 값을 지정된 형식으로 변환합니다.

다음은 WITH 절에서 명시적으로 지정하는 출력에 대한 스키마와 함께 OPENJSON을 사용하는 빠른 예제입니다.

예제:

DECLARE @json NVARCHAR(MAX)
SET @json =   
  N'[  
       {  
         "Order": {  
           "Number":"SO43659",  
           "Date":"2011-05-31T00:00:00"  
         },  
         "AccountNumber":"AW29825",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":1  
         }  
       },  
       {  
         "Order": {  
           "Number":"SO43661",  
           "Date":"2011-06-01T00:00:00"  
         },  
         "AccountNumber":"AW73565",  
         "Item": {  
           "Price":2024.9940,  
           "Quantity":3  
         }  
      }  
 ]'  
   
SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              Number   varchar(200) '$.Order.Number' ,  
              Date     datetime     '$.Order.Date',  
              Customer varchar(200) '$.AccountNumber',  
              Quantity int          '$.Item.Quantity'  
 ) 

Results:

이 함수는 JSON 배열의 요소를 반환하고 형식을 지정합니다.

• OPENJSON 은 출력 테이블에 JSON 배열의 각 요소에 대한 새 행을 생성합니다. JSON 배열의 두 요소는 반환된 테이블의 두 행으로 변환됩니다.

• 구문을 사용하여 colName type json_path 지정된 각 열에 대해 OPENJSON은 지정된 경로의 각 배열 요소에 있는 값을 지정된 형식으로 변환합니다. 이 예제에서 열의 값은 경로 $.Order.Date 의 Date 각 요소에서 가져와 날짜/시간 값으로 변환됩니다.

명시적 스키마를 사용하는 OPENJSON에 대한 자세한 정보

자세한 정보 및 예제는 명시적 스키마와 함께 OPENJSON 사용(SQL Server)을 참조하세요.

구문 및 사용법은 OPENJSON(Transact-SQL)을 참조하세요.

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

OPENJSON 함수는 호환성 수준 130에서만 사용할 수 있습니다. 데이터베이스 호환성 수준이 130보다 낮으면 SQL Server에서 OPENJSON 함수를 찾아 실행할 수 없습니다. 다른 기본 제공 JSON 함수는 모든 호환성 수준에서 사용할 수 있습니다.

sys.databases 뷰 또는 데이터베이스 속성에서 호환성 수준을 확인할 수 있습니다.

다음 명령을 사용하여 데이터베이스의 호환성 수준을 변경할 수 있습니다.
ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130

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

Microsoft 비디오

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

• NoSQL과 관계형 세계 간의 브리지로서의 JSON

참고 항목

• OPENJSON(Transact-SQL)