PostgreSQL中如何基于jsonb数组值关联表并聚合匹配Type数组

你原来的查询语句存在多个明显问题，是导致执行异常的核心原因：

1. 笛卡尔积性能问题：ON TRUE的关联逻辑会先生成Games和Items两张表的全量笛卡尔积，只要表中数据量稍大，执行效率会指数级下降，这是长时间无返回也无报错的核心原因。
2. 关联条件位置错误：LEFT JOIN的匹配条件写在WHERE子句中，会过滤掉没有匹配到Items的Games记录，相当于隐式转成了INNER JOIN，不符合保留Games全量数据的需求。
3. 缺失分组逻辑：使用了ARRAY_AGG聚合函数但未添加GROUP BY子句，严格SQL模式下会直接抛出语法错误。
4. 冗余写法：Objects本身已是jsonb类型无需额外强转，#> '{game_ids}'可以简化为更易读的-> 'game_ids'写法。

SELECT
  -- 无匹配时返回空数组而非NULL，可根据需求调整该逻辑
  COALESCE(ARRAY_AGG(it.Type), '{}'::text[]) AS Types,
  g.*
FROM Games g
LEFT JOIN Items it
  -- 匹配条件直接写在JOIN的ON子句中
  ON (
    it.Objects -> 'game_ids' ? g.GameId
    OR it.Objects -> 'tag_ids' ? g.Tag
  )
-- 按Games表主键分组，即可覆盖所有Games表字段的聚合分组要求
GROUP BY g.GameId;

如果你的Games表未设置主键，可将GROUP BY子句替换为所有Games表字段，例如GROUP BY g.Title, g.GameId, g.Genre, g.Tag。

如果两张表数据量较大，可给Items表的Objects字段创建GIN索引，专门优化jsonb数组的存在性查询（即?操作符的查询场景），能大幅提升关联速度：

CREATE INDEX idx_items_objects_gin ON Items USING GIN (Objects);

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