0

My Postgres version: 10.9

Using JsonB Column type which has a below structure:

columnName: favorites
{
"favouriteCity": "Boston",
"favoriteColors": ["Blue", "Red", "Green"],
}

Now, I want to filter all records which have favorite colors as Blue assigned.

I tried the below queries, but it didn't work.

select * 
from r_fav 
where favorites->>'favoriteColors' in ('Blue', 'Red')

or

jsonb_array_elements_text(favorites->>'favoriteColors' @>'Blue', 'Red')

Any idea how can I filter this?

0

You can use the contains all ?& operator with arrays:

select * 
from r_fav 
where favorites -> 'favoriteColors' ?| array['Blue', 'Red']

That will return rows at least one of the colors exist in the array. If want those where all colors are contained, use the operator ?& instead

| improve this answer | |
  • This worked perfectly. Thank you! Saved a lot of time. – Sravan Nov 26 '19 at 3:12
  • @Sravan If that answer solved your question then please accept it, so that your question is marked as resolved. – a_horse_with_no_name Nov 26 '19 at 6:44
  • Sorry, I don't have enough reputation I guess. When I click upward arrow, I get this message: "Thanks for the feedback! Votes cast by those with less than 15 reputation are recorded, but do not change the publicly displayed post score" – Sravan Dec 9 '19 at 21:16
  • You don't need to upvote, just click on the checkmark to accept the answer i.stack.imgur.com/LkiIZ.png – a_horse_with_no_name Dec 9 '19 at 21:21
0

Use an array of values, bear in mind both values must be included in favoriteColors

select
    favourites->'favouriteCity'
from
    t
where
    favourites->'favoriteColors' @> '["Blue","Red"]'::jsonb;
| ?column? |
| :------- |
| "Boston" |

Let me add this comment of jjanes:

You could also put the key name on the other side of the @> operator. select favourites->'favouriteCity' from t where favourites @> '{"favoriteColors":["Blue","Red"]}'::jsonb; The two ways of formulating it will benefit from different indexes, so it may be worthwhile to try both

db<>fiddle here

| improve this answer | |
  • You could also put the key name on the other side of the @> operator. select favourites->'favouriteCity' from t where favourites @> '{"favoriteColors":["Blue","Red"]}'::jsonb; The two ways of formulating it will benefit from different indexes, so it may be worthwhile to try both. – jjanes Nov 24 '19 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.