How to parse JSON string recursively with openjson

Question

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)

Answer 1

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:

Answer 2

I have encountered the same problem myself, so I contorted my brain for an hour until I came up with a recursive approach. (I don't know why recursion in SQL seems so unintuitive to me!)

WITH ParseKey AS (
    SELECT [key], [value], [type] 
    FROM OPENJSON(@json)
    UNION ALL
    SELECT pop.[key], pop.[value], pop.type
    FROM ParseKey op
        CROSS APPLY (SELECT op.[key] + '.' + ip.[key] [key], ip.[value], ip.[type] FROM OPENJSON(op.[value]) ip WHERE op.[type]=5) pop
    )
SELECT *
FROM ParseKey
WHERE [type]<>5

This works, although there can still be JSON elements embedded in list values (type=4, values enclosed in square brackets). I'm not yet sure how to deal with these elegantly, but I'll post back if I come up with anything worth sharing!