如何使用openjson递归解析JSON字符串 [英] How to parse JSON string recursively with openjson

问题描述

我有以下 JSON 数据:

I have the following JSON data :

set @json = N'{
    "Book":{
        "IssueDate":"02-15-2019"
        , "Detail":{
            "Type":"Any Type"
            , "Author":{
                "Name":"Annie"
                , "Sex":"Female"
            }
        }
        , "Chapter":[
            {
                "Section":"1.1"
                , "Title":"Hello world."
            }
            ,
            {
                "Section":"1.2"
                , "Title":"Be happy."
            }       
        ]
        , "Sponsor":["A","B","C"]
    }
}'

预期的结果是

topKey     Key         Value
Book       IssueDate   02-15-2019
Book       Detail      { "Type":"Any Type", "Author":{ "Name":"Annie" , "Sex":"Female"}
Book       Chapter     [{ "Section":"1.1", "Title":"Hello world." }, { "Section":"1.2", "Title":"Be happy." }]
Book       Sponsor     ["A","B","C"]
Detail     Type        Any Type
Detail     Author      { "Name":"Annie" ,"Sex":"Female"} 
Author     Name        Annie
Author     Sex         Female 
Chapter    Section     1.1
Chapter    Title       Hello world
Chapter    Section     1.2
Chapter    Title       Be happy.

我发现当字段Value"是JSON时，我需要继续解析它.

I found that when the field "Value" is JSON, I need to keep parsing it.

所以我创建了一个函数来完成解析工作，但它返回不符合要求的 ''.

So I created a function to do the parsing work but it returns '' which does not meet the requirement.

create function ParseJson(@json nvarchar(max))
returns @tempTable table ([key] nvarchar(max), [value] nvarchar(max))
as
begin
    insert @tempTable
    select 
        x.[key]
        , x.[value]
    from
        openjson(@json) x
    cross apply ParseJson(x.[value]) y 
    where ISJSON(x.[value])=1
end

一个字符串可以传递给函数.

A string may be passed to the function.

select * from ParseJson(@json)

推荐答案

我不确定您对结果的期望是否合理，但显然您的函数的返回表与您所说的不符——它缺少 topKey 列.出于这个原因，我宁愿聚合层次结构的路径.我们开始吧:

I'm not sure if your expectation of the results is reasonable but clearly the returning table of your function doesn't match what you stated -- it lacks topKey column. For this reason, I'd rather aggregate the path of the hierarchy. Here we go:

create function ParseJson(
    @parent nvarchar(max), @json nvarchar(max))
returns @tempTable table (
    [key] nvarchar(max), [value] nvarchar(max))
as
begin
    ; with cte as (
        select 
            iif(@parent is null, [key]
                , concat(@parent, '.', [key])) [key]
            , [value]
        from 
            openjson(@json)
    )
    insert 
        @tempTable
    select 
        x.* 
    from 
        cte x
    union all
    select 
        x.* 
    from 
        cte y
    cross apply ParseJson(y.[key], y.[value]) x
    where isjson(y.[value])=1

    return
end

结果:

这篇关于如何使用openjson递归解析JSON字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！