如何在SQL查询中将关联标签输出为JSON数组格式的Tags列

现有三张表：

• Product：存储产品基础信息（包含Id、Name、SKU等字段）
• Product_ProductTag_Mapping：产品与标签的关联表（通过Product_Id关联Product.Id，ProductTag_Id关联ProductTag.Id）
• ProductTag：存储标签信息（包含Id、Name字段）

需要查询产品信息，同时新增Tags列，以JSON数组格式展示该产品关联的所有标签名称，预期结果示例：

ProductId: 0
ProductName: Pretty Necklace
Tags: ["gold", "topaz", "fire"]

使用普通JOIN查询会因每个标签生成重复的产品记录，尝试的REPLACE处理JSON的方法生成的结果格式错误，不符合预期。

方法1：使用STRING_AGG（SQL Server 2017+ 支持） #

这种方法简洁高效，且能自动处理标签名中的特殊字符（如双引号、反斜杠），生成合法的JSON数组：

SELECT
    ROW_NUMBER() OVER (ORDER BY p.SKU) AS Id,
    p.Id AS ProductId,
    p.Name AS ProductName,
    -- 生成JSON数组：用JSON_QUOTE转义标签名，STRING_AGG拼接成数组格式
    CASE 
        WHEN COUNT(pt.Id) = 0 THEN '[]'
        ELSE '[' + STRING_AGG(JSON_QUOTE(pt.Name), ',') + ']'
    END AS Tags
FROM dbo.Product AS p WITH (NOLOCK)
LEFT JOIN dbo.Product_ProductTag_Mapping AS ptm ON ptm.Product_Id = p.Id
LEFT JOIN dbo.ProductTag AS pt ON pt.Id = ptm.ProductTag_Id
GROUP BY p.Id, p.Name, p.SKU
ORDER BY p.SKU

• LEFT JOIN确保没有标签的产品也能被查询到，此时Tags会返回空数组[]
• JSON_QUOTE负责对标签名进行JSON转义，避免生成无效的JSON格式
• STRING_AGG将转义后的标签名用逗号分隔，再拼接成数组格式

方法2：使用子查询+FOR JSON PATH #

如果使用的是SQL Server 2016及更早版本，可采用这种方法生成纯JSON数组：

SELECT
    ROW_NUMBER() OVER (ORDER BY p.SKU) AS Id,
    p.Id AS ProductId,
    p.Name AS ProductName,
    ISNULL(
        (
            SELECT pt.Name AS [value]
            FROM dbo.Product_ProductTag_Mapping AS ptm
            INNER JOIN dbo.ProductTag AS pt ON pt.Id = ptm.ProductTag_Id
            WHERE ptm.Product_Id = p.Id
            ORDER BY pt.Name
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ), '[]'
    ) AS Tags
FROM dbo.Product AS p WITH (NOLOCK)

• 子查询中通过AS [value]指定字段名，配合FOR JSON PATH生成标准化的键值对结构
• WITHOUT_ARRAY_WRAPPER去掉子查询生成的外层数组，最终拼接成合法的JSON数组格式
• ISNULL处理无标签的产品，确保返回空数组[]而非NULL

原方法通过REPLACE手动修改FOR JSON AUTO生成的JSON结构，这种方式存在诸多问题：

• 无法处理标签名中的特殊字符（如双引号），会直接破坏JSON语法
• 依赖FOR JSON AUTO的固定输出格式，一旦SQL Server的JSON生成逻辑有细微调整，替换逻辑就会失效
• 生成的结果存在语法错误（如缺少闭合的}），不符合JSON规范

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