PostgreSQL中为JSONB内id字段创建索引报错求助

问题背景 #

已创建wine表，SQL语句如下：

create table wine
(
   id       varchar(50) not null primary key, 
   payload  jsonb not null default '{}'::jsonb
);

其中payload为单个JSON对象，示例结构：

{
"country": "US",
"province": "Planted in 1988, this vineyard's fruit captures the unique minerality found in this AVA. It leaves an impression of sucking on rock—not a bad thing in a wine. The black-cherry fruit grips the palate with real power and some density, with potent herbal highlights as well. Drink this now through the mid-2020s.",
"variety": "Meredith Mitchell Vineyard",
"price": "90",
"description": "48.0",
"id": "976",
"designation": "Oregon",
"winery": "McMinnville",
"region_1": "Willamette Valley",
"region_2": "Pinot Noir",
"points": "Winderlea"
}

为加速select * from wine where payload @> '{"id": "{id}"}';这类查询，编写了如下函数：

create or replace function jsonb_values_of_key(jsonb, wanted_key text)
  returns text
  language sql
  immutable
as $$
  select array_to_string(array(select attr.value from jsonb_array_elements($1) arrayElem, jsonb_each(arrayElem) attr WHERE attr.key=wanted_key),' ') 
$$;

尝试创建索引时触发报错：

ERROR:  cannot extract elements from an object
CONTEXT:  SQL function "jsonb_values_of_key" statement 1
SQL state: 22023

错误原因 #

函数里的jsonb_array_elements($1)是用来解析JSON数组的，但你的payload是单个JSON对象，不是数组，调用这个函数时自然会抛出"无法从对象提取元素"的错误。

可行的索引方案 #

方案1：给整个JSONB字段建GIN索引 #

如果经常使用@>操作符查询，可以直接给payload字段创建GIN索引：

CREATE INDEX wine_payload_idx ON wine USING GIN (payload);

这个索引完全支持payload @> '{"id": "976"}'这类查询，性能满足需求。

方案2：针对JSON内的id字段建B-tree索引 #

如果只需要查询payload里的id字段，创建B-tree索引更高效：

CREATE INDEX wine_payload_id_idx ON wine ((payload->>'id'));

查询时可以直接写成：

SELECT * FROM wine WHERE payload->>'id' = '976';

即使保持原@>的查询写法，PostgreSQL也能自动利用这个索引。

方案3：针对id字段创建trgm GIN索引（用于模糊查询） #

如果需要对id做模糊匹配，再用trgm索引：

CREATE INDEX wine_payload_id_trgm_idx ON wine USING GIN ((payload->>'id') gin_trgm_ops);

这个索引支持payload->>'id' LIKE '%76%'这类模糊查询场景。

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