1

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?

2 Answers 2

1

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

4
  • This worked perfectly. Thank you! Saved a lot of time.
    – Sravan
    Nov 26, 2019 at 3:12
  • @Sravan If that answer solved your question then please accept it, so that your question is marked as resolved.
    – user1822
    Nov 26, 2019 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, 2019 at 21:16
  • You don't need to upvote, just click on the checkmark to accept the answer i.stack.imgur.com/LkiIZ.png
    – user1822
    Dec 9, 2019 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

1
  • 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, 2019 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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