SQL Server多对多关联查询：将子表数据返回为对象

在SQL Server中，可以通过FOR JSON PATH结合子查询来实现将关联标签转为对象数组的需求，以下是具体实现方案：

假设表结构 #

先明确三张表的基础字段（可根据实际表结构调整字段名）：

• tbl_forms：form_id（主键）、form_title（表单标题）等表单核心字段
• tbl_tags：tag_id（主键）、tag_name（标签名）、color（标签颜色）
• x_form_tags：form_id（外键关联tbl_forms）、tag_id（外键关联tbl_tags）

推荐查询语句（SQL Server 2016+） #

SELECT
    f.form_id,
    f.form_title,
    -- 子查询生成对应表单的标签JSON对象数组
    (
        SELECT
            t.tag_name AS tag,
            t.color
        FROM x_form_tags xt
        JOIN tbl_tags t ON xt.tag_id = t.tag_id
        WHERE xt.form_id = f.form_id
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS tags
FROM tbl_forms f;

说明 #

• FOR JSON PATH会自动将子查询的标签集合转为JSON格式，WITHOUT_ARRAY_WRAPPER用于移除默认的外层额外数组包裹，最终tags字段会直接返回[{"tag":"标签1","color":"#ff0000"},{"tag":"标签2","color":"#00ff00"}]这样的对象数组
• 如果需要将整个查询结果直接输出为完整JSON，可在主查询末尾追加FOR JSON PATH

兼容低版本的替代方案 #

如果你的SQL Server版本低于2016，无法使用FOR JSON，可以用STRING_AGG手动拼接JSON数组：

SELECT
    f.form_id,
    f.form_title,
    CONCAT('[', STRING_AGG(CONCAT('{"tag":', STRING_ESCAPE(t.tag_name, 'json'), ',"color":', STRING_ESCAPE(t.color, 'json'), '}'), ','), ']') AS tags
FROM tbl_forms f
LEFT JOIN x_form_tags xt ON f.form_id = xt.form_id
LEFT JOIN tbl_tags t ON xt.tag_id = t.tag_id
GROUP BY f.form_id, f.form_title;

这里用STRING_ESCAPE处理标签名或颜色中的特殊字符（比如双引号），避免JSON格式出错。

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