How to query records where jsonb string field is in array

Question

I want to query ActiveRecord collection and select records, where string value inside jsonb field is included in a given array.

Model:

create_table "dishes", force: :cascade do |t|
    ...
    t.jsonb     "params"
    ...
end

Content of params always has this structure:

{"procart_id"=>"4", "procart_config"=>{}}

I have a given array:

availabilities = ['4', '8', '11']

How can I query Dish models where params.procart_id is in availabilities array?

I tried the following:

Dish.where("params::jsonb ->> 'procart_id' = any (array[?])", availabilities)

But it gave me the error:

ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: 
: SELECT "dishes".* FROM "dishes" WHERE (params::jsonb ->> 'procart_id' = any (array['4', '8', '11']))

Those are both quite old, but it does work in Postgres 9.4. — Schwern
– Schwern, Commented Dec 21, 2022 at 8:19
@Schwern ye, quite old 😅 Strange, the snippet does really work, but doesn't on my machine. Maybe I've missed something, will investigate. Thanks. — Mark Cholak
– Mark Cholak, Commented Dec 21, 2022 at 8:27

Yashwanth N.R · Accepted Answer · 2022-12-23 07:24:13Z

3

Dish.where("params::jsonb ->> 'procart_id' = any (array[?]::jsonb[])", availabilities)

Try this..it might work

answered Dec 23, 2022 at 7:24

Yashwanth N.R

561 bronze badge

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

1 Comment

ethanmclark Over a year ago

Removing the ::jsonb[] after the any from this solution worked for me. Ruby 3.2.0, PGSQL 13.

Sachin Singh · Accepted Answer · 2022-12-21 10:18:58Z

-1

You can modify the query params before querying the Dish model and query on procart_id like below:

availabilities = ['4', '8', '11']
query_params = availabilities.map { |availability| { procart_id: availability } }
Dish.where(params: query_params)

answered Dec 21, 2022 at 10:18

Sachin Singh

1,10810 silver badges17 bronze badges

Collectives™ on Stack Overflow

How to query records where jsonb string field is in array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related