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

적용 대상: 예SQL Server 2016(13.x) 이상 예Azure SQL Database 예Azure SQL Managed Instance 예Azure Synapse Analytics

OPENJSON 행 집합 함수는 JSON 텍스트를 일련의 행과 열로 변환합니다. OPENJSON 을 사용하여 JSON 컬렉션을 행 집합으로 변환한 후 반환된 데이터에 대해 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);

결과

key type
name John 1
surname Doe 1
age 45 2
기술 ["SQL","C#","MVC"] 4

기본 스키마를 사용하는 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'  
 ) 

결과

Number Date Customer 수량
SO43659 2011-05-31T00:00:00 AW29825 1
SO43661 2011-06-01T00:00:00 AW73565 3

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

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

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

명시적 스키마를 사용하는 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 비디오

참고

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

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

참고 항목

OPENJSON(Transact-SQL)