반응형

/*******************************************************************************************************************
-- Title : [SQL2016] FOR JSON을 사용한 쿼리 결과의 JSON 서식 지정
-- Reference : msdn.microsoft.com
-- Key word : for json path root for json auto include_null_values  without_array_wrapper
*******************************************************************************************************************/

-- URL
    * https://msdn.microsoft.com/ko-kr/en-kr/library/dn921882.aspx

-- 테이블 생성
create table #ttt
( number varchar(10)
, date datetime
, customer varchar(10)
, price numeric(10,2)
, quantity int
);

insert into #ttt
select 'SO43659','2011-05-30', 'MSFT',59.99, 1 union all
select 'MO43659','2012-05-30', 'WOPS',24.99, 3;

select * from #ttt;


-- Query 1 : for json path
select *
from #ttt
for json path;


-- Query 2 : for json path, root()
select *
from #ttt
for json path, root('ORDERS');


-- Query 3 : for json path, root(), 서식 지정
select number, date, customer, price "ITEM.UNITPRICE", quantity "ITEM.QTY"
from #ttt
for json path, root('ORDERS');


-- Query 4 : for json path, root(), 서식 지정
select number "ORDER.NUMBER", date "ORDER.DATE", customer "ACCOUNT_NUMBER"
     , price "ITEM.UNITPRICE", quantity "ITEM.QTY"
from #ttt
for json path, root('ORDERS');



-- Query 5 : for json auto
select *
from #ttt
for json auto;


-- NULL 포함 데이터로 변경

truncate talbe #ttt;

insert into #ttt
select 'SO43659',NULL, 'MSFT',59.99, 1 union all
select 'MO43659','2012-05-30', NULL,24.99, 3;

select * from #ttt;

-- Query 6 : NULL 빠짐 확인
select *
from #ttt
for json path;


-- Query 7 : include_null_values(NULL 포함)

select * 
from #ttt
for json path, INCLUDE_NULL_VALUES;


-- Query 8 : WITHOUT_ARRAY_WRAPPER(대괄호[] 제거, 1건만 적용됨)
select *
from #ttt
for json path, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER;

-- Query 9 : 특수문자 처리
-- https://msdn.microsoft.com/ko-kr/dn921889
SELECT 'VALUE\    /  "' as [KEY\/"],  
       CHAR(0) as '0',  
       CHAR(1) as '1',  
       CHAR(31) as '31'  
FOR JSON PATH;
/*
JSON_F52E2B61-18A1-11d1-B105-00805F49916B
--------------------------------------------------------------------------------------------
[{"KEY\\\/\"":"VALUE\\    \/  \"","0":"\u0000","1":"\u0001","31":"\u001f"}]
*/

 

반응형

+ Recent posts