PostgreSQL中如何按多属性过滤JSON数组内的对象元素

原生@> JSON包含运算符仅支持等值匹配，无法实现数值大小比较逻辑。要筛选name="class"且value>2的记录，同时兼容value字段的字符串、整型、浮点型、布尔型多类型场景，可通过数组拆分校验的方式实现，不会触发非数值类型的转换报错。

可直接运行的实现代码 #

with data (id, extra_info) as (
values 
  (1, '[{"name": "class", "value": 3}, {"name": "dept", "value": "No. 2"}, {"name": "batch", "value": 20070102}]'::jsonb), 
  (2, '[{"name": "class", "value": 2}, {"name": "dept", "value": "No. 3"}, {"name": "batch", "value": 20081123}]'::jsonb), 
  (3, '[{"name": "class", "value": 3}, {"name": "dept", "value": "No. 1"}]'::jsonb),
  (4, '[{"name": "class", "value": 2.8}, {"name": "dept", "value": "No. 4"}]'::jsonb),
  (5, '[{"name": "class", "value": "3"}, {"name": "dept", "value": "No. 5"}]'::jsonb),
  (6, '[{"name": "class", "value": true}, {"name": "dept", "value": "No. 6"}]'::jsonb)
)
select distinct d.*
from data d
cross join jsonb_array_elements(d.extra_info) as elem
where 
  elem ->> 'name' = 'class'
  and jsonb_typeof(elem -> 'value') in ('number', 'integer')
  and (elem ->> 'value')::numeric > 2;

上述代码执行后会返回id为1、3、4的三条记录，符合class值大于2的预期，value为字符串"3"、布尔值true的记录会被正常过滤。

核心逻辑说明 #

• 通过jsonb_array_elements表函数将每行存储的JSON数组拆分为独立的JSON对象元素，和原表数据做交叉关联，即可逐元素校验属性
• 用->>操作符取元素的name字段文本值，匹配class对应的目标属性项
• 用jsonb_typeof提前判断value字段的JSON类型，仅整型、浮点型（对应类型值为integer、number）参与后续数值比较，字符串、布尔类型直接跳过，避免类型转换报错
• 校验通过的value值转为numeric类型做大小比较，可同时兼容整数、浮点数的比较精度要求
• 加distinct去重是为了避免单条记录的JSON数组中存在多个匹配元素时，原表记录被重复返回

叠加原有等值过滤的写法 #

如果需要同时保留原有的多属性等值匹配逻辑，直接在where子句中追加原有@>包含判断即可，示例如下：

select distinct d.*
from data d
cross join jsonb_array_elements(d.extra_info) as elem
where 
  elem ->> 'name' = 'class'
  and jsonb_typeof(elem -> 'value') in ('number', 'integer')
  and (elem ->> 'value')::numeric > 2
  -- 原有等值匹配条件可直接追加
  and extra_info @> '[{"name": "batch", "value": 20070102}]'::jsonb;

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