How to parse JSON string recursively by using mssql?

Question

I took the idea from this answer and got further questions. I defined a variable:

declare @json nvarchar(max)
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"]
    }
}'

Then I execute the query:

select 
    x.[key] topKey
    , y.[key] 
    , y.[value] 
    , '{"'+ y.[key] + '":' + y.[value] +'}' jsonString 
from openjson(@json) x
cross apply openjson(x.[value]) y

I reset the variable @json from the table(namely jsonString), and execute the query above repeatedly.

The following is the result of execution:

I've been trying to store the result above into a single table and had created the function below:

create function ParseJson(
    @parent nvarchar(max), @json nvarchar(max))
returns @tempTable table (topKey nvarchar(max), FieldName nvarchar(max), FieldValue nvarchar(max), IsType int)
as
begin
    ; with cte as (
        select 
            x.[key] topKey, 
            y.[key] FieldName, 
            y.[value] FieldValue
            , iif([dbo].GetTypeId(y.[Key]) is null or y.[Key] = 'Type' ,0 ,1) IsType
        from 
            openjson(@json) x
            cross apply openjson(x.[value]) y
    )
    insert 
        @tempTable
    select 
        x.* 
    from 
        cte x
    union all
    select 
        z.* 
    from 
        cte y
    cross apply ParseJson(default,'{"'+ y.FieldName + '":' + y.FieldValue+'}') z
    where y.IsType=1

    return

end

-- execute
select * from ParseJson(default, @json)

The field IsType is the condition to check if recursion is needed.

[dbo].GetTypeId is a user defined function which is for checking whether FieldValue is a terminal value although it might not look like something for that purpose.

The following is the function GetTypeId and the Type table:

create function GetTypeId(
    @typeName nvarchar(255)
)
returns nvarchar(1000)
as
begin
    declare 
        @typeId nvarchar(1000)
    select 
        @typeId=id
    from 
        [Type]
    where
        [Type].[Name]=@typeName

    return @typeId
end
go

Here is the error message :

The JSON text format is incorrect. Unexpected character '0' was found at position 13.

Answer 1

It looks and sounds like you're trying to get a nice non-redundant encoding in the table, but it's not entirely clear.

If so, here's a query I use to do something similar. Look at the output and see if that's what you're after. A couple of points. First, a terminal node is easy to determine from the isjson() function which will return 0 for values (and nulls). Second, building arbitrary Ids is harder than letting the json build its own. Thirdly, I threw a null or two in...to catch all the legal conditions, and finally...I cheated on the formatting (the columns were nvarchar(4000) and nvarchar(max)...and so the final select had converts...but I didn't want to muddy up the query)

declare @j nvarchar(max) =  
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",null]
      , "Hooey":null
  }
}';

with nodes as 
(
  select 
    [key] ParentId, 
    [key]  Id, 
    [key] Node, 
    [value] Val, 
    [type] NodeType, 
    isnull(abs(isjson([value])-1),1) IsTerminal
  from
    openjson( @j ) j 
  union all
  select
    nodes. Id, 
    nodes. Id + '.' + j.[key],
    j.[key], 
    j.[value], 
    j.[type],
    isnull(abs(isjson( j.[value] )-1),1)
  from
    nodes
    outer apply
    openjson( nodes.Val ) j
  where
    isjson( nodes.Val ) = 1
)
select 
  nodes.ParentId,
  nodes. Id,
  nodes.Node,
  case when NodeType= 5 then '{}' when NodeType=4 then '[]' else Val end Val,
  nodes.NodeType,
  nodes.IsTerminal
from 
  nodes

...which returns:

