原生SQL实现PostgreSQL多对多查询 聚合关联标签为嵌套数组结构

PostgreSQL内置的JSON聚合函数可以单条SQL直接查出符合要求的结构，不需要在Node.js层做二次数据拼装。

可直接使用的SQL语句 #

SELECT
  q.id,
  q.content,
  COALESCE(
    jsonb_agg(
      jsonb_build_object('id', t.id, 'name', t.name)
    ) FILTER (WHERE t.id IS NOT NULL),
    '[]'::jsonb
  ) AS tags
FROM questions q
LEFT JOIN questions_tags qt ON q.id = qt.question_id
LEFT JOIN tags t ON qt.tag_id = t.id
GROUP BY q.id, q.content
ORDER BY q.id;

语句说明 #

• 用LEFT JOIN做关联，保证没有绑定任何标签的问题也会出现在结果里，不会像内连接一样漏掉无标签的问题记录
• jsonb_build_object把单条标签的id、name字段组装成预期的对象结构
• jsonb_agg将同一个问题下的所有标签对象聚合成JSON数组
• FILTER子句过滤掉左连接空匹配时产生的null值，配合COALESCE把无标签问题的tags字段兜底为空数组，避免返回null

node-postgres适配说明 #

node-postgres默认会将PostgreSQL的jsonb类型自动解析为JavaScript原生对象/数组，执行上述SQL后拿到的rows结果直接就是需要的结构，不需要额外做JSON反序列化或者手动分组拼装：

const { rows } = await pool.query(`/* 填入上述SQL语句 */`);
// 返回的rows结构完全匹配预期
// [
//   {
//     id: 1,
//     content: { someProp: 'someValue' },
//     tags: [ {id:58, name:'sometag58'}, {id:216, name:'sometag216'} ]
//   },
//   ...
// ]

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