如何从JSON数组中提取全部header值并聚合为逗号分隔字符串？

问题场景 #

我在Files表的Columns字段中存储了多个JSON数组，示例数据如下：

[{"header": "A", "value": "test"}, {"header": "B", "value": "test1"}, {"header": "C", "value": "test2"}]

[{"header": "A1", "value": "test"}, {"header": "B1", "value": "test1"}, {"header": "C1", "value": "test2"}]

[{"header": "A2", "value": "test"}, {"header": "B2", "value": "test1"}, {"header": "C2", "value": "test2"}]

我尝试用JSON_VALUE获取header，但只能指定数组索引提取单个值：

SELECT JSON_VALUE(Columns, '$[0].header') FROM Files

得到的结果是：

1: A
2: A1
3: A2

我需要的预期结果是每个JSON数组对应的所有header拼接成逗号分隔的字符串：

1: A, B, C
2: A1, B1, C1
3: A2, B2, C2

解决方案 #

可以使用OPENJSON解析每个JSON数组，提取所有header值，再通过STRING_AGG将同一行的header值拼接成字符串。具体SQL语句如下：

SELECT 
    STRING_AGG(j.header, ', ') AS all_headers
FROM Files
CROSS APPLY OPENJSON(Files.Columns)
WITH (
    header NVARCHAR(100) '$.header'
) j
GROUP BY Files.Id -- 若表有主键（如Id）优先用主键分组，无主键可替换为Files.Columns

说明 #

• OPENJSON会将JSON数组拆分成多行，每行对应数组中的一个对象，通过WITH子句指定提取header字段。
• CROSS APPLY用于关联原表每行与对应JSON数组解析后的行。
• STRING_AGG将同一分组下的所有header值用, 拼接为单个字符串。
• 优先用表的主键分组，比直接用Columns字段分组更高效，还能避免JSON内容重复导致的分组错误。

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