Postgresql jsonb (Rails) - query multiple values, via an array, in a single json attribute

Question

In Rails I can do .where(:attr => [val1, val2, val3]) and I'll get back all the rows matching any of those val1-3.

I'm using Postgres/Postgresql and have a jsonb type and I'd like to do a similar thing. PsuedoCode: .where("col @> ?", {attr: [val1, val2, val3]}.to_json), but this returns nothing - bc it's trying to find a value of the entire array [val1, val2, val3 ] not each of the individual values: val1, val2, val3?

Is there any way to pass in multiple values, relative to as single attribute, in a jsonb query?

I could do .where("attr @> {.. val1 ...} OR attr @> {... val2 ..} ..."), but it seems like there would be a better way.

I've tried various things from https://www.postgresql.org/docs/9.4/static/functions-json.html, but seem to have a solution evading me.

Answer 1

You can often generalize OR expressions using ANY:

9.23.3. ANY/SOME (array)

expression operator ANY (array expression)
expression operator SOME (array expression)

So something like this:

where c = 1 or c = 2 or c = 3

can be written as:

where c = any(array[1,2,3])

The operator doesn't have to be = or course, it can be >, like, or even @>. Also, if the value of a placeholder is an array then ActiveRecord will expand that array as a comma-separated list in the SQL so things like:

where('c = any(array[?])', [1,2,3])

will become:

where c = any(array[1,2,3])

by the time the database sees it.

Combining the above with your JSON gives you something like:

where('attr @> any(array[?]::jsonb[])', [val1, val2, val3].map(&:to_json))

The ::jsonb[] is a type cast to make sure PostgreSQL sees the array as an array of jsonb rather than an array of text.