时间戳

首页 > 解决方案 > 解析包含多个数组和键的 JSON 列

sql - 解析包含多个数组和键的 JSON 列

问题描述

我已经通过一些示例/教程尝试使用 SQL Server (2017) 中的 JSON 函数解析包含多个数组的 JSON 列，但是我仍然无法尝试解析以下 JSON 格式，如下所示.

出于示例目的 - 我将在下面的代码示例中将其声明为 @json 变量。我已经尝试了多次迭代来尝试提取这些值，所以这是我在尝试解析下面显示的示例时停止的地方。

DECLARE @json nvarchar(max)
SET @json = N'[
  [
    {
      "Title": "Title 1",
      "Property": "String1",
      "Value": "123456",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 2",
      "Property": "String2",
      "Value": "54321",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ],
  [
    {
      "Title": "Title 3",
      "Property": "String3",
      "Value": "33333333",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 4",
      "Property": "String4",
      "Value": "44444444",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ]
]'
SELECT JSON_VALUE(j.value, '$.Title') AS 'Title Output', j.[key], j.Value, JSON_VALUE(j.value, '$.Title[0]') AS Title1
--STRING_AGG('Value: ' + v.[value] + ' Title: ' + t.[value], ', ') AS [Values]
FROM OPENJSON(@json) j

随着 JSON 格式存储到列中的方式，我希望获得所有标题和值，如下所示。

预期输出列

标题 1 - 123456，标题 2 - 54321，标题 3 - 33333333，标题 4 - 44444444

我还尝试在解析数据之前更改 JSON 字符串，例如预先添加一个数组名称，以帮助识别集合成员的位置，但是我只能获得第一个标题和值。

标签： sqljsonsql-server-2017string-parsing

解决方案

[]它包含一个带有对象的数组数组{}

您可以CROSS APPLY通过数组的值来获取元素。

这是一个使用表格的示例。
但这对于变量来说是相同的原理。 

CREATE TABLE test
(
  id int identity(1,1) primary key,
  jsonCol nvarchar(max)
);

INSERT INTO test VALUES
(N'[
  [
    {
      "Title": "Title 1",
      "Property": "String1",
      "Value": "123456",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 2",
      "Property": "String2",
      "Value": "54321",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ],
  [
    {
      "Title": "Title 3",
      "Property": "String3",
      "Value": "33333333",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    },
    {
      "Title": "Title 4",
      "Property": "String4",
      "Value": "44444444",
      "ValueArray": [
        ""
      ],
      "SecondaryValue": null,
      "SelectedItem": null
    }
  ]
]');

询问：

SELECT id, a.[Values]
FROM test t
OUTER APPLY
(
   SELECT
   STRING_AGG(CONCAT(
      JSON_VALUE(obj.value,'$.Title'),
      ' - ', 
      JSON_VALUE(obj.value,'$.Value')
   ), ', ') AS [Values]
   FROM OPENJSON(t.jsonCol) AS arr
   CROSS APPLY OPENJSON(arr.Value) AS obj
) a;

结果：

编号 | 价值观                                                                   
-: | :------------------------------------------------ ----------------------
 1 | 标题 1 - 123456，标题 2 - 54321，标题 3 - 33333333，标题 4 - 44444444

在这里测试db<>fiddle

推荐阅读