Postgres JSONB字段查询：匹配顶级键或数组值的方法

要实现查找是否存在某个值（无论是顶级键，还是数组元素），可以用以下两种方案：

方案1：使用jsonb_each展开键值对（兼容PostgreSQL 9.4+） #

通过jsonb_each将JSONB对象的键值对拆分成行，再分别检查顶级键和数组元素：

SELECT data 
FROM somethings
WHERE 
  -- 检查是否存在顶级键为"value1"
  data ? 'value1'
  OR
  -- 展开所有键值对，检查是否有数组包含"value1"
  EXISTS (
    SELECT 1
    FROM jsonb_each(data) AS kv(key, val)
    WHERE val ? 'value1'
  );

逻辑说明： #

• data ? 'value1'：直接判断JSONB对象的顶级键中是否存在value1；
• 子查询里jsonb_each(data)把每个键值对拆成key（键名）和val（对应值）两列，val ? 'value1'检查该值（如果是数组）是否包含value1，只要有一个符合条件的键值对，就返回true。

方案2：使用JSON路径查询（PostgreSQL 12+） #

PostgreSQL 12及以上支持JSON路径表达式，写法更简洁：

SELECT data
FROM somethings
WHERE jsonb_path_exists(data, '$ ? (exists(@.*.key() == "value1") || exists($.*[*] == "value1"))');

路径语法说明： #

• exists(@.*.key() == "value1")：检查是否存在顶级键等于value1；
• exists($.*[*] == "value1")：遍历所有顶级键对应的数组元素，检查是否有元素等于value1；
• ||表示逻辑“或”，满足任意一个条件即返回匹配记录。

为什么你之前的写法无效？ #

data ? ".*" -> "value1"不符合PostgreSQL JSONB操作符的语法规则：

• ?操作符只能接收单个字符串参数（键或数组元素），不支持通配符；
• ->是获取指定键值的操作符，不能和?按这种方式组合使用。

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