[SQL2016] JSON을 활용한 집계처리 및 JSON의 NULL 확인 함수 - dBRang

/*******************************************************************************************************************
-- Title : [SQL2016] JSON을 활용한 집계처리 및 JSON의 NULL 확인 함수 - dBRang
-- Reference : dBRang
-- Key word : for json auto include null values group by
*******************************************************************************************************************/

■ JSON을 활용한 집계 처리

use tempdb;
go
 
-- drop table #ttt;
create table #ttt
( db_cd varchar(10)
, skey bigint
, seq int
, code nvarchar(10)
);
 
insert into #ttt 
select 'JP', 2200001000005, 1, NULL union all
select 'JP', 2200001000005, 2, NULL union all
select 'JP', 2200081001803, 1, 'SS' union all
select 'JP', 2200081001803, 2, NULL union all
select 'JP', 2200081001803, 3, 'DD' union all
select 'CN', 6588001000073, 1, NULL union all
select 'CN', 6588001000073, 2, 'JP' union all
select 'CN', 6716858000047, 1, NULL union all
select 'CN', 6716858000047, 2, NULL union all
select 'CN', 6716858000047, 3, NULL;
 
select * from #ttt
 
-- Group By시 JSON을 통한 집계 처리
select db_cd, skey
     , js_code = (select code 
                  from #ttt 
                  where db_cd = a.db_cd and skey = a.skey
                  for json auto, include_null_values
                 )
from #ttt a
group by db_cd, skey;

-- 결과

■ JSON의  NULL 확인

use tempdb;
go
 
-- drop function tempdb..
create function dbo.fn_json_null_check (@json nvarchar(max))
returns nvarchar(max)
as
begin 
    declare @ret nvarchar(max) = null
    declare @val nvarchar(max) = null
 
    SELECT top 1 @val = js
    FROM OPENJSON(@json)
    with(js nvarchar(100)  '$.code')
    where js is not null;
 
    if @val is not null
        set @ret = @json
    else 
        set @ret = NULL;
 
    return (@ret)
end;
 
-- NULL 리턴 확인
select *
     , tempdb.dbo.fn_json_null_check(js_code) "js_null_check"
from 
(
    select db_cd, skey
         , js_code = (select code 
                      from #ttt 
                      where db_cd = a.db_cd and skey = a.skey
                      for json auto, include_null_values
                     )
    from #ttt a
    group by db_cd, skey
) aa;

-- 결과