Microsoft SQL Server
OPENJSON

키 가져 오기 : JSON 텍스트의 값 쌍

OPENJSON 함수는 JSON 텍스트를 구문 분석하고 JSON의 첫 번째 레벨에서 모든 키 : 값 쌍을 반환합니다.

declare @json NVARCHAR(4000) = N'{"Name":"Joe","age":27,"skills":["C#","SQL"]}';
SELECT * FROM OPENJSON(@json);

열 유형은 널 (0), 문자열 (1), 숫자 (2), 부울 (3), 배열 (4) 및 오브젝트 (5)와 같은 값의 유형을 설명합니다.

JSON 배열을 행 집합으로 변환

OPENJSON 함수는 JSON 객체 컬렉션을 파싱하고 JSON 텍스트의 값을 행 세트로 반환합니다.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","Customer": "MSFT","Price":59.99,"Quantity":1},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","Customer":"Nokia","Price":24.99,"Quantity":3}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Customer varchar(200),
          Quantity int
  )

WITH 절에는 OPENJSON 함수의 반환 스키마가 지정됩니다. JSON 객체의 키는 열 이름으로 가져옵니다. JSON의 일부 키가 WITH 절에 지정되지 않은 경우 (예 :이 예에서는 Price) 무시됩니다. 값은 자동으로 지정된 유형으로 변환됩니다.

중첩 된 JSON 필드를 행 집합으로 변환

OPENJSON 함수는 JSON 객체 컬렉션을 파싱하고 JSON 텍스트의 값을 행 세트로 반환합니다. 입력 개체의 값이 중첩되어 있으면 WITH 절의 각 열에 추가 매핑 매개 변수를 지정할 수 있습니다.

declare @json nvarchar(4000) = N'[
  {"data":{"num":"SO43659","date":"2011-05-31T00:00:00"},"info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"data":{"number":"SO43661","date":"2011-06-01T00:00:00"},"info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200) '$.data.num',
          Date     datetime '$.data.date',
          Customer varchar(200) '$.info.customer',
          Quantity int '$.info.qty',
  )

WITH 절에는 OPENJSON 함수의 반환 스키마가 지정됩니다. type이 반환 된 값을 찾아야하는 JSON 노드에 대한 지정된 경로입니다. JSON 객체의 키는 이러한 경로에 의해 반입됩니다. 값은 자동으로 지정된 유형으로 변환됩니다.

내부 JSON 하위 객체 추출하기

OPENJSON은 JSON 텍스트 내에서 JSON 객체 조각을 추출 할 수 있습니다. JSON 하위 객체를 참조하는 열 정의에서 nvarchar (max) 유형과 AS JSON 옵션을 설정합니다.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00","info":{"customer":"MSFT","Price":59.99,"qty":1}},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00","info":{"customer":"Nokia","Price":24.99,"qty":3}}
]'

SELECT    * 
FROM OPENJSON (@json)
    WITH (
          Number   varchar(200),
          Date     datetime,
          Info nvarchar(max) '$.info' AS JSON
  )

정보 열은 "정보"개체에 매핑됩니다. 결과는 다음과 같습니다.

중첩 된 JSON 하위 배열을 사용한 작업

JSON은 내부 배열과 복잡한 구조를 가질 수 있습니다. 이 예에서는 OrderItems의 중첩 된 하위 배열이있는 배열을 갖습니다.

declare @json nvarchar(4000) = N'[
  {"Number":"SO43659","Date":"2011-05-31T00:00:00",
    "Items":[{"Price":11.99,"Quantity":1},{"Price":12.99,"Quantity":5}]},
  {"Number":"SO43661","Date":"2011-06-01T00:00:00",
    "Items":[{"Price":21.99,"Quantity":3},{"Price":22.99,"Quantity":2},{"Price":23.99,"Quantity":2}]}
]'

우리는 OPENJSON을 사용하여 루트 레벨 속성을 파싱 할 수 있습니다. 그러면 항목 배열 AS JSON 부분이 반환됩니다. 그런 다음 Items 배열에 OPENJSON을 다시 적용하고 내부 JSON 테이블을 열 수 있습니다. 첫 번째 수준의 테이블과 내부 테이블은 표준 테이블 간의 JOIN과 같이 "조인"됩니다.

SELECT    * 
FROM
    OPENJSON (@json)
    WITH (  Number varchar(200), Date datetime,
            Items nvarchar(max) AS JSON )
        CROSS APPLY 
            OPENJSON (Items)
            WITH ( Price float, Quantity int)

결과 :