关联同表两个数组字段返回异常结果的原因排查

数据库结构与初始数据 #

create schema my_schema;

create table support_conversation (
  id serial primary key,
  participant_ids bigint[],
  active_participant_ids bigint[]
);

create table participant (
  id serial primary key
);

insert into participant values (1), (2);
insert into support_conversation values (1, '{1,2}', '{2}');

执行的查询语句 #

select
  sc.*,
  to_json(array_agg(p1.*)) as all_conversation_participants,
  to_json(array_agg(p2.*)) as participants_currently_in_chat
from support_conversation sc
  join participant p1
    on p1.id = any(sc.participant_ids)
  join participant p2
    on p2.id = any(sc.active_participant_ids)
where sc.active_participant_ids && '{2}'
and sc.participant_ids && '{2}'
group by sc.id;

异常查询结果 #

{
  . . .
  "all_conversation_participants": [
    { "id": 1 },
    { "id": 2 }
  ],
  "participants_currently_in_chat": [
    { "id": 2 },
    { "id": 2 }
  ]
}

修改数据后的结果 #

将对话表的插入语句修改为：

-- insert into support_conversation values (1, '{1,2}', '{2}'); -- 修改前
insert into support_conversation values (1, '{1,2}', '{1,2}'); -- 修改后

此时查询结果变为：

{
  . . .
  "all_conversation_participants": [
    { "id": 1 },
    { "id": 1 },
    { "id": 2 },
    { "id": 2 }
  ],
  "participants_currently_in_chat": [
    { "id": 1 },
    { "id": 2 },
    { "id": 1 },
    { "id": 2 }
  ]
}

期望结果 #

{
  "id": 1,
  "participant_ids": [1, 2],
  "active_participant_ids": [1, 2],
  "all_conversation_participants": [
    { "id": 1 },
    { "id": 2 }
  ],
  "participants_currently_in_chat": [
    { "id": 2 }
  ]
}

异常原因分析 #

问题根源是两次JOIN操作产生了笛卡尔积：

1. 第一次关联participant表和participant_ids数组时，对话1对应2个参与者，生成2行数据；
2. 第二次关联participant表和active_participant_ids数组时，会基于已有的2行数据再次关联：
   • 初始数据中活跃参与者只有1个，2行×1行得到2行结果；
   • 修改后活跃参与者有2个，2行×2行得到4行结果；
3. array_agg会对笛卡尔积的结果集直接聚合，自然会出现重复的参与者记录。

解决方法 #

方案1：子查询单独聚合（推荐） #

避免笛卡尔积，对每个参与者集合单独做子查询聚合：

select
  sc.*,
  (select to_json(array_agg(p.*)) from participant p where p.id = any(sc.participant_ids)) as all_conversation_participants,
  (select to_json(array_agg(p.*)) from participant p where p.id = any(sc.active_participant_ids)) as participants_currently_in_chat
from support_conversation sc
where sc.active_participant_ids && '{2}'
and sc.participant_ids && '{2}';

方案2：聚合时去重 #

在array_agg中加入distinct关键字去除重复记录（仅作为临时 workaround，本质还是先解决笛卡尔积更合理）：

select
  sc.*,
  to_json(array_agg(distinct p1.*)) as all_conversation_participants,
  to_json(array_agg(distinct p2.*)) as participants_currently_in_chat
from support_conversation sc
  join participant p1
    on p1.id = any(sc.participant_ids)
  join participant p2
    on p2.id = any(sc.active_participant_ids)
where sc.active_participant_ids && '{2}'
and sc.participant_ids && '{2}'
group by sc.id;

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