SQL Server使用OPENJSON按条件将JSON数组转为逗号分隔列表

你需要补充嵌套解析location子数组、按类型分组拼接的逻辑，完整实现语句如下：

兼容SQL Server 2016的写法 #

SELECT 
    m.category_id,
    m.name,
    m.budget,
    m.release_date,
    -- 拼接location_type=1的编码为country字段
    STUFF((
        SELECT ',' + l.location_code
        FROM OPENJSON(m.location) 
        WITH (
            location_type INT '$.location_type',
            location_code VARCHAR(10) '$.location_code'
        ) l
        WHERE l.location_type = 1
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, NULL) AS country,
    -- 拼接location_type=2的编码为distribution字段
    STUFF((
        SELECT ',' + l.location_code
        FROM OPENJSON(m.location) 
        WITH (
            location_type INT '$.location_type',
            location_code VARCHAR(10) '$.location_code'
        ) l
        WHERE l.location_type = 2
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, NULL) AS distribution
FROM OPENJSON(@json, '$.movies')
WITH (
    category_id INT '$.category_id',
    name VARCHAR(255) '$.name',
    budget INT '$.budget',
    release_date DATE '$.release_date',
    location NVARCHAR(MAX) '$.location' AS JSON -- 提取嵌套的location数组JSON用于二次解析
) m

逻辑说明 #

• 基础查询的WITH子句中新增location字段提取，加AS JSON关键字声明该字段是JSON结构，支持后续二次解析
• 采用STUFF + FOR XML PATH的SQL Server经典字符串拼接方案实现同类型location_code的逗号分隔拼接，无匹配值时自动返回NULL，完全符合需求
• 若后续环境升级到SQL Server 2017及以上，可使用STRING_AGG简化写法，逻辑完全一致：

SELECT 
    m.category_id,
    m.name,
    m.budget,
    m.release_date,
    STRING_AGG(CASE WHEN l.location_type = 1 THEN l.location_code END, ',') AS country,
    STRING_AGG(CASE WHEN l.location_type = 2 THEN l.location_code END, ',') AS distribution
FROM OPENJSON(@json, '$.movies')
WITH (
    category_id INT '$.category_id',
    name VARCHAR(255) '$.name',
    budget INT '$.budget',
    release_date DATE '$.release_date',
    location NVARCHAR(MAX) '$.location' AS JSON
) m
CROSS APPLY OPENJSON(m.location)
WITH (
    location_type INT '$.location_type',
    location_code VARCHAR(10) '$.location_code'
) l
GROUP BY m.category_id, m.name, m.budget, m.release_date

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