PostgreSQL 10如何通过查询返回指定结构的JSON数组？

这事儿用PostgreSQL的JSON聚合函数就能轻松搞定，直接给你写好完整的查询语句，再拆解下逻辑：

SELECT json_agg(
         json_build_object(
           'name', m.name,
           'substances', array_agg(s.name ORDER BY s.id)
         )
       ) AS result
FROM main m
LEFT JOIN link l ON m.id = l.id_main
LEFT JOIN substances s ON l.id_substance = s.id
GROUP BY m.id, m.name
ORDER BY m.id;

拆解每个部分的作用： #

• 关联表：用LEFT JOIN把main、link和substances三张表关联起来，这样哪怕main里的某条记录没有对应物质（示例里没这种情况，但实际场景可能存在），也能被保留下来，避免丢失数据。如果确定所有main记录都有对应物质，换成INNER JOIN也没问题。
• 聚合物质名称：array_agg(s.name ORDER BY s.id)会把每个main记录对应的所有物质名称收集成一个数组，加ORDER BY s.id是为了和你示例里的物质顺序保持一致。
• 构建JSON对象：json_build_object函数用来生成每个包含name（主表名称）和substances（物质数组）的JSON对象。
• 生成最终JSON数组：最后用json_agg把所有单个的JSON对象聚合起来，形成你需要的JSON数组结构。

执行这个查询后，就能得到你想要的结果啦～

内容的提问来源于stack exchange，提问作者François Romain