如何编写SQL查询将用户与关联笔记按JSON格式分组返回？

首先修正你的notes表建表语句，user_id字段需要指定数据类型：

CREATE TABLE notes(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT UNIQUE,
  user_id INTEGER FOREIGN KEY(user_id) REFERENCES users(id)
);

要实现你需要的查询结果，可以利用SQLite的JSON函数组合完成，最终的SELECT语句如下：

SELECT json_group_array(
         json_object(
           'name', users.name,
           'notes', COALESCE(json_group_array(
             json_object('id', notes.id, 'content', notes.content)
           ), '[]')
         )
       ) AS user_notes
FROM users
LEFT JOIN notes ON users.id = notes.user_id
GROUP BY users.id;

语句说明： #

• 使用LEFT JOIN关联用户和笔记，确保没有笔记的用户也会被包含（如果不需要这类用户，可以换成INNER JOIN）
• json_object('id', notes.id, 'content', notes.content)：将单条笔记数据转换为标准JSON对象
• json_group_array(...)：把当前用户的所有笔记对象聚合为一个JSON数组，COALESCE用来处理无笔记的用户，返回空数组[]而非NULL
• 外层的json_object('name', users.name, 'notes', ...)：将用户名和对应的笔记数组组合成单个用户的JSON对象
• 最外层的json_group_array(...)：把所有用户对象聚合为最终的JSON数组

查询结果（标准JSON格式）： #

[
  {"name":"John","notes":[{"id":1,"content":"John's 1st Note"},{"id":2,"content":"John's 2nd Note"}]},
  {"name":"Jane","notes":[{"id":3,"content":"Jane's 1st Note"}]}
]

注：你期望的结果是非标准JSON（键名未加引号），实际业务中建议使用标准JSON格式，前端解析会更稳定。

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