MySQL JSON列参数化查询：如何让Rails不将参数转为字符串？

方法1：在SQL中转换JSON字段的类型 #

针对不同数据库，通过SQL函数把JSON字段提取出的值转为数字类型，再和参数比较：

PostgreSQL

如果JSON字段中year_built存储的是字符串：

House.where("CAST(data->>'$.year_built' AS integer) > ?", 2000)
# 或者用PostgreSQL特有的类型转换语法
House.where("data->>'$.year_built'::integer > ?", 2000)

如果JSON字段中year_built存储的是数字，直接用->（返回JSON原生类型）代替->>（返回字符串）：

House.where("data->'$.year_built' > ?", 2000)

MySQL

House.where("CAST(JSON_UNQUOTE(JSON_EXTRACT(data, '$.year_built')) AS UNSIGNED) > ?", 2000)

方法2：用Arel构建查询 #

Arel是Rails的底层查询构造器，能自动处理类型映射，避免手动SQL的类型问题：

# 提取JSON字段并转为整数类型
data_year_built = House.arel_table[:data]
                       .json_extract('$.year_built')
                       .cast('integer')

# 构建大于条件的查询
House.where(data_year_built.gt(2000))

方法3：封装模型查询方法 #

把查询逻辑封装到模型中，复用的同时避免重复处理类型问题：

class House < ApplicationRecord
  def self.year_built_greater_than(year)
    where("CAST(data->>'$.year_built' AS integer) > ?", year)
  end
end

# 使用示例
House.year_built_greater_than(2000)

问题原因说明 #

Rails在处理手动编写的SQL字符串参数时，无法自动推断JSON相关表达式的目标类型，因此会默认将参数转为字符串。通过上述方法，要么转换SQL中字段的类型，要么借助Arel让Rails正确识别参数类型，就能解决整数与字符串比较的问题。

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