#title JSON
[[TableOfContents]]

==== json schema 뽑아내기 ====
{{{
create function [json].[get_schema](@json nvarchar(max)) returns table as return with cte as (
    select 
        convert(varchar(500), [key]) [key]
    ,   [key] name
    ,   [value]
    ,   [type]
    ,   1 level
    ,   convert(varchar(200), right(concat('000', row_number() over(order by (select 1))), 3)) path
    from openjson(@json)
    union all
    select 
        --convert(varchar(500), concat(a.[key], case when b.[type]=5 then '' else  concat('.', case when isnumeric(b.[key])=1 and b.type=2 then '[]' else b.[key] end) end))
        convert(varchar(500), concat(a.[key], case when a.type = 4 then '.[]' else concat('.', b.[key]) end))
    ,   case 
            when a.type = 4 then '[]' 
            when b.[value] = '[]' then concat(b.[key], '[]')
            else b.[key] 
        end name
    ,   b.[value]
    ,   b.type
    ,   level + 1
    ,   convert(varchar(200), concat(a.path, '/', right(concat('000', row_number() over(order by (select 1))), 3)))
    from cte a
        cross apply openjson(a.value) b
    where 1=1
    and a.type in (4,5)
)
select top 100 percent
    [key]
,   replicate('    ', level-1) + '- ' + name name
,   type
,   level
,   path
from (
    select
        [key]
    ,   name
    ,   level
    ,   min(type) type
    ,   min(path) path
    from cte
    group by
        [key]
    ,   name
    ,   level
    ) t
order by path
go
}}}

==== 값들 모두 뽑아내기 ====
{{{
create function [json].[get_value_set](@json nvarchar(max)) returns table as return with cte as (
    select 
        convert(varchar(500), [key]) [key]
    ,   [value]
    ,   [type]
    from openjson(@json)
    union all
    select 
        --convert(varchar(500), concat(a.[key], case when b.[type]=5 then '' else  concat('.', case when isnumeric(b.[key])=1 and b.type=2 then '[]' else b.[key] end) end))
        convert(varchar(500), concat(a.[key], case when a.type = 4 then '.[]' else concat('.', b.[key]) end))
    ,   b.[value]
    ,   b.type
    from cte a
        cross apply openjson(a.value) b
    where 1=1
    and a.type in (4,5)
)
select
    [key]
,   case when type not in (4,5) then [value] end [value]
,   type
from cte
go
}}}

==== openjson() ====
2016 버전부터 지원된다. with 절 안쓰면 메모리 엄청 잡아먹는다.
매개변수는 json문자열이고, return은 key, value, type.
type은 다음과 같다.

||Value of the Type column||JSON data type||
||0||null||
||1||string||
||2||int||
||3||true/false||
||4||array||
||5||object||

2가지 모드가 있다.
 * lax
 * strict 

다음과 같이 써먹는다.
{{{
select store.title, location.street, location.lat, location.long  
from store  
cross apply openjson(store.jsoncol, 'lax $.location')   
     with (street varchar(500) ,  postcode  varchar(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     as location
}}}

==== t-sql 구현 ====
단순한 json에만 써먹는다.
{{{
create function [dbo].json_get_value(@json varchar(8000), @key varchar(200))
returns varchar(200)
begin
    /*
    declare
        @json varchar(8000) = '{"dt":"2015-09-13 03:33:03","code1":"31","code2":"17","resultcode":0}'
    ,   @key varchar(200) = 'code2'
    */
    set @json = replace(replace(@json, '" :', '":'), ': "', ':"')

    declare @val varchar(200)
    select
        @val = replace(substring(json, start_pos+len([key])+2, end_pos), '"', '')
    from (
        select
            json
        ,   [key]
        ,   start_pos
        ,   case when end_pos = 0 then len(json) - start_pos - len([key]) - 2 else end_pos - len([key]) - 3 end end_pos
        ,   end_pos a
        from (
            select 
                @json json
            ,   @key [key]
            ,   charindex(@key, @json) start_pos
            ,   charindex(',"', substring(@json, charindex(@key, @json), 8000)) end_pos
        ) t
        where charindex(@key, @json) > 0
    ) t

    return @val

end

--select dbo.json_get_value(''{"dt":"2015-09-13 03:33:03","code1":"31","code2":"17","resultcode":0}', 'code2')
}}}

==== 1행씩 json 문자열 만들기 ====
{{{
with temp
as
(
    select 1 id, '{"a":1, "b":"b"}' json_string
    union all
    select 2 id, '{"a":2, "b":"c"}' json_string
)
select (select id, string_escape(json_string, 'json') json_string  for json path, without_array_wrapper) json_return
from temp
}}}

==== Pretty Json ====
 * https://stackoverflow.com/questions/52899115/sql-server-2016-ssms-json-formatting
 * attachment:JSON/JSONBeautifier.sql https://github.com/msornakumar/SQLServerJSONBeautifier/blob/master/Deploy/JSONBeautifier.sql
 * https://www.example-code.com/sql/json_pretty_print.asp


==== 참고자료 ====
 * https://www.red-gate.com/simple-talk/blogs/71858/ --> t-sql로 이쁘게 변환
 * http://www.objgen.com/json --> json을 프로그래밍하듯이 만들 수 있다.
 * http://chris.photobooks.com/json/ --> 테이블 형태로 이쁘게 만들 수 있다.
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/09/08/passing-arrays-to-t-sql-procedures-as-json.aspx Passing arrays to T-SQL procedures as JSON]
 * [https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/29/use-with-clause-in-openjson-to-improve-parsing-performance/ Use WITH clause in OPENJSON to improve parsing performance]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/12/21/without-array-wrapper-new-important-change-in-for-json.aspx WITHOUT_ARRAY_WRAPPER - New important change in FOR JSON]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/12/21/designing-product-catalogs-in-sql-server-2016-using-json.aspx Designing Product Catalogs in SQL Server 2016 using JSON]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/12/21/indexing-data-in-json-product-catalogs.aspx Indexing data in JSON product catalogs]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/12/09/create-relational-view-over-json-text.aspx Create relational view over JSON text]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/30/using-non-relational-models-in-sql-server.aspx Using non-relational models in SQL Server]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/23/storing-json-in-sql-server.aspx Storing JSON in SQL Server]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/28/query-json-data.aspx Query JSON data]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/29/de-normalize-database-schema-using-json.aspx De-normalize database schema using JSON]