PostgreSQL：统计JSONB字段内嵌对象中包含指定键的行数

你之前的查询逻辑是匹配id_1对应的具体值，所以只会返回值完全匹配的行，不符合只判断键是否存在的需求。

PostgreSQL的JSONB类型提供了专门的键存在检查操作符?，可以直接实现你的需求，正确查询语句如下：

SELECT COUNT(*) AS matched_count
FROM "myTable"
WHERE value ? 'props'
  AND value->'props' ? 'id_1';

语法说明 #

• ?操作符的作用是判断左侧的JSONB对象是否包含右侧字符串作为顶层键，只要键存在就返回true，和键对应的数值无关
• 前置value ? 'props'的条件是为了过滤掉没有props字段的行（比如示例中id=2的行），避免空值导致逻辑异常

扩展用法 #

如果需要判断多个键同时存在，可以使用?&操作符配合数组实现：

-- 统计props下同时存在id_1、id_2的行数
SELECT COUNT(*) AS matched_count
FROM "myTable"
WHERE value ? 'props'
  AND value->'props' ?& ARRAY['id_1','id_2'];

如果需要判断多个键中存在任意一个，可以使用?|操作符：

-- 统计props下存在id_1或者id_3的行数
SELECT COUNT(*) AS matched_count
FROM "myTable"
WHERE value ? 'props'
  AND value->'props' ?| ARRAY['id_1','id_3'];

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