PostgreSQL中查询含指定键值对的JSONB列行的方法

根据你的表结构（主键id（bigint）、JSONB列data），分两种场景给出查询语句：

场景1：目标键值对在JSONB顶层 #

使用PostgreSQL JSONB的@>包含操作符，这是最高效的方式（如果给data列创建GIN索引，性能会更好）：

SELECT id, data
FROM your_table
WHERE data @> '{"目标键": "目标值"}'::jsonb;

比如要找data里有"name": "Alice"的行，语句就是：

SELECT id, data
FROM your_table
WHERE data @> '{"name": "Alice"}'::jsonb;

场景2：目标键值对可能在数组元素或嵌套结构中 #

如果JSONB里包含数组，或者键值对在深层嵌套结构里，使用jsonb_path_exists函数递归遍历所有节点：

SELECT id, data
FROM your_table
WHERE jsonb_path_exists(data, '$.** ? (@."目标键" == "目标值")');

比如要找任意层级（包括数组元素）中存在"status": "active"的行，语句就是：

SELECT id, data
FROM your_table
WHERE jsonb_path_exists(data, '$.** ? (@."status" == "active")');

补充说明 #

• @>操作符要求匹配的键值对是顶层的完整键值，不能匹配部分嵌套结构；
• jsonb_path_exists支持更复杂的JSON路径查询，灵活性更高，但性能略低于@>（如果有索引的话）；
• 若频繁查询JSONB列，建议给data列创建GIN索引：CREATE INDEX idx_your_table_data ON your_table USING GIN (data);

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