Rails 5.2操作PostgreSQL JSON字段排序时触发类型转换错误求助

你碰到的这个问题很典型——当data ->> 'position'返回空字符串（""）时，PostgreSQL没法把它转换成整数，所以抛出了ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation错误。下面给你几个实用的解决思路：

• 过滤掉无有效position的记录
  如果那些position为空的记录不需要参与这次遍历和排序，可以先把它们排除在外，避免转换错误：

  MyModel.where("data ->> 'position' != ''")
         .order("(data ->> 'position')::Integer ASC")
         .each { |x| puts x.position }
  

  或者更严谨一点，先确保转换后的值不为NULL：

  MyModel.where("(data ->> 'position')::Integer IS NOT NULL")
         .order("(data ->> 'position')::Integer ASC")
         .each { |x| puts x.position }
  

• 给空值设置默认整数
  如果不想过滤记录，想把空字符串当作某个默认值（比如0，或者一个很大的数让它排在末尾）来排序，可以用PostgreSQL的NULLIF和COALESCE组合处理：

  # 把空字符串转成NULL，再替换成0，这样空值会排在最前面
  MyModel.order("COALESCE((NULLIF(data ->> 'position', ''))::Integer, 0) ASC")
         .each { |x| puts x.position }
  

  要是想让空值的记录排在最后，把0换成一个足够大的整数就行，比如9999。

• 修复数据库中的脏数据
  如果这些空字符串是意外产生的脏数据，最好从根源上解决，直接修正数据库里的记录：
  用Rails控制台批量更新：

  MyModel.where("data ->> 'position' = ''").find_each do |model|
    model.data['position'] = 0 # 或者你需要的默认值
    model.save!
  end
  

  或者直接执行SQL（更快，适合大量数据）：

  -- 如果你用的是jsonb类型
  UPDATE my_models 
  SET data = jsonb_set(data, '{position}', '0'::jsonb) 
  WHERE data ->> 'position' = '';
  
  -- 如果你用的是json类型
  UPDATE my_models 
  SET data = json_set(data, '{position}', '0'::json) 
  WHERE data ->> 'position' = '';
  

这样处理后，你原来的代码就能正常运行啦～

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