sql - 将列内包含 JSON 数组的行扩展到多行

Question

我遇到了 SQL 数据迁移 (SQL Server) 问题，希望您能引导我走向正确的方向。

假设我们有表DataTable(名称简化)，其中包含以下列:

DataID | SomeForeignKey | SpecificDataValues | OtherSpecificDataValues
int    | int            | String             | String
-------+----------------+--------------------+------------------------
0      | 1              | ['1','2']          | ['1', '2']

其中 SpecificDataValues 和 OtherSpecificDataValues 是 JSON 数组(如 ['1', '2'])

现在我想将此表(最多使用 SQL 迁移脚本)迁移到新表:

DataValuesTable
DataID | SomeForeignKey | SpecificDataValues | OtherSpecificDataValues
-------+----------------+--------------------+------------------------
0      | 1              | 1                  | 1
1      | 1              | 2                  | 2

所以，我基本上想在新表中为每个值生成一个新行，存储在“SpecificDataValues”和“OtherSpecificDataValues”中

我已经检查过，有 SQL 函数可以处理 JSON(OPENJSON、JSON_QUERY)，但我无法使用此工具生成所需的结果。

我希望你能给我指明正确的方向。

Answer 1

一种解决方案是使用 OPENJSON 两次。它将生成 Array(2) x Array(2) = 4 行，您感兴趣的是索引位置匹配的行:

SELECT DataID, SomeForeignKey, j1.value, j2.value
FROM t
CROSS APPLY OPENJSON(SpecificDataValues) AS j1
CROSS APPLY OPENJSON(OtherSpecificDataValues) AS j2
WHERE j1.[key] = j2.[key]

另一个解决方案是同时使用 OPENJSON 和 JSON_VALUE:

SELECT DataID, SomeForeignKey, j1.value, JSON_VALUE(OtherSpecificDataValues, CONCAT('$[', CAST(j1.[key] AS INT), ']'))
FROM t
CROSS APPLY OPENJSON(SpecificDataValues) AS j1

请注意，您的“JSON”无效。字符串必须包含在 " 内。

Demo on db<>fiddle