如何将搜索jsonb数组字段的SQL查询转换为Active Record查询？

你的问题核心在于ActiveRecord对PostgreSQL的行生成函数（比如jsonb_array_elements）的连接语法处理，以及参数占位符的正确使用方式。下面是两种可行的解决方案：

方法一：直接使用SQL片段构建查询（简单直接） #

这种方式最贴近你原本的SQL语句，只需修正joins的写法和参数传递方式：

# 假设some_search_term是你的搜索关键词
Color.joins("LATERAL jsonb_array_elements(colored_things) AS colorvalues(colorvalue)")
     .where("colorvalue->>'color' ILIKE ?", "%#{some_search_term}%")
     .distinct

关键修正点： #

1. 显式指定LATERAL连接：PostgreSQL中，jsonb_array_elements这类返回多行的函数需要和主表做LATERAL关联（确保每一行的数组都被正确展开并关联到主表行），虽然某些场景下PostgreSQL会隐式处理，但显式声明更安全，也能避免你遇到的UndefinedTable错误。
2. 正确处理参数占位符：不要把%和?写在一起（'%?%'），这会让ActiveRecord把?当成字符串的一部分，无法正确替换为变量。正确的做法是把搜索词前后拼接%后作为参数传递。

方法二：使用Arel构建查询（更符合ActiveRecord风格） #

如果不想直接写SQL片段，可以用Arel（ActiveRecord的底层查询构建器）来构建整个查询，更安全也更贴合ORM的写法：

# 获取Color模型的Arel表对象
colors_table = Color.arel_table

# 构建jsonb_array_elements的函数调用，并指定别名
color_values = Arel::Nodes::NamedFunction.new(
  'jsonb_array_elements',
  [colors_table[:colored_things]]
).as('colorvalues(colorvalue)')

# 构建查询
Color.joins(color_values)
     .where(
       Arel.sql('colorvalue->>\'color\'').matches("%#{some_search_term}%")
     )
     .distinct

优势： #

• 完全通过Ruby代码构建查询，避免硬写SQL字符串，更易于维护和重构。
• Arel会自动处理SQL注入防护，和ActiveRecord的参数绑定机制保持一致。

为什么你的原代码会报错？ #

1. joins的写法错误：你直接在joins里写jsonb_array_elements(colored_things) AS colorvalues(colorvalue)，ActiveRecord无法正确解析这个片段，需要用字符串包裹并显式声明LATERAL连接。
2. 参数占位符使用错误：where("colorvalue->>'color' ILIKE '%?%'", some_search_term)中，%?%会被当成普通字符串，?不会被替换为你的搜索词，导致SQL语法错误（进而引发表关联的错误提示）。

内容的提问来源于stack exchange，提问作者Josien