PostgreSQL jsonb列数组索引创建及WHERE IN查询实现

一、实现支持多值的IN风格查询 #

你当前用LIKE的方式不仅效率低，还容易出现误匹配（比如A55会误匹配到A555），推荐用PostgreSQL原生的jsonb数组操作来实现多值查询，有两种常用方式：

方式1：使用@>操作符匹配单个或多个值 #

@>是jsonb的包含操作符，可以检查数组是否包含指定元素，多个值的话用OR拼接，或者把查询值包装成jsonb数组后按需匹配：

-- 单个值查询
SELECT *
FROM permissions.application_settings
WHERE configuration @> '{"subscriberCodes": ["A555"]}';

-- 匹配包含任一指定code的行（IN风格）
SELECT *
FROM permissions.application_settings
WHERE configuration @> '{"subscriberCodes": ["A555"]}'
   OR configuration @> '{"subscriberCodes": ["A666"]}';

-- 匹配同时包含所有指定code的行（按需选择）
SELECT *
FROM permissions.application_settings
WHERE configuration @> '{"subscriberCodes": ["A555", "A666"]}';

方式2：展开数组后用IN子句 #

如果需要严格匹配数组中存在任一IN列表中的值，可以用jsonb_array_elements_text把数组展开成行，再关联查询：

SELECT DISTINCT t.*
FROM permissions.application_settings t
CROSS JOIN jsonb_array_elements_text(t.configuration->'subscriberCodes') AS sc
WHERE sc IN ('A555', 'A666');

注意加DISTINCT避免同一行因多个匹配元素被重复返回。

二、创建GIN索引避免全表扫描 #

为了让上述查询走索引而非全表扫描，你需要给configuration列创建GIN索引，GIN索引天然支持jsonb的@>等操作符：

CREATE INDEX idx_application_settings_config_subscriber_codes
ON permissions.application_settings
USING GIN (configuration jsonb_path_ops);

这里用jsonb_path_ops是专门针对jsonb路径操作的优化索引，比普通GIN索引更小、查询更快，适合这种针对特定键的数组查询场景。

如果你的PostgreSQL版本在12及以上，还可以创建仅针对subscriberCodes数组的局部GIN索引，进一步缩小索引体积：

CREATE INDEX idx_application_settings_subscriber_codes
ON permissions.application_settings
USING GIN ((configuration->'subscriberCodes') jsonb_path_ops);

验证索引是否生效 #

创建索引后，可以用EXPLAIN ANALYZE查看查询计划，确认是否使用了索引：

EXPLAIN ANALYZE
SELECT *
FROM permissions.application_settings
WHERE configuration @> '{"subscriberCodes": ["A555"]}';

如果计划中出现Index Scan using idx_application_settings_config_subscriber_codes on application_settings，说明索引已生效。

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