1 json schema 뽑아내기

2 값들 모두 뽑아내기

3 openjson()

4 t-sql 구현

5 1행씩 json 문자열 만들기

6 Pretty Json

7 참고자료

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

• 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

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')

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

• https://stackoverflow.com/questions/52899115/sql-server-2016-ssms-json-formatting
  
• 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/ --> 테이블 형태로 이쁘게 만들 수 있다.
  
• Passing arrays to T-SQL procedures as JSON
  
• Use WITH clause in OPENJSON to improve parsing performance
  
• WITHOUT_ARRAY_WRAPPER - New important change in FOR JSON
  
• Designing Product Catalogs in SQL Server 2016 using JSON
  
• Indexing data in JSON product catalogs
  
• Create relational view over JSON text
  
• Using non-relational models in SQL Server
  
• Storing JSON in SQL Server
  
• Query JSON data
  
• De-normalize database schema using JSON