[SQL2016] OPENJSON 사용 예

/*******************************************************************************************************************
-- Title : [SQL2016] OPENJSON 사용 예
-- Reference : micsrosoft.com
-- Key word : json openjson sql
*******************************************************************************************************************/

-- 링크
    * https://msdn.microsoft.com/ko-kr/library/dn921885.aspx

-- SQL
-- https://msdn.microsoft.com/ko-kr/library/dn921885.aspx /******************************* -- 개체의 각 속성을 반환 *******************************/ DECLARE @json NVARCHAR(MAX) SET @json=N'{ "StringValue":"John", "IntValue":45, "TrueValue":true, "FalseValue":false, "NullValue":null, "ArrayValue":["a","r","r","a","y"], "ObjectValue":{"obj":"ect"} }' SELECT * FROM OPENJSON(@json) /******************************* -- 중첩 된 개체를 지정 하 여 반환 된 경로 *******************************/ DECLARE @json NVARCHAR(MAX) SET @json=N'{ "path": { "to":{ "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"] } } }'; SELECT [key],value FROM OPENJSON(@json,'$.path.to."sub-object"') /******************************* -- OPENJSON 함수에서 반환 된 행의 스키마를 명시적으로 지정 *******************************/ DECLARE @json NVARCHAR(MAX) SET @json = N'{"Orders": {"OrdersArray": [ { "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, '$.Orders.OrdersArray' ) WITH ( Number varchar(200) '$.Order.Number', Date datetime '$.Order.Date', Customer varchar(200) '$.AccountNumber', Quantity int '$.Item.Quantity', [Order] nvarchar(MAX) AS JSON ) /******************************* -- 두 JSON 개체에서 속성을 병합 *******************************/ DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX) SET @json1=N'{"name": "John", "surname":"Doe"}' SET @json2=N'{"name": "John", "age":45}' SELECT * FROM OPENJSON(@json1) UNION ALL SELECT * FROM OPENJSON(@json2) WHERE [key] NOT IN(SELECT [key] FROM OPENJSON(@json1))