SQL中如何将JSON数组val的元素拼接为逗号分隔字符串？

要实现将val数组的所有元素合并为逗号分隔的字符串，你需要先展开val数组的所有元素，再通过聚合函数合并它们。以下是修改后的完整查询：

DECLARE @JSONVariable nvarchar(max)
SELECT @JSONVariable  = N'{"name":"North Carolina","region":"North East",
"category":[
    {"group_id":"1AB",
        "groups":[{"val":[3242,4234],"make":{"type":"US","value":"1235"}}]},
    {"group_id":"1AC",
        "groups":[{"val":[2354],"make":{"type":"US","value":"2342"}}]},
    {"group_id":"1CS",
        "groups":[{"val":[242,3433,2424],"make":{"type":"US","value":"0656379"}}]},
    {"group_id":"3AC",
        "groups":[{"val":[2463,4633],"make":{"type":"US","value":"3453"}}]}
    ]}'

SELECT 
    category.group_id,
    STRING_AGG(val_item.value, ',') AS val,
    category.make_type,
    category.make_value
FROM 
    OPENJSON(@JSONVariable, '$.category')
    WITH (
        group_id nvarchar(100) '$.group_id',
        groups nvarchar(max) '$.groups' AS JSON, -- 将groups标记为JSON类型，方便后续解析
        make_type nvarchar(10) '$.groups[0].make.type',
        make_value nvarchar(100) '$.groups[0].make.value'
    ) AS category
CROSS APPLY 
    OPENJSON(category.groups) AS groups_items -- 展开groups数组
CROSS APPLY 
    OPENJSON(groups_items.value, '$.val') AS val_item -- 展开val数组的每个元素
GROUP BY 
    category.group_id, category.make_type, category.make_value; -- 按分组字段聚合

关键修改说明： #

1. 标记groups为JSON类型：在第一个OPENJSON的WITH子句中，将groups字段定义为nvarchar(max)并加上AS JSON，这样后续可以继续解析这个嵌套数组。
2. 展开嵌套数组：通过两次CROSS APPLY分别展开groups数组和val数组，把val中的每个元素拆分为单独的行。
3. 聚合合并元素：使用STRING_AGG(val_item.value, ',')函数，按group_id、make_type、make_value分组，将同一组的val元素合并为逗号分隔的字符串。

另外注意：原JSON中的group_id:1AB不符合标准JSON格式（字符串需加引号），修改后的代码中已修正为"group_id":"1AB"，避免解析报错。

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