Search code examples
sql-server-2016sql-functionopen-json

How to parse JSON string recursively with openjson


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"]
    }
}'

The expected result is

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.

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)

Solution

  • 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
    

    And the results: