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?
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'])