[SQL2016] JSON 기본 사용법 및 NULL 출력

/*********************************************************************************************************
-- Title : [SQL2016] JSON 기본 사용법 및 NULL 출력
-- Reference : blogs.technet.microsoft.com/dataplatforminsider
-- Key word : for json path openjson include_null_values json_value json_query json value json query
*********************************************************************************************************/

-- JSON Function

-- CROSS/OUTER APPLY 활용
    * https://msdn.microsoft.com/ko-kr/library/dn921885.aspx

-- SQL

/******************************* -- JSON 조회 *******************************/ DECLARE @json NVARCHAR(4000) SET @json = N'{ "info":{ "type":1, "address":{ "town" :"Bristol", "county" :"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }'; SELECT JSON_VALUE(@json, '$.type') as type, JSON_VALUE(@json, '$.info.address.town') as town, JSON_VALUE(@json, '$.info.address.country') as ctry, JSON_QUERY(@json, '$.info.tags') as tags; SELECT value FROM OPENJSON(@json, '$.info.tags') /******************************* -- JSON 필드 생성 *******************************/ drop table person; CREATE TABLE Person ( Id int IDENTITY PRIMARY KEY NONCLUSTERED, FirstName nvarchar(100) NOT NULL, LastName nvarchar(100) NULL, InfoJson nvarchar(max) ); ALTER TABLE Person ADD CONSTRAINT [Content should be formatted as JSON] CHECK ( ISJSON( InfoJSON )> 0 ) insert into person select 'aaa', 'aaaaa' , N'{"info":{"type":1,"address":{"town":"Bristol","county":"Avon","country":"England"},"tags":["Sport", "Water polo"]},"type":"Basic"}'; insert into person select 'bbb', 'bbbbb' , N'{"info":{"type":2,"address":{"town":"Bristol2","county":"Avon2","country":"England2"},"tags":["Sport2", "Water polo2"]},"type":"Basic2"}'; select * from person; insert into person select 'bbb', 'bbb', NULL; insert into person select 'ccc', null, NULL; select * from person; /******************************* -- JSON NULL 출력 여부 -- include_null_values *******************************/ select id, firstname, lastname from person for json path; /* [{"id":1 ,"firstname":"aaa" ,"lastname":"aaaaa"} ,{"id":2 ,"firstname":"bbb" ,"lastname":"bbb"} ,{"id":3 ,"firstname":"ccc"}] */ select id, firstname, lastname from person for json path, include_null_values; /* [{"id":1 ,"firstname":"aaa" ,"lastname":"aaaaa"} ,{"id":2 ,"firstname":"bbb" ,"lastname":"bbb"} ,{"id":3 ,"firstname":"ccc" ,"lastname":null}] */ /******************************* -- APPLY 활용 출력 *******************************/ select *, b.[key] from person a outer apply OPENJSON (infojson) b; select firstname, lastname, j.* from person a cross apply OPENJSON (infojson, '$.info.address') WITH ( [town] nvarchar(100) '$.town' , [county] nvarchar(100) '$.county' , [country] nvarchar(100) '$.country' ) AS J; /******************************* -- JSON_VALUE, JSON_QUERY 활용 출력 *******************************/ /* {"info": {"type":1 ,"address":{"town":"Bristol" ,"county":"Avon" ,"country":"England" } ,"tags":["Sport", "Water polo"] } ,"type":"Basic" }' */ select firstname, lastname , JSON_QUERY(InfoJson, '$.info') as info , JSON_VALUE(InfoJson, '$.info.type') as "info.type" , JSON_QUERY(InfoJson, '$.info.address') as address , JSON_VALUE(InfoJson, '$.info.address.town') as town , JSON_VALUE(InfoJson, '$.info.address.country') as ctry , JSON_QUERY(InfoJson, '$.info.tags') as tags , JSON_VALUE(InfoJson, '$.info.tags[0]') as "tags[0]" , JSON_VALUE(InfoJson, '$.type') as type from person;