How to query records where jsonb string field is in array-CodePudding

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

CodePudding user response：

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)

CodePudding user response：

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

Try this..it might work