SQL Server 2016 JSON 형식을 일반 ROW 형식으로 반환하기
· Version : SQL Server 2016
SQL Server 2016 부터 지원되는 JSON 형식은 쿼리 결과를 JSON 형식으로 반환 할 수 있으며 JSON 형식의 데이터를 일반 ROW로 데이터를 반환할 수도 있다. 이번 포스트에서는 JSON 형식의 데이터를 일반 ROW형식으로 반환하는 방법에 대해서 알아본다.
JSON 형식의 텍스트를 구문 분석하여 일반 ROW 형식으로 데이터를 반환하는 함수는 OPENJSON 이다. OPENJSON 함수를 사용하여 JSON 컬렉션을 행 집합으로 변환한 후 데이터에 대해서 SQL 쿼리를 실행하거나 SQL Server 테이블에 삽입할 수 있다.
OPEN JSON 함수는 단일 JSON 개체 또는 JSON 개체 컬렉션을 받아서 하나 또는 여러 개의 행으로 변환한다. 기본적으로 OPENJSON 함수는 다음 데이터를 반환한다.
· JSON 개체에서 이 함수는 첫 번쨰 수준에서 발견된 모든 키:값 쌍을 반환한다.
· JSON 배열에서 이 함수는 모든 요소 및 해당 인덱스를 반환한다.
WITH 옵션을 사용하여 열을 명시적으로 지정할 수 있다.
[기본 구조를 사용하는 OPENJSON]
결과에 대해 명시적으로 스키마를 제공하지 않는다.
DECLARE @json NVARCHAR(MAX)
SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}';
SELECT * FROM OPENJSON(@json); |
[명시적 구조를 사용하는 OPENJSON]
OPENJSON 함수의 WITH절을 함께 사용하여 결과의 스키마를 지정하여 WITH에 포함된 열만 반환한다.
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' ) |
[참고자료]
2017-10-17 / 강성욱 / http://sqlmvp.kr
SQL Server 2016, JSON format, OPENJSON
