active-record postgres check if jsonb array contains x or y

Question

I have a table with a jsonb column that has a nested json array. I would like to find all records where the nested array contains at least one of a value.

For instance, my model Person has a jsonb column named preferences. Inside the jsonb preferences, there are a few keys, one of which the value is an array:

Person.first.preferences = {"diet" => {"foods" => ["apples", "oranges", "bananas"]}}

I would like to create a query that returns all Persons whose preferences -> diet -> foods include 'apples' OR 'mangos' (for example).

Currently, I can get results with 1 input (ie. 'apples') like this:

Person.where('preferences @> ?', {'diet' => {'foods' => ['apples']}}.to_json)

And multiple inputs, if they all exist in the array. So if I pass in 'apples' and 'oranges', the result is returned because the record includes BOTH apples and oranges.

Person.where('preferences @> ?', {'diet' => {'foods' => ['apples', 'oranges']}}.to_json)

Is there a way to pass in multiple variables (ie. ['apples', 'mangos']) and check if the nested jsonb array contains either apples OR mangos but not necessarily both?

The following query has no results, because my Person's food preferences don't include mangos, even though they include apples.

Person.where('preferences @> ?', {'diet' => {'foods' => ['apples', 'mangos']}}.to_json)

I am using Rails 5.2 and Postgresql 10. How do I accomplish this?

mu is too short · Accepted Answer · 2019-09-28 07:06:53Z

9

The first thing to do is to get to the array using -> or #> and then you can use the ?| operator to check if the array overlaps what you're looking for. Something like this SQL:

preferences->'diet'->'foods' ?| array['apples', 'mangos']

or with ActiveRecord:

Person.where("preferences->'diet'->'foods' ?| array[:foods]", foods: ['apples', 'mangos'])

answered Sep 28, 2019 at 7:06

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

2 Comments

johndavid400 Over a year ago

thanks! worked perfectly.. I read through the postgres documentation page but must have misread how that ?| operator was intended to work.

mu is too short Over a year ago

The ?| and ?& operator docs for JSON aren't the best, you sort of have to read them both and their examples together.

Collectives™ on Stack Overflow

active-record postgres check if jsonb array contains x or y

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related