Contents

1 json schema 觸願鍵
2 螳 覈 觸願鍵
3 openjson()
4 t-sql 蟲
5 1 json 覓語 襷り鍵
6 Pretty Json
7 谿瑚襭


[edit]

1 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

[edit]

2 螳 覈 觸願鍵 #

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

[edit]

3 openjson() #

2016 覯覿 讌. with 磯 覃覈襴 豌 ′襾豪.
襷り覲 json覓語伎願, return key, value, type.
type れ螻 螳.

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

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

[edit]

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

[edit]

5 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

[edit]

6 Pretty Json #

[edit]

7 谿瑚襭 #