如何在rails -活动记录中使用IN操作符查询JSONB字段

Question

我有一个名为lms_data的jsonb列，其中包含哈希数据结构。我试图找到与id数组匹配的元素。这个查询可以工作并返回正确的结果:

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]

然而，当我试图传递一个变量作为id数组时:

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

我没有得到任何结果，我得到两个查询在日志中执行…

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

我无法理解这个问题。在这两种情况下执行的查询似乎是相同的。为什么一个工作而另一个不行?为什么在第二种情况下执行两次查询?

Answer 1

问号在postgres的json查询函数集中是它自己的操作符(意思是，这个是否存在)。ActiveRecord正在尝试做它认为你想做的事情，但是期望有限制。

解决方案。不要用它。自从?可能会导致postgres的json查询出现问题，我使用命名替代。

来自postgres文档:

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

因此，首先我们使用?|postgres json操作符在lms_data的值中查找ANY。

然后我们告诉postgres我们将使用一个带有postgres数组函数array[:named_substitution]的an数组

最后，在postgres查询结束时的,之后，添加您的命名子变量(在本例中我使用:ids)和您的数组。

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