json - 将多个 json 数组读入 SQL Server 中的行

Question

鉴于下面的示例 json 数据，我如何编写查询以一步将数组数据全部提取到单个列？

DECLARE @json NVARCHAR(MAX);
SET @json = N'{
  "Title":"ReportExport",
  "Attachment":"",
  "Name":"Dhoni",
  "FirstTextArea":"Dhoni",
  "Radio":"First",
  "CheckBox":"{C++~.Net}",
  "FileUpload":"",
  "FirstGroupitem":[
    {
      "Address":"QTUvNU55THBxT2hmVXFkWGpia2NJQT09",
      "Age":"30",
      "TestField":"",
      "Country":"ind",
      "SecondTextArea":"11"
    },
    {
      "Address":"OVpuaXpxNTlrZWg4dGI4VXRYMUg0Zz09",
      "Age":"30",
      "TestField":"",
      "Country":"us",
      "SecondTextArea":"22"
    },
    {
      "Address":"WGErNFU2S0tYekdsRWZTT2NxSzZLQT09",
      "Age":"30",
      "TestField":"",
      "Country":"us",
      "SecondTextArea":"33"
    }
  ],
  "SecondGroupitem":[
    {
      "Address1":"QTUvNU55THBxT2hmVXFkWGpia2NJQT09",
      "Age1":"30"
    },
    {
      "Address1":"OVpuaXpxNTlrZWg4dGI4VXRYMUg0Zz09",
      "Age1":"30"
    }
  ]
}';

陈述:

SELECT i.Title,i.Attachment,i.Name,i.FirstTextArea,i.Radio,i.CheckBox,i.FileUpload,'FirstGroupitem' as FirstGroupitem,
 a.Address, a.Age,a.TestField,a.Country,a.SecondTextArea 
FROM OPENJSON(@json) 
WITH (
    Title  VARCHAR(max) N'$.Title',
    Attachment  VARCHAR(max) N'$.Attachment',
    Name  VARCHAR(max) N'$.Name',
    FirstTextArea  VARCHAR(max) N'$.FirstTextArea',
    Radio  VARCHAR(max) N'$.Radio',
    CheckBox  VARCHAR(max) N'$.CheckBox',
    FileUpload  VARCHAR(max) N'$.FileUpload',
    FirstGroupitem nvarchar(max) '$.FirstGroupitem' AS JSON
) AS i
CROSS APPLY (
   SELECT *
   FROM OPENJSON(i.FirstGroupitem)
   WITH (
     Address  VARCHAR(max) N'$.Address',
     Age  VARCHAR(max) N'$.Age',
     TestField  VARCHAR(max) N'$.TestField',
     Country  VARCHAR(max) N'$.Country',
     SecondTextArea  VARCHAR(max) N'$.SecondTextArea'
   )
) a

我想要这样的输出:

Answer 1

您需要加入 $.FirstGroupitem" 中的项目和 $."SecondGroupitem按索引的 JSON 数组:
陈述:

SELECT 
   i.Title, i.Attachment, i.Name, i.FirstTextArea, i.Radio, i.CheckBox,
   j.FirstGroupAddress, j.SecondGroupAddress
FROM OPENJSON(@json) 
WITH (
    Title  VARCHAR(max) N'$.Title',
    Attachment  VARCHAR(max) N'$.Attachment',
    Name  VARCHAR(max) N'$.Name',
    FirstTextArea  VARCHAR(max) N'$.FirstTextArea',
    Radio  VARCHAR(max) N'$.Radio',
    CheckBox  VARCHAR(max) N'$.CheckBox',
    FileUpload  VARCHAR(max) N'$.FileUpload',
    FirstGroupitem nvarchar(max) '$.FirstGroupitem' AS JSON,
    SecondGroupitem nvarchar(max) '$.SecondGroupitem' AS JSON
) AS i
OUTER APPLY (
   SELECT f2.Address AS FirstGroupAddress, s2.Address AS SecondGroupAddress
   FROM OPENJSON (i.FirstGroupitem) f1
   FULL JOIN OPENJSON (i.SecondGroupitem) s1 ON f1.[key] = s1.[key]
   OUTER APPLY OPENJSON(f1.[value]) WITH (
       Address varchar(100) '$.Address'
       -- Additional columns here
   ) f2
   OUTER APPLY OPENJSON(s1.[value]) WITH (
      Address varchar(100) '$.Address1'
      -- Additional columns here
   ) s2
) j

结果:

Title        Attachment Name    FirstTextArea Radio CheckBox    FirstGroupAddress                SecondGroupAddress
---------------------------------------------------------------------------------------------------------------------------------
ReportExport            Dhoni   Dhoni         First {C++~.Net}  QTUvNU55THBxT2hmVXFkWGpia2NJQT09 QTUvNU55THBxT2hmVXFkWGpia2NJQT09
ReportExport            Dhoni   Dhoni         First {C++~.Net}  OVpuaXpxNTlrZWg4dGI4VXRYMUg0Zz09 OVpuaXpxNTlrZWg4dGI4VXRYMUg0Zz09
ReportExport            Dhoni   Dhoni         First {C++~.Net}  WGErNFU2S0tYekdsRWZTT2NxSzZLQT09