Question 90447 in PostgreSQL Question posted in
The official documentation can be found here.

How to query records where jsonb string field is in array – Postgresql

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

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

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

    Try this..it might work

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search