PostgreSQL 12如何为jsonb数组内对象的type字段创建索引

jsonb_values_of_key不是PostgreSQL原生内置函数，数据库没有对应函数定义，执行时自然会抛出不存在的错误。

根据实际查询场景选对应方案即可：

• 方案1：适配等值包含查询（最常用）
  如果你的需求是查找events_info数组中存在type为指定值的记录，完全不需要单独提取字段，直接给events_info字段建GIN索引，配合jsonb包含操作符@>就能命中索引：

-- 建GIN索引
CREATE INDEX event_info_gin_idx ON events USING GIN (events_info);

查询写法示例：

-- 查找所有包含type=INTERNAL元素的记录
SELECT * FROM events WHERE events_info @> '[{"type": "INTERNAL"}]'::jsonb;

这个方案不需要自定义任何函数，写法简单，等值匹配性能很高，而且索引会覆盖jsonb里的所有字段，后续查其他字段的包含关系也能复用，不用单独建多个索引，适合绝大多数常规业务场景。

• 方案2：自定义提取函数建索引，支持等值、模糊、前缀匹配
  如果需要针对type字段做更灵活的查询（比如模糊匹配、前缀匹配、单独聚合type值做统计），可以先创建一个不可变SQL函数，提取jsonb数组中指定key的所有值组成文本数组，再基于这个函数的返回值建索引。
  首先创建提取值的函数：

CREATE OR REPLACE FUNCTION get_jsonb_arr_key_values(input_json jsonb, target_key text)
RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
    SELECT ARRAY(SELECT jsonb_array_elements(input_json)->>target_key);
$$;

先安装模糊查询需要的pg_trgm扩展，再创建索引：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 创建支持模糊查询的GIN索引
CREATE INDEX event_info_type_idx ON events USING GIN (get_jsonb_arr_key_values(events_info, 'type') gin_trgm_ops);

对应查询写法示例：

-- 等值匹配
SELECT * FROM events WHERE get_jsonb_arr_key_values(events_info, 'type') @> ARRAY['INTERNAL'];
-- 前缀匹配
SELECT * FROM events WHERE get_jsonb_arr_key_values(events_info, 'type')::text LIKE 'INTER%';
-- 模糊匹配
SELECT * FROM events WHERE get_jsonb_arr_key_values(events_info, 'type')::text LIKE '%NAL%';

注意：用于建索引的自定义函数必须标记为IMMUTABLE，保证相同输入永远返回相同输出，否则数据库不允许基于该函数创建索引。

• 方案3：PostgreSQL 12及以上版本无需自定义函数
  如果你的数据库版本是12或更高，可以直接用内置的jsonpath相关函数提取所有type值，省去自定义函数的步骤：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX event_info_type_idx ON events USING GIN (
    jsonb_path_query_array(events_info, '$[*].type') gin_trgm_ops
);

查询写法示例：

SELECT * FROM events WHERE jsonb_path_query_array(events_info, '$[*].type') @> '"INTERNAL"'::jsonb;

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