如何用SQL拆分JSON数组实现单渠道一行的查询结果？

数据表结构 #

其中JSON数据格式示例：

{
    "status": "x",
    "campaigns": [
        {"channel": "channel_1", "name": "sample_1"},
        {"channel": "channel_2", "name": "sample2"},
        {"channel": "channel_3", "name": "sample_3"}
    ]
 }

需求与问题 #

需要将JSON数组中的每个campaigns项拆分为单独行，关联原表的其他列，最终输出如下格式：

此前使用JSON_QUERY()仅能处理单个channel的情况，多channel时会报错，无法实现拆分需求。

解决方案 #

使用OPENJSON()函数配合CROSS APPLY来拆分JSON数组，这是SQL Server中处理JSON数组拆分的标准方法：

SELECT
    t.[其他列数据] AS [其他列常规数据],
    JSON_VALUE(c.value, '$.channel') AS Channel,
    JSON_VALUE(c.value, '$.name') AS Name
FROM test t
CROSS APPLY OPENJSON(t.[JSON数据], '$.campaigns') c

说明 #

• OPENJSON(t.[JSON数据], '$.campaigns')：将JSON数据列中的campaigns数组拆分为多行，每行对应一个数组元素（即单个{"channel": "...", "name": "..."}对象），用别名c表示。
• JSON_VALUE(c.value, '$.channel')：从拆分后的每个JSON对象中提取channel字段的标量值，与提取JSON片段的JSON_QUERY不同，JSON_VALUE专门用于获取单个值。
• CROSS APPLY：将原表的每一行与拆分后的JSON行关联，确保原表的其他列能正确对应到每个拆分出的channel项。

如果需要兼容空数组或无campaigns字段的情况，可改用OUTER APPLY，这样即使JSON中没有有效数组，原表行仍会保留（对应Channel和Name为NULL）。

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