PostgreSQL 12中JSONB嵌套数组字符串值的索引优化咨询

Trigram索引完全适用于你的场景——它正是为优化LIKE '%xxx%'这类前后带通配符的模糊查询设计的，结合JSONB的多层嵌套数组结构，我们可以通过表达式索引实现高效检索。

步骤1：安装pg_trgm扩展 #

Trigram功能依赖PostgreSQL的pg_trgm扩展，先确认已安装：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

步骤2：创建针对性的Trigram索引 #

由于数据是多层嵌套数组，需要先提取出所有符合articleSize='XXL'的articleName值，再对这些值建立Trigram索引。使用jsonb_path_query_array可以高效提取嵌套路径下的目标字段：

CREATE INDEX idx_invoice_article_name_size ON invoice
USING GIN (
  (jsonb_path_query_array(invoice_parts, '$.groups[*].categories[*].items[*] ? (@.articleSize == "XXL").articleName')) gin_trgm_ops
);

• jsonb_path_query_array：从多层嵌套的JSONB中，筛选出articleSize等于XXL的所有articleName并返回数组。
• gin_trgm_ops：指定使用GIN索引的Trigram操作符类，支持模糊匹配的快速检索。

步骤3：优化查询语句（可选但推荐） #

原查询通过交叉连接展开所有数组元素，会返回重复的发票记录。改用EXISTS子查询可以只返回符合条件的唯一发票，同时让索引更好地生效：

SELECT * FROM invoice i
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(i.invoice_parts) parts,
       jsonb_array_elements(parts -> 'groups') groups,
       jsonb_array_elements(groups -> 'categories') categories,
       jsonb_array_elements(categories -> 'items') items
  WHERE items ->> 'articleName' LIKE '%name%'
    AND items ->> 'articleSize' = 'XXL'
);

通用场景优化（如果articleSize不固定） #

如果你的查询需要支持不同的articleSize值，可以创建包含articleName和articleSize键值对的数组索引，适配更灵活的查询：

CREATE INDEX idx_invoice_items_name_size ON invoice
USING GIN (
  (jsonb_path_query_array(invoice_parts, '$.groups[*].categories[*].items[*].{"name": @.articleName, "size": @.articleSize}'))
);

对应的查询可以改用jsonb_path_exists，利用索引快速匹配：

SELECT * FROM invoice i
WHERE jsonb_path_exists(
  i.invoice_parts, 
  '$.groups[*].categories[*].items[*] ? (@.articleName like_regex ".*name.*" && @.articleSize == "XXL")'
);

验证索引生效 #

使用EXPLAIN ANALYZE执行查询，查看计划中是否出现Index Scan using idx_invoice_article_name_size on invoice，确认索引被正确使用。

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