我正在尝试读取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