如何在PostgreSQL的jsonb字段中结合索引查询文本与数字

我有一个名为base的PostgreSQL 15.1表，包含jsonb类型的addresses字段，用于存储同一用户的多个地址。表结构和测试数据如下：

create table base(name text, addresses jsonb);

insert into base(name, addresses) values('John', '[{"zip":"01431000","state":"SP","number":100,"street1":"Avenida Brasil","city_name":"São Paulo"},{"zip":"01310900","state":"SP","number":200,"street1":"Avenida Paulista","city_name":"São Paulo"}]');
insert into base(name, addresses) values('Doe', '[{"zip":"01332000","state":"SP","number":250,"street1":"Rua Itapeva","city_name":"São Paulo"}]');

addresses字段的JSON结构示例：

[
  {
    "zip":"01431000",
    "state":"SP",
    "number":100,
    "street1":"Avenida Brasil",
    "city_name":"São Paulo"
  },
  {
    "zip":"01310900",
    "state":"SP",
    "number":200,
    "street1":"Avenida Paulista",
    "city_name":"São Paulo"
  }
]

需要实现**同时匹配street1的部分文本（如用to_tsquery('simple', 'paulista')）和特定number值（如200）**的查询，且要利用索引提升性能。当前已创建的jsonb_path_ops索引仅支持精确包含查询，无法满足需求。

以下是几种可行的索引策略和对应查询方式，可根据实际场景选择：

方案1：JSON Path查询 + 通用GIN索引 #

适合需要灵活匹配JSON结构的场景，PostgreSQL 12+支持JSON Path语法。

步骤1：创建通用GIN索引 #

替换原有的jsonb_path_ops索引（或新增），使用默认的jsonb_ops算子支持JSON Path查询：

CREATE INDEX base_addresses_json_ops_idx ON base USING GIN (addresses);

步骤2：编写查询语句 #

直接用JSON Path语法匹配数组中同时满足条件的元素：

SELECT *
FROM base
WHERE addresses @@ '$.[] ? (@.street1 like_regex "paulista" flag "i" && @.number == 200)'::jsonpath;

• flag "i"表示不区分大小写，可根据需求移除
• 该查询会利用上述GIN索引加速匹配

方案2：Trigram索引 + 数组展开查询 #

适合仅需简单模糊匹配（如LIKE）的场景，性能优于全文检索。

步骤1：创建Trigram复合索引 #

针对数组元素的street1和number创建索引：

CREATE INDEX base_addr_street_trgm_number_idx ON base USING GIN (
  (jsonb_array_elements(addresses)->>'street1') gin_trgm_ops,
  (jsonb_array_elements(addresses)->>'number')::int
);

步骤2：编写查询语句 #

通过展开JSON数组过滤符合条件的记录：

SELECT DISTINCT b.*
FROM base b, jsonb_array_elements(b.addresses) addr
WHERE addr->>'street1' ILIKE '%paulista%'
  AND (addr->>'number')::int = 200;

• DISTINCT用于避免同一用户因多个地址匹配而重复返回

方案3：全文检索索引 + EXISTS子查询 #

适合需要复杂文本匹配（如多词检索）的场景。

步骤1：创建全文检索表达式索引 #

CREATE INDEX base_addr_street1_ts_number_idx ON base USING GIN (
  to_tsvector('simple', jsonb_array_elements(addresses)->>'street1'),
  (jsonb_array_elements(addresses)->>'number')::int
);

步骤2：编写查询语句 #

SELECT DISTINCT b.*
FROM base b
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(b.addresses) addr
  WHERE to_tsvector('simple', addr->>'street1') @@ to_tsquery('simple', 'paulista')
    AND (addr->>'number')::int = 200
);

方案4：生成列 + GIN索引（最优性能） #

适合频繁进行此类组合查询的场景，预先计算可索引的字段组合。

步骤1：创建生成列 #

提取所有地址的street1和number组合为新的jsonb列：

ALTER TABLE base ADD COLUMN addr_street_number jsonb GENERATED ALWAYS AS (
  jsonb_agg(jsonb_build_object('street1', elem->>'street1', 'number', elem->>'number'))
  FROM jsonb_array_elements(addresses) AS elem
) STORED;

步骤2：创建GIN索引 #

CREATE INDEX base_addr_street_number_gin_idx ON base USING GIN (addr_street_number);

步骤3：编写查询语句 #

SELECT *
FROM base
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(addr_street_number) AS addr
  WHERE to_tsvector('simple', addr->>'street1') @@ to_tsquery('simple', 'paulista')
    AND (addr->>'number')::int = 200
);

执行查询前可通过EXPLAIN ANALYZE查看执行计划，确认索引是否被命中：

EXPLAIN ANALYZE
SELECT * FROM base WHERE addresses @@ '$.[] ? (@.street1 like_regex "paulista" && @.number == 200)'::jsonpath;

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