SQL Server 2019：如何将JSON列中对应数组展开为对应行

嘿，我懂你遇到的麻烦！你之前用两次CROSS APPLY OPENJSON的方式会生成笛卡尔积，因为两个数组是被独立展开后再关联的，自然会出现所有组合。要让names和ages数组里的元素按对应位置匹配，得利用OPENJSON解析数组时返回的索引来关联。

正确的实现方法 #

核心思路是：先解析names数组，拿到每个元素的索引（也就是数组里的位置），再用这个索引去ages数组中提取对应位置的元素，这样就能保证每组name和age是一一对应的。

下面是具体的SQL语句：

SELECT 
    t.id,
    j.value AS names,
    JSON_VALUE(t.json_col, '$.ages[' + j.[key] + ']') AS ages
FROM Schema.table t
CROSS APPLY OPENJSON(t.json_col, '$.names') j

代码说明 #

• OPENJSON(t.json_col, '$.names') j：解析names数组，返回的结果集包含三个默认列：
  • [key]：数组元素的索引（字符串类型，从0开始计数）
  • value：数组元素的具体值（这里就是名字）
  • type：元素的数据类型（我们这里用不到）
• JSON_VALUE(t.json_col, '$.ages[' + j.[key] + ']')：通过[key]索引定位到ages数组中对应位置的元素，取出年龄值。

测试示例 #

对于你给出的示例数据：

id=1时，json_col={"names":["John","Peter"],"ages":["31","40"]}

执行语句后会得到：

完全符合你想要的展开效果，而且因为题目中明确names和ages数组长度始终相等，所以不用担心索引越界的问题。

内容的提问来源于stack exchange，提问作者CutePoison