使用json_build_object关联嵌套属性时查询结果异常的问题排查

表结构与测试数据 #

create table product(id serial primary key);
create table product_image(id serial primary key, product_id bigint references product(id));
create table chat(id serial primary key, product_id bigint references product(id));

insert into product values(1), (2), (3);
insert into product_image values(30, 1), (31, 1);
insert into chat values(50, 1), (51, 2);

期望查询结果 #

chat_id |  product                                 |
---------+------------------------------------------+
      50 |  {"id":1,"images":[{"id":30},{"id":31}]} |
      51 |  {"id":2,"images":[]}                    |

你的SQL问题分析 #

你写的SQL存在三个核心问题：

• 内连接导致重复行：使用join product_image会让有多个图片的产品（比如id=1）对应的chat记录被重复输出，无法得到单条聚合结果。
• 子查询无关联过滤：to_jsonb中的子查询没有关联当前行的p.id，会返回所有product_image的记录，而不是当前产品对应的图片。
• 冗余关联表：子查询中重复关联product表完全多余，且没有限定条件，导致数据混乱。

正确SQL实现 #

select
  c.id as chat_id,
  json_build_object(
    'id', p.id,
    'images', coalesce(json_agg(json_build_object('id', pi.id)) filter (where pi.id is not null), '[]'::jsonb)
  ) as product
from chat c
join product p on p.id = c.product_id
left join product_image pi on pi.product_id = p.id
group by c.id, p.id

逻辑说明 #

• 使用left join product_image：确保没有图片的产品（比如id=2）也能保留对应的chat记录，不会被过滤。
• json_agg聚合图片：将当前产品的所有图片id组装成JSON数组，配合filter (where pi.id is not null)排除空值，再用coalesce将空聚合结果替换为[]，符合期望格式。
• group by c.id, p.id：按聊天记录和产品分组，确保每个chat只返回一条聚合后的结果。

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