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

问题描述

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

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保存在结构"中.而不是json内容，它仅返回NULL:

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://msdn.microsoft.com/en -us/library/mt631706.aspx#Anchor_6

See FAQ: https://msdn.microsoft.com/en-us/library/mt631706.aspx#Anchor_6

如果要在返回的结构数组上应用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屋！