I am using a JSONB column where the data is stored as { key => [value, value, value ] } How do I write a scope that returns records which contain a particular value in the array of a particular key?
I've figured out how to search simple JSON Hashes;
scope :rice_flour, -> { where("ingredients ->> 'flour' = ?", "rice") }
...but this query type still escapes me. Everything I have looked up lays things out in raw SQL commands, and I am looking for how to write tidy Rails Scopes.
Use the @> operator:
postgres@/> select '["a", "b"]'::jsonb @> '["a"]';
+------------+
| ?column? |
|------------|
| True |
+------------+
postgres@/> select '["a", "b"]'::jsonb @> '["c"]';
+------------+
| ?column? |
|------------|
| False |
+------------+
https://www.postgresql.org/docs/10/functions-json.html
Your scope will be like:
scope :rice_flour, -> {
.where("ingredients -> 'flour' @> '[\"rice\"]'::jsonb")
}
This will generate a SQL like:
WHERE (ingredients -> 'flour' @> '["rice"]'::jsonb)
Assuming flour is the key and rice is one of the values in the array.
