SQL Server OPENJSON 读取嵌套的 json [英] SQL Server OPENJSON read nested json

问题描述

我有一些想要在 SQL Server 2016 中解析的 json.有项目->结构->属性的层次结构.我想编写一个解析整个层次结构的查询，但我不想通过索引号指定任何元素，即我不想做这样的事情:

I have some json that I would like to parse in SQL Server 2016. There is a hierarchy structure of Projects->Structures->Properties. I would like to write a query that parses the whole hierarchy but I don't want to specify any elements by index number ie I don't want to do anything like this:

openjson (@json, '$[0]')

或

openjson (@json, '$.structures[0]')

我有一个想法，我可以读取顶级项目对象的值以及表示其下方结构的 json 字符串，然后可以单独解析这些值.问题是以下代码不起作用:

I had this idea that I could read the values of the top level project objects along with the json string that represents the structures below it, which could then be parsed separately. The problem is that the following code does not work:

declare @json nvarchar(max)
set @json = '
[
   {
      "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
      "Name":"Test Project",
      "structures":[
         {
            "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
            "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
            "Name":"Test Structure",
            "BaseStructure":"Base Structure",
            "DatabaseSchema":"dbo",
            "properties":[
               {
                  "IdProperty":"618DC40B-4D04-4BF8-B1E6-12E13DDE86F4",
                  "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
                  "Name":"Test Property 2",
                  "DataType":1,
                  "Precision":0,
                  "Scale":0,
                  "IsNullable":false,
                  "ObjectName":"Test Object",
                  "DefaultType":1,
                  "DefaultValue":""
               },
               {
                  "IdProperty":"FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC",
                  "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
                  "Name":"Test Property 1",
                  "DataType":1,
                  "Precision":0,
                  "Scale":0,
                  "IsNullable":false,
                  "ObjectName":"Test Object",
                  "DefaultType":1,
                  "DefaultValue":""
               }
            ]
         }
      ]
   }
]';

select IdProject, Name, structures
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max)
) as Projects

IdProject 和 Name 没有问题返回，但由于某种原因，我无法获取保存在结构"中的嵌套 json.它只是返回 NULL 而不是 json 内容:

IdProject and Name get returned no problem but for some reason I cannot get the nested json held in 'structures'. Instead of the json content it just returns NULL:

有谁知道这是否可行，如果可行，我做错了什么?

Does anyone know if this is possible and if so, what am I doing wrong?

推荐答案

如果引用 JSON 对象或数组，则需要指定 AS JSON 子句:

If you reference JSON object or array you need to specify AS JSON clause:

select IdProject, Name, structures
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max) AS JSON
) as Projects

请参阅常见问题解答:https://docs.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-ver15#return-a-nested-json-sub-object-from-json-text-with-openjson

如果你想在返回的结构数组上应用 OPENJSON，你可以使用如下代码:

If you want to apply OPENJSON on the returned structures array, you can use something like following code:

select IdProject, Name, structures
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max) AS JSON
) as Projects 
     CROSS APPLY OPENJSON (structures) WITH (......)

这篇关于SQL Server OPENJSON 读取嵌套的 json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！