PostgreSQL中使用json_field@>实现大于、in等查询条件的方法咨询

结论 #

@>是PostgreSQL针对jsonb类型的包含匹配运算符，本身仅支持精准的键值对包含校验，无法直接实现大于、不等于、IN这类逻辑。但可以通过兼容索引的写法实现相同需求，避免全表扫描，具体方法如下：

前提说明

首先需要确认你的json_field是jsonb类型（普通json类型不支持GIN索引优化），提前建GIN索引才能获得高性能：

-- 建通用GIN索引，适配所有@>相关查询
CREATE INDEX idx_my_table_json_field ON my_table USING GIN (json_field);

各场景实现方案 #

1. 不等于查询（对应name != 'foo'需求）

直接在@>查询前加NOT即可，该写法仍然可以命中GIN索引：

SELECT * from my_table WHERE NOT json_field @> '{"name":"foo"}';

2. IN查询（对应age in (20,50)需求）

把每个匹配值拆分为独立的@>条件，用OR拼接即可，多条件OR仍然可以命中GIN索引：

SELECT * from my_table 
WHERE json_field @> '{"age":20}' 
OR json_field @> '{"age":50}';

3. 大于/小于等范围查询

这类逻辑无法用@>实现，但可以给你高频查询的JSON键单独建B树表达式索引，让范围查询也能走索引，性能和@>查询相当：

-- 以age字段数值查询为例，先建表达式索引
CREATE INDEX idx_my_table_json_age ON my_table USING BTREE ( (json_field->'age')::int );

-- 后续查询直接写范围比对即可走索引
SELECT * from my_table WHERE (json_field->'age')::int > 20;

如果是字符串类型的范围比对，建索引和查询时不用转int，直接用->>提取文本即可。

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