How to check if a Postgres JSONArray has a string

Question

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.

This would be so much easier with a properly normalized data model. — user330315
– user330315, Commented Aug 26, 2022 at 6:33

user330315 · Accepted Answer · 2022-08-26 06:33:05Z

1

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 @> ...

answered Aug 26, 2022 at 6:33

user330315

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

1 Comment

justrying Over a year ago

Hi @a_horse_with_no_name what should be here jsonb_path_query_first am a bit lost

Collectives™ on Stack Overflow

How to check if a Postgres JSONArray has a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related