如何对JSON-b字段执行IN检索并命中GIN索引

你现有表结构如下（假设表名为point_records）：

id | data
----------
1  | '{"points": [{"id": 10, "address": "Test 1"}, {"id": 20, "address": "Test 2"}, {"id": 30, "address": "Test 3"}]}'
2  | '{"points": [{"id": 40, "address": "Test 444"}, {"id": 20, "address": "Test 222"}, {"id": 50, "address": "Test 555"}]}'

1. 创建适配查询的GIN索引 #

针对points数组创建专用GIN索引，比全字段GIN索引占用空间更小、查询性能更高：

-- 使用jsonb_path_ops运算符类，针对包含查询做了专项优化，性能比默认实现高3倍左右
CREATE INDEX idx_point_records_points_gin ON point_records USING GIN ((data->'points') jsonb_path_ops);

2. 实现IN效果的查询语句 #

使用jsonb@>包含运算符实现匹配逻辑，完全兼容GIN索引，同时支持ID数组从子查询获取。

固定ID数组版本（匹配[40,20]） #

-- 查询所有包含目标ID的points子元素
SELECT pr.id, point_item
FROM point_records pr,
     jsonb_array_elements(pr.data->'points') AS point_item
WHERE pr.data->'points' @> ANY(
  ARRAY(SELECT jsonb_build_object('id', target_id) FROM unnest(ARRAY[40,20]) AS target_id)::jsonb[]
);

-- 如果仅需要匹配包含任意目标ID的整行数据，可简化为
SELECT * FROM point_records
WHERE data->'points' @> ANY(ARRAY['{"id":40}', '{"id":20}']::jsonb[]);

子查询获取ID版本 #

假设目标ID从target_table表的id字段查询获得，替换子查询逻辑即可：

SELECT pr.id, point_item
FROM point_records pr,
     jsonb_array_elements(pr.data->'points') AS point_item
WHERE pr.data->'points' @> ANY(
  -- 此处替换为你的实际子查询逻辑
  ARRAY(SELECT jsonb_build_object('id', t.id) FROM target_table t WHERE t.filter_condition = 'xxx')::jsonb[]
);

验证索引生效 #

执行以下语句查看执行计划，确认输出中出现你创建的GIN索引名称即可：

EXPLAIN ANALYZE SELECT * FROM point_records
WHERE data->'points' @> ANY(ARRAY['{"id":40}', '{"id":20}']::jsonb[]);

如果执行计划出现Index Scan using idx_point_records_points_gin on point_records字样，说明GIN索引已正常触发。

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