PostgreSQL基于JSONB的关联查询TypeORM实现及性能问题排查

CREATE TABLE product(id SERIAL PRIMARY KEY, name text, categories JSONB);

INSERT INTO product(name, categories) VALUES
   ('prouct1', '{"ids":[4,5]}'),
   ('prouct2', '{"ids":[5,6]}'),
   ('prouct3', '{"ids":[7]}');

CREATE TABLE category(id bigint, rootid bigint);

INSERT INTO category(id, rootid) VALUES
   (1, null),
   (2, null),
   (3, null),
   (4, 1),
   (5, 2),
   (6, 1),
   (7, 3);

jsonb_array_elements_text是PostgreSQL专属的返回集合类函数，直接使用TypeORM的QueryBuilder自定义原生JOIN语句即可实现。注意修正你原生SQL中的两处笔误：b.categories的别名b不存在应为p，category表主键是id不是categoryid。
参考实现代码：

import { getRepository } from 'typeorm';
import { Product } from './entities/Product'; // 替换为你自己的Product实体路径

const productRepo = getRepository(Product);
const queryResult = await productRepo
  .createQueryBuilder('p')
  // 对应jsonb_array_elements_text的关联逻辑，直接写原生表达式
  .innerJoin('jsonb_array_elements_text(p.categories->\'ids\')', 'pc', 'TRUE')
  .innerJoin('category', 'c', 'pc.categoryid = c.id AND c.rootid = :targetRootId', { targetRootId: 1000 })
  .select(['p.id', 'p.name', 'p.categories'])
  // 如果需要返回实体实例可以换用.getMany()
  .getRawMany();

• 子查询强制全量展开所有product行的categories数组：无论后续关联是否需要，都会先扫描整张product表、解析所有JSONB字段的ids数组，数据量越大额外开销越高。
• 缺少JSONB索引：没有针对categories->'ids'建立GIN索引的情况下，PostgreSQL无法快速过滤符合条件的product行，每次查询都要全量解析JSONB内容。
• 隐式类型转换损耗：jsonb_array_elements_text返回的是文本类型，和category表的bigint类型id关联时，每次都需要执行类型转换，增加计算开销。

• 优先使用第一种JOIN写法：允许PostgreSQL查询优化器下推过滤条件，不需要全量展开所有product的数组，仅处理符合后续关联条件的行。
• 新增GIN索引加速JSONB数组查询，执行SQL：CREATE INDEX idx_product_categories_ids ON product USING GIN ((categories->'ids'));
• 可以将jsonb_array_elements_text替换为jsonb_array_elements后手动转bigint，避免隐式转换：(jsonb_array_elements(p.categories->'ids')::text::bigint)

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