如何用MySQL查询关联表并生成指定格式的JSON输出结果？

针对你的笔记与标签多对多关联结构，以下是两种生成目标JSON格式的SQL查询方案：

方案1：MySQL 5.7.22+ 推荐写法（原生JSON函数） #

利用JSON_ARRAYAGG直接聚合标签ID为数组，再通过JSON_OBJECT构造单条笔记的JSON对象，最后用JSON_ARRAYAGG包装成外层数组：

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id', n.id,
        'title', n.title,
        'markdown', n.markdown,
        'tagIds', COALESCE(JSON_ARRAYAGG(nt.tagId), JSON_ARRAY())
    )
) AS result
FROM notes n
LEFT JOIN noteTags nt ON n.id = nt.noteId
GROUP BY n.id, n.title, n.markdown;

• LEFT JOIN确保无标签的笔记也能被查询到
• COALESCE用来处理无标签的情况，返回空数组[]而非NULL
• 执行后result字段就是你需要的完整JSON数组

如果只需要单条笔记的结构（不包装外层数组），可以用简化版：

SELECT 
    n.id,
    n.title,
    n.markdown,
    COALESCE(JSON_ARRAYAGG(nt.tagId), JSON_ARRAY()) AS tagIds
FROM notes n
LEFT JOIN noteTags nt ON n.id = nt.noteId
GROUP BY n.id, n.title, n.markdown
ORDER BY n.id;

方案2：兼容低版本MySQL（5.7以前） #

如果你的MySQL版本不支持JSON_ARRAYAGG，可以用GROUP_CONCAT拼接标签ID字符串，再手动转为JSON数组格式：

SELECT 
    n.id,
    n.title,
    n.markdown,
    CONCAT('[', IFNULL(GROUP_CONCAT(DISTINCT nt.tagId SEPARATOR ','), ''), ']') AS tagIds
FROM notes n
LEFT JOIN noteTags nt ON n.id = nt.noteId
GROUP BY n.id, n.title, n.markdown
ORDER BY n.id;

• DISTINCT避免重复标签ID（虽然题目说明笔记和标签关联唯一，可根据实际情况省略）
• 若标签数量较多，可能需要调整group_concat_max_len系统参数，避免拼接内容被截断

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