How to search for multiple items in Postgres Json-CodePudding

I have a json column called items that stores data as json in Postgres DB, it stores the data as in a single row

[
   {
      "item_code":"UMI MAIZE Box (10.0 PC)",
      "name":"5e0648a9e5",
      "uom":null
   },
   {
      "item_code":"Fadge Chocolate Box (4.0 Box)",
      "name":"d805f9bb9d",
      "uom":null
   }
]

I have query that looks for a single Item code that shows the results

select items->>'item_code' from pricing_rule where items::jsonb @> '[{"item_code": "Fadge Chocolate Box (4.0 Box)"}]';

How can I write a query that looks for all items in item_code alone without passing

[{"item_code": "Fadge Chocolate Box (4.0 Box)"}]'

and pass only the item_codes like

("Fadge Chocolate Box (4.0 Box)", "UMI MAIZE Box (10.0 PC)")

and only use the in command something similar to the query

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Thank you in advance.

CodePudding user response：

If your column type is JSONB (which it should be) first flatten the JSON array using jsonb_array_elements and cross join lateral.

select * 
from the_table 
cross join lateral jsonb_array_elements(jitems) as t(item)
where item ->> 'item_code' in 
      ('Fadge Chocolate Box (4.0 Box)','UMI MAIZE Box (10.0 PC)');

If you need the query to be parameterized then this would be easier

select * from  
the_table 
cross join lateral jsonb_array_elements(jitems) as t(item)
where item ->> 'item_code' = 
      any ('{Fadge Chocolate Box (4.0 Box),UMI MAIZE Box (10.0 PC)}'::text[]);

where the search string becomes a parameter.