Solved: How to check if a Postgres JSONArray has a string


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
How should I find the items whose item_code = ‘Nivea Men Box (12.0 PC)’ I came up with this:
but the item_code is not getting recognised. Anyhelp is appreciated.

Best Answer:

You can use the contains operator @> to find the rows that contain that item code:
If you also want to extract the matching array element, you can use a JSON path expression:
This assumes that items is a jsonb column (which it really should be). If it’s not you need to cast it: items::jsonb @> ...

If you have better answer, please add a comment about this, thank you!