SQL Server使用OPENJSON解析嵌套JSON

yeotifhr 于 2022-12-15 发布在 SQL Server

关注(0)|答案(1)|浏览(314)

我正在尝试读取JSON，我想解析SQL Server中的每个集合。
其结构如下所示：根〉操作〉GoAction文件/池
我希望为每个GoActionFiles〉GoActionFile返回一行，因此如下所示;

JSON如下所示;

DECLARE @Json NVARCHAR(MAX)
SET @Json = 
N'
{
   "Root":{
      "Action":{
         "ActionId":1,
         "OutcomeId":2,
         "ActionDateTime":"2022-11-22T14:28:20.9700312+00:00",
         "GoActionFiles":{
            "GoActionFile":[
               {
                  "Name":"Arigon",
                  "Status":"Failed"
               },
               {
                  "Name":"Butella",
                  "Status":"Passed"
               },
               {
                  "Name":"Chantice",
                  "Status":"Passed"
               },
               {
                  "Name":"Fordwat",
                  "Status":"Passed"
               }
            ]
         },
         "Pools":{
            "Pool":[
               {
                  "Name":"Arigon",
                  "Status":"Passed"
               },
               {
                  "Name":"Butella",
                  "Status":"Failed"
               },
               {
                  "Name":"Chantice",
                  "Status":"Failed"
               },
               {
                  "Name":"Fordwat",
                  "Status":"Failed"
               }
            ]
         },
         "ExtCheck":{
            "Score":800,
            "ExtStatus":"Passed",
            "IntScore":0
         }
   }
}
'

到目前为止，我已经尝试了以下SQL;

SELECT ActionId, a.GoActionFiles FROM OPENJSON(@Json, '$.Root.Action') WITH
(
    ActionId INT,
    GoActionFiles NVARCHAR(MAX) AS JSON
) AS a

JSON

来源：https://stackoverflow.com/questions/74773735/sql-server-parse-nested-json-using-openjson

1条答案

按热度按时间

yquaqz181#

如果只有一个actionID，我怀疑这会更有性能

Select ActionID           = JSON_VALUE(@Json,'$.Root.Action.ActionId')
      ,GoActionFileName   = [Name]
      ,GoActionFileStatus = [Status]
 From OpenJSON(@Json, '$.Root.Action.GoActionFiles.GoActionFile') 
      with ( [Name] varchar(150),
             [Status] varchar(150)
           ) AS a

如果有多个ID

SELECT A.ActionId
       ,GoActionFileName   = B.[Name]
       ,GoActionFileStatus = B.[Status]
   FROM OPENJSON(@Json, '$.Root.Action') WITH (
                                                ActionId INT,
                                                GoActionFiles NVARCHAR(MAX) AS JSON
                                              ) A
 Cross Apply ( Select * 
                From OpenJSON(GoActionFiles, '$.GoActionFile') 
                        with ( [Name]   varchar(150),
                               [Status] varchar(150)
                              ) B1 
             ) B

两者返回

ActionId    GoActionFileName    GoActionFileStatus
1           Arigon              Failed
1           Butella             Passed
1           Chantice            Passed
1           Fordwat             Passed

赞(0）回复(0）举报 2022-12-15

我来回答

SQL Server使用OPENJSON解析嵌套JSON

1条答案

相关问题

热门标签

最新问答