How to check if a Postgres JSONArray has a string-CodePudding

I have a Postgres table called pricing_rule that has a column called items of type json that saves records as a JSON Array example

[{"item_code":"Nivea Men Box (12.0 PC)","name":"5e0648a9e5","uom":null},{"item_code":"Dove Men Box (6.0 Box)","name":"d805f9bb9d","uom":null}]

How should I find the items whose item_code = 'Nivea Men Box (12.0 PC)' I came up with this:

select items->>'item_code' from pricing_rule where item_code->'Nivea Men Box (12.0 PC)';

but the item_code is not getting recognised. Anyhelp is appreciated.

CodePudding user response：

You can use the contains operator @> to find the rows that contain that item code:

where items @> '[{"item_code": "Nivea Men Box (12.0 PC)"}]

If you also want to extract the matching array element, you can use a JSON path expression:

select jsonb_path_query_first(items, '$[*] ? (@.item_code == "Nivea Men Box (12.0 PC)")')
from pricing_rule
where items @> '[{"item_code": "Nivea Men Box (12.0 PC)"}]

This assumes that items is a jsonb column (which it really should be). If it's not you need to cast it: items::jsonb @> ...