PostgreSQL如何将关联查询的指定列转换为JSONB对象？

针对你的需求，我们可以利用PostgreSQL的JSON聚合函数，基于原内连接查询进行分组聚合，实现两种不同结构的JSONB列：

方案1：单对象嵌套数组（[{"城市名":"人口数",...}]） #

使用jsonb_object_agg将城市和人口聚合为一个键值对对象，再用jsonb_build_array将该对象包裹为数组：

SELECT
  t1.id,
  t1.label,
  jsonb_build_array(jsonb_object_agg(t2.city, t2.ppl)) AS city_population
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.country_id
GROUP BY t1.id, t1.label;

说明 #

• jsonb_object_agg(t2.city, t2.ppl)：将每个国家对应的所有城市-人口转换为一个JSON对象，键为城市名，值为人口数
• jsonb_build_array(...)：将上述对象包裹成一个单元素数组，匹配你要求的[{"城市名":"人口数",...}]结构

方案2：多对象数组（[{"city":"城市名", "ppl":"人口数"},...]） #

使用jsonb_build_object构造单个城市-人口对象，再用jsonb_agg将这些对象聚合为数组：

SELECT
  t1.id,
  t1.label,
  jsonb_agg(jsonb_build_object('city', t2.city, 'ppl', t2.ppl)) AS city_population
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.country_id
GROUP BY t1.id, t1.label;

说明 #

• jsonb_build_object('city', t2.city, 'ppl', t2.ppl)：为每一行城市数据构造一个包含city和ppl键的JSON对象
• jsonb_agg(...)：将同一国家的所有城市对象聚合为一个JSON数组，匹配你要求的[{"city":"城市名", "ppl":"人口数"},...]结构

注意：如果存在城市名重复的情况，jsonb_object_agg会保留最后一条重复城市的数据；若需要处理重复值，可以先通过DISTINCT或者分组去重后再聚合。

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