PostgreSQL带关联的SELECT查询结果如何转换为JSON对象？

无需逐个声明chat_messages表字段，可直接通过JSON拼接运算符实现需求，具体实现如下：

方案代码（适配PostgreSQL 9.5及以上版本） #

SELECT 
  to_jsonb(cm.*) || jsonb_build_object(
    'user', to_jsonb(u.*),
    'event', to_jsonb(e.*)
  ) AS result
FROM chat_messages cm
INNER JOIN events e ON cm.event_id = e.id
INNER JOIN users u ON cm.user_id = u.id;

方案说明 #

• to_jsonb(cm.*)会直接将chat_messages表的所有字段转换为JSONB对象，不需要手动罗列每一个字段，表结构变更后无需修改SQL
• ||为JSONB拼接运算符，会将后面的user、event嵌套对象追加到chat_messages生成的JSON对象中，输出结构完全符合需求
• 若chat_messages表本身存在user或event字段，拼接时会自动覆盖原有字段值；如果需要保留原有字段，可以调整键名或者提前删除冲突字段：

  -- 提前删除cm中冲突的user字段再拼接
  to_jsonb(cm.*) - 'user' || jsonb_build_object('user', to_jsonb(u.*))
  

• 如果你需要使用JSON类型而非JSONB，PostgreSQL 14及以上版本可直接替换jsonb为json，低版本可最后强转回JSON：

  SELECT 
    (to_jsonb(cm.*) || jsonb_build_object(
      'user', to_jsonb(u.*),
      'event', to_jsonb(e.*)
    ))::json AS result
  FROM chat_messages cm
  INNER JOIN events e ON cm.event_id = e.id
  INNER JOIN users u ON cm.user_id = u.id;
  

批量返回JSON数组 #

如果需要将多条查询结果整合成一个JSON数组，外层套json_agg即可：

SELECT 
  json_agg(
    to_jsonb(cm.*) || jsonb_build_object(
      'user', to_jsonb(u.*),
      'event', to_jsonb(e.*)
    )
  ) AS result_list
FROM chat_messages cm
INNER JOIN events e ON cm.event_id = e.id
INNER JOIN users u ON cm.user_id = u.id;

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