PostgreSQL 14中如何查询JSONB随机外层键的指定条件数据？

当JSONB数据的外层键是随机生成时，无法直接通过通配符匹配，核心思路是用jsonb_each()函数将JSONB对象拆分为键-值对的行记录，然后针对每个内层对象做条件筛选。假设你的表名为your_table，存储JSONB的字段名为data，以下是针对三个需求的具体实现：

1. 找出包含broke键的外层键 #

通过jsonb_each()展开JSONB，用?操作符检查内层对象是否包含指定键：

SELECT DISTINCT j.key AS outer_key
FROM your_table,
     jsonb_each(your_table.data) j
WHERE j.value ? 'broke';

• jsonb_each(data)：将data字段的JSONB对象拆分为多行，每行包含key（外层随机键）和value（内层对象）
• ?：JSONB操作符，判断内层对象是否存在指定键
• DISTINCT：避免同一外层键因表中多行数据重复返回

2. 找出year值大于1988的外层键 #

需要兼容year为单个数值或数组的情况，先判断类型再做比较：

SELECT DISTINCT j.key AS outer_key
FROM your_table,
     jsonb_each(your_table.data) j
WHERE 
  -- 处理year为单个数值的场景
  (jsonb_typeof(j.value -> 'year') = 'number' AND (j.value ->> 'year')::numeric > 1988)
  OR
  -- 处理year为数组的场景（示例取数组第一个元素判断，可按需调整）
  (jsonb_typeof(j.value -> 'year') = 'array' AND (j.value -> 'year' ->> 0)::numeric > 1988);

• jsonb_typeof()：判断JSONB元素的类型（number/array等）
• ->>：将JSONB元素转为文本，再通过::numeric转为数值类型做比较

3. 找出包含engine且数组第二个元素大于50的外层键 #

先验证engine存在且为数组，再取数组第二个元素（索引从0开始）做比较：

SELECT DISTINCT j.key AS outer_key
FROM your_table,
     jsonb_each(your_table.data) j
WHERE 
  j.value ? 'engine'
  AND jsonb_typeof(j.value -> 'engine') = 'array'
  AND (j.value -> 'engine' ->> 1)::numeric > 50;

• j.value -> 'engine' ->> 1：先定位到engine数组，再取索引为1的元素（第二个元素）转为文本
• 多条件组合确保同时满足「存在engine键」「是数组类型」「第二个元素大于50」

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