How to query JSONB field with IN operator in rails - active record

user_4620141 0
Viewed 912

I have a jsonb column called lms_data with a hash data-structure inside. I am trying to find elements that match an array of ids. This query works and returns the correct result :

CoursesProgram
.joins(:course)
.where(program_id: 12)
.where(
"courses.lms_data->>'_id' IN ('604d26cadb238f542f2fa', '604541eb0ff9d7b28828c')")

SQL LOG :

  CoursesProgram Load (0.5ms)  SELECT "courses_programs".* FROM "courses_programs" INNER JOIN "courses" ON "courses"."id" = "courses_programs"."course_id" WHERE "courses_programs"."program_id" = $1 AND (courses.lms_data->>'_id' IN ('604d26cadb61e238f542f2fa', '604541eb0ff9d8387b28828c'))  [["program_id", 12]

However when I try to pass a variable as the array of ids :

CoursesProgram
.joins(:course)
.where(program_id: 12)
.where(
  "courses.lms_data->'_id' IN (?)",
["604d26cadb61e238f542f2fa", "604541eb0ff9d8387b28828c"])

I dont get any results and I get two queries performed in the logs...

 CoursesProgram Load (16.6ms)  SELECT "courses_programs".* FROM "courses_programs" INNER JOIN "courses" ON "courses"."id" = "courses_programs"."course_id" WHERE "courses_programs"."program_id" = $1 AND (courses.lms_data->'_id' IN ('604d26cadb61e238f542f2fa','604541eb0ff9d8387b28828c'))  [["program_id", 12]]
  CoursesProgram Load (0.8ms)  SELECT "courses_programs".* FROM "courses_programs" INNER JOIN "courses" ON "courses"."id" = "courses_programs"."course_id" WHERE "courses_programs"."program_id" = $1 AND (courses.lms_data->'_id' IN ('604d26cadb61e238f542f2fa','604541eb0ff9d8387b28828c')) LIMIT $2  [["program_id", 12], ["LIMIT", 11]]

I cannot wrapp my head around this one. The queries perform in both cases seem to be the same. Why is one working and the other one not ? and why in the second case is the query performed twice ?

jsonb postgresql activerecord ruby-on-rails
1 Answers

The question mark is its own operator in postgres's json query function set (meaning, does this exist). ActiveRecord is attempting to do what it thinks you want, but there are limitations with expectation.

Solution. Don't use it. Since the ? can cause problems with postgres's json query, I use named substitution instead.

from the postgres documentation:

?|  text[]  Do any of these array strings exist as top-level keys?  '{"a":1, "b":2, "c":3}'::jsonb ?| array['b', 'c']

So first we use the ?| postgres json operator to look for an ANY in the values of lms_data.

And secondly we tell postgres we'll be using an an array with the postgres array function array[:named_substitution]

ANd lastly after the , at the end of the postgres query, add your named sub variable (in this case I used :ids) and your array.

CoursesProgram
  .joins(:course)
  .where(program_id: 12)
  .where(
    "courses.lms_data->>'_id' ?| array[:ids]", 
    ids: ['604d26cadb238f542f2fa', '604541eb0ff9d7b28828c'])
user_17535960
Related