json - 如何使用mssql递归解析JSON字符串？

Question

我的想法来自 this answer并得到进一步的问题。我定义了一个变量:

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

然后我执行查询:

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

我从表中重置了变量@json(即jsonString)，并重复执行上面的查询。

执行结果如下:

我一直在尝试将上面的结果存储到一个表中，并创建了下面的函数:

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)

字段IsType是检查是否需要递归的条件。

[dbo].GetTypeId 是一个用户定义的函数，用于检查 FieldValue 是否为最终值，尽管它可能看起来不像用于此目的的东西。

以下是函数GetTypeId和Type表:

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

这是错误消息:

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

Answer 1

看起来和听起来都像是您试图在表中获得一个很好的非冗余编码，但并不完全清楚。

如果是这样，这是我用来执行类似操作的查询。查看输出，看看这是否是您想要的。有几点。首先，可以通过 isjson() 函数轻松确定终端节点，该函数将返回 0 值(和空值)。其次，构建任意 Id 比让 json 构建自己的 Id 更困难。第三，我扔了一个或两个空值...以捕获所有合法条件，最后...我在格式上作弊(列是 nvarchar(4000) 和 nvarchar(max)。 ..所以最终的选择有转换...但我不想混淆查询)

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

...返回:

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)

这显示了 ParentId、Id 和 Node，但实际上，Id 列是多余的。您不需要它来重建 json。然而，包含一个序列可能会更方便。

最后一点...我认为在 cte 内部进行递归可能比在 cte 外部更容易，因为您不必交叉应用该函数...如引用的答案中所示。如果您愿意，您仍然可以将其封装在函数中。

编辑(可能是TL；NR)

我建议对节点进行排序对于以后的重组来说是一个好主意...并且将选择放入一个函数中可能是可取的...然后我为没有这样做而感到内疚:-)所以，在这里'

生成的输出不按整个文档顺序排列，而是按程序集顺序排列。也就是说，对于任何给定的节点，所有上层节点都保证在输出中较早......并且父节点的所有子节点都按文档顺序排列。我向该函数添加了一个相对序列号和一个深度指示器，认为这些值在某些情况下可能有用。

将其放入 cte 内部进行递归的函数的一个好处是，生成的函数可以是内联表值函数，这通常比累积表变量的表值函数更有效。

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
)