How can I filter object elements by multi-attributes in JSON Array in PostgreSQL?-CodePudding

I want to filter JSON objects in array by its multi-attributes. Below is example data and basic filters:

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) 
)
select *
from data
where extra_info @> '[{"name": "class", "value": 3}]'::Jsonb and 
      extra_info @> '[{"name": "batch", "value": 20070102}]'::Jsonb

All objects in JSON array has same structure. But now I want to get JSON objects with "name"="class" and "value" > 2, how can I do it? The "value" attribute may be string, int, float and boolean. Thanks!

CodePudding user response：

You can use jsonb_array_elements in a subquery:

select d.* from data d where exists (select 1 from jsonb_array_elements(d.extra_info) v 
   where (v.value -> 'name')::text = '"class"' and (v.value -> 'value')::text::int > 2)

See fiddle.

CodePudding user response：

You can use a JSON path expression:

and extra_info @? '$[*] ? (@.name == "class" && @.value > 2)'

Note that this won't work if value isn't a number (as shown in the sample data) inside the JSON