PostgreSQL实现jsonb列存在指定键'b'的SELECT查询方法

场景说明 #

现有PostgreSQL数据表包含jsonb类型列jsonb_data，列内存储结构为包裹JSON对象的数组，示例数据如下：

需要替代传统LIKE模糊匹配，用jsonb原生语法筛选出数组中任意对象存在顶级键b的行，预期返回3条含b键的记录，排除仅含c键的行。

推荐实现方案 #

方案1：PostgreSQL 12+ 原生JSONPath写法（性能最优，无漏判） #

直接使用jsonb_path_exists函数通过JSON路径判断数组元素是否存在目标键，不受键对应值的类型影响（无论b对应值是null、数字、字符串、对象、布尔值，只要键存在就会命中），写法如下：

SELECT jsonb_data
FROM 你的实际表名
WHERE jsonb_path_exists(jsonb_data, '$[*] ? (exists(@.b))');

路径逻辑说明：

• $[*]：遍历jsonb_data数组下的所有元素
• exists(@.b)：判断当前遍历到的元素是否存在顶级键b
• 只要数组中有任意一个元素满足条件，该行就会被返回，完全匹配预期查询结果。

方案2：低版本兼容写法（支持PostgreSQL 12以下版本） #

如果使用的PostgreSQL版本低于12、不支持JSONPath语法，可以通过数组拆解+键存在运算符实现：

SELECT DISTINCT t.jsonb_data
FROM 你的实际表名 t, jsonb_array_elements(t.jsonb_data) AS elem
WHERE elem ? 'b';

逻辑说明：

• jsonb_array_elements会将每个jsonb数组拆解为多行独立的元素值
• ?是jsonb类型原生的键存在运算符，判断拆解后的对象元素是否包含键b
• DISTINCT用于避免单条记录数组内多个元素同时含b键时返回重复行

性能优化建议 #

以上两种原生jsonb写法的执行效率远高于LIKE模糊匹配，不会出现值内容误判的问题。如果该查询频率较高，可以给jsonb_data字段创建GIN索引进一步提速：

CREATE INDEX idx_tbl_jsonb_data ON 你的实际表名 USING GIN (jsonb_data);

注意：不要使用LIKE '%"b":%'这类模糊匹配写法，一方面无法利用jsonb索引、全表扫描性能极差，另一方面如果JSON值中碰巧包含"b":字符串会出现误匹配，准确率无法保证。

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