Azure SQL中提取JSON数组所有course_key并转为逗号分隔字符串

可以通过OPENJSON解析JSON数组，再结合STRING_AGG聚合函数实现需求，无需手动遍历固定数量的元素，自动适配数组长度变化。

核心SQL语句 #

SELECT 
    v.person_id,
    v.event_date,
    STRING_AGG(j.course_key, ', ') AS course_keys
FROM violation v
CROSS APPLY OPENJSON(v.course_json)
WITH (
    course_key VARCHAR(100) '$.course_key' -- 根据实际字段类型调整长度
) j
GROUP BY v.person_id, v.event_date;

处理空值或空数组场景 #

如果course_json可能为NULL或空数组，想要保留这些行并显示空字符串而非NULL，可改用LEFT JOIN配合ISNULL：

SELECT 
    v.person_id,
    v.event_date,
    ISNULL(STRING_AGG(j.course_key, ', '), '') AS course_keys
FROM violation v
LEFT JOIN OPENJSON(v.course_json)
WITH (
    course_key VARCHAR(100) '$.course_key'
) j ON 1 = 1
GROUP BY v.person_id, v.event_date;

关键函数说明 #

• OPENJSON：将JSON数组转换为关系型行集，通过WITH子句指定要提取的course_key字段及其映射的SQL数据类型
• STRING_AGG：Azure SQL支持的字符串聚合函数，按分组（person_id和event_date）将多行course_key值拼接为单个逗号分隔的字符串

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