Filter JSON using IN operator in PostgreSQL DB

Question

{
  "List": [
    {
      "f1": "eed31964",
      "f2": "ABC"
    },
    {
      "f1": "964433d1",
      "f2": "DEF"
    }
  ]
}

JSON list contains 2 objects. How to filter where

f1 IN ('eed31964','964433d1') AND f2 IN ('ABC','DEF').

I have tried

column->'List' @> '[{"f1": "eed31964"},{"f1": "964433d1"}]'

but this works like AND condition where as IN operator works like OR operator.

Does this answer your question? Select "WHERE IN" with PostgreSQL and JSONB — Eldar
– Eldar, Commented Nov 22, 2022 at 11:46
No, I am asking about List of objects not the single object. — christopher klien
– christopher klien, Commented Nov 22, 2022 at 12:00

Ajax1234 · Accepted Answer · 2022-11-22 15:43:03Z

0

You can apply successive cross joins to access the list elements, and then apply your filtering conditions:

select jsonb_object_agg(t1.key, t1.l) from (
   select t2.key, jsonb_agg(v.value) l 
   from tbl t cross join jsonb_each(t.js) t2 
   cross join jsonb_array_elements(t2.value) v 
   where v.value ->> 'f1' in ('eed31964','964433d1') 
       and v.value ->> 'f2' in ('ABC','DEF') 
   group by t2.key) t1

See fiddle

edited Nov 22, 2022 at 15:43

answered Nov 22, 2022 at 14:32

Ajax1234

71.7k9 gold badges67 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filter JSON using IN operator in PostgreSQL DB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related