ParentId             Id                             Node       Val                  NodeType IsTerminal
-------------------- ------------------------------ ---------- -------------------- -------- -----------
Book                 Book                           Book       {}                   5        0
Book                 Book.IssueDate                 IssueDate  02-15-2019           1        1
Book                 Book.Detail                    Detail     {}                   5        0
Book                 Book.Chapter                   Chapter    []                   4        0
Book                 Book.Sponsor                   Sponsor    []                   4        0
Book                 Book.Hooey                     Hooey      NULL                 0        1
Book.Sponsor         Book.Sponsor.0                 0          A                    1        1
Book.Sponsor         Book.Sponsor.1                 1          B                    1        1
Book.Sponsor         Book.Sponsor.2                 2          C                    1        1
Book.Sponsor         Book.Sponsor.3                 3          NULL                 0        1
Book.Chapter         Book.Chapter.0                 0          {}                   5        0
Book.Chapter         Book.Chapter.1                 1          {}                   5        0
Book.Chapter.1       Book.Chapter.1.Section         Section    1.2                  1        1
Book.Chapter.1       Book.Chapter.1.Title           Title      Be happy.            1        1
Book.Chapter.0       Book.Chapter.0.Section         Section    1.1                  1        1
Book.Chapter.0       Book.Chapter.0.Title           Title      Hello world.         1        1
Book.Detail          Book.Detail.Type               Type       Any Type             1        1
Book.Detail          Book.Detail.Author             Author     {}                   5        0
Book.Detail.Author   Book.Detail.Author.Name        Name       Annie                1        1
Book.Detail.Author   Book.Detail.Author.Sex         Sex        Female               1        1

(20 row(s) affected)

This shows a ParentId, Id and Node, but in reality, the Id column is redundant. You wouldn't need it to rebuild the json. What might be handy, however, is to include a sequence.

A final note...I think it's perhaps easier to do the recursion inside the cte than outside, as you don't have to cross apply the function...as shown in the referenced answer. You could still encapsulate this in a function if you like.

EDIT (and probably TL;NR)

I suggested that sequencing the nodes would be a good idea for later reassembly...and that putting the select into a function might be desirable...and then I felt guilty for not doing that :-) So, here 'tis.

The resulting output isn't in overall document order, but is in an assembly order. That is, for any given node, all up-level nodes are guaranteed to be earlier in the output...and all child nodes of a parent node are in document order. I added a relative sequence number and a depth indicator to the function, thinking these values might be useful in some scenarios.

One nice thing about making it into a function with the recursion being inside the cte is that the resulting function can be an inline table-valued function, which is generally more efficient than a table-valued function that accumulates a table variable.

create function dbo.JsonNodes( @j nvarchar( max ) ) returns table as return 
(        
  with nodes as 
  (
    select 
      [key] ParentId, 
      [key] Id, 
      [key] Node, 
      [value] Val, 
      [type] Type, 
      isnull( abs( isjson( [value] ) -1 ), 1 ) IsLeaf,
      1 Depth,
      convert( bigint, 1 ) Seq
    from
      openjson( @j ) j 
    union all
    select
      nodes.Id, 
      nodes.Id + '.' + j.[key],
      j.[key], 
      j.[value], 
      j.[type],
      isnull( abs( isjson( j.[value] ) -1 ), 1 ),
      nodes.Depth + 1,
      row_number() over( partition by nodes.Id order by nodes.Id )
    from
      nodes
      outer apply
      openjson( nodes.Val ) j
    where
      isjson( nodes.Val ) = 1
  )
  select
    ParentId,
    Id,
    Node,
    case when Type=5 then '{}' when Type=4 then '[]' else Val end Val,
    Type,
    IsLeaf,
    Depth,
    Seq
  from 
    nodes
)

Answer 2

For how to parse JSON string recursively by using mssql, I believe my answer to your linked question just answered that as well.

From the clues that you mentioned about type and looking at your code, we can deduce that your JSON data are non-trivial and some kind of schema is out there. You did not, however, describe the mechanism under the hood at all.

Base on the assumption I just made, let's make a further guess. Say you have a Type named SomeType1 as a type of nonterminal values, and a type String as a type of terminal values which is unlikely to be resolved as something consists of values of other types but direct symbols; and you'd have to tell us:

1) How GetTypeId distinguishes between them as one is a terminal and the other is a nonterminal while we would get SomeType1 and String?

2) How cross apply works in the CTE and how the property names are relevant to GetTypeId as they would be passed as @typeName?

If there was an external schema of your JSON data, I'd personally guess the code just messed things up especially related to properties and types; and if there wasn't, it's even worse .. you're asking something for nothing -- in that case, IMHO, no can do.