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.

mu is too short · Accepted Answer · 2016-08-19 23:00:49Z

16

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.

answered Aug 19, 2016 at 23:00

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

7 Comments

Taylor Allred Over a year ago

Sweet! I'll test it today and get back to you!!

Taylor Allred Over a year ago

Works great! Thanks! Model.where("model_attributes @> any(array[?]::jsonb[])", [{attr: 1}, {attr: 2}].map(&:to_json))!!

gingerlime Over a year ago

any tips on how to apply this to a nested value? e.g. if my jsonb attr looks like {"inner_key": "inner value"}... It doesn't seem to work with where("attr->>inner_key @> any(array[?]::jsonb[])") ...

mu is too short Over a year ago

@gingerlime The ->> operator gives you text, not jsonb. You'd want to use -> to give you jsonb or a text[] instead of jsonb[].

mu is too short Over a year ago

@gingerlime The best place for a bug to hide is right out in the open where you'd never look for it ;)

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related