Rails5 ActiveRecord如何不指定键搜索Postgres JSON列所有值

核心逻辑是通过Postgres内置的JSON处理函数，把JSON列存储的对象拆成所有键值对，无需提前指定键名即可遍历所有值做匹配，不需要额外安装扩展，兼容Rails 5的ActiveRecord写法。

原生SQL实现 #

假设你的数据表名为records，存储JSON内容的列名为properties：

• 单关键词模糊匹配（不区分大小写）
  用json_each_text（JSON列用）/jsonb_each_text（JSONB列用）把每行的JSON对象拆分为独立的键值对行，再对所有值做模糊匹配，加DISTINCT避免单条记录多值匹配时返回重复结果：

  SELECT DISTINCT records.* FROM records,
  json_each_text(records.properties) AS kv_pair
  WHERE kv_pair.value ILIKE '%Rio%';
  

  针对给出的示例数据，传入Rio、de、Janeiro、Rj任意一个关键词，都能命中对应行。

• 多关键词同时匹配
  如果需要传入多个关键词、要求记录同时命中所有关键词，用EXISTS子句实现即可，避免重复展开JSON：

  SELECT DISTINCT records.* FROM records
  WHERE
    EXISTS (SELECT 1 FROM json_each_text(records.properties) v WHERE v.value ILIKE '%Rio%')
    AND EXISTS (SELECT 1 FROM json_each_text(records.properties) v WHERE v.value ILIKE '%Rj%');
  

Rails 5 模型层封装 #

把查询逻辑封装为可复用的作用域，避免裸SQL拼接，自动适配JSON/JSONB列类型，同时做SQL注入防护：

# 在对应模型文件中添加
class Record < ApplicationRecord
  scope :keyword_match_json, ->(json_column, search_keyword) {
    unnest_func = type_for_attribute(json_column).type == :jsonb ? 'jsonb_each_text' : 'json_each_text'
    where(
      <<~SQL.squish,
        EXISTS (
          SELECT 1 FROM #{unnest_func}(#{table_name}.#{json_column}) kv
          WHERE kv.value ILIKE :pattern
        )
      SQL
      pattern: "%#{sanitize_sql_like(search_keyword)}%"
    )
  }
end

调用方式非常简单，支持链式调用：

# 单关键词搜索
Record.keyword_match_json(:properties, "Janeiro")

# 多关键词同时匹配，直接链式拼接即可
Record.keyword_match_json(:properties, "Rio")
      .keyword_match_json(:properties, "Rj")

大数据量性能优化 #

如果表数据量超过10万条，ILIKE全表扫描性能会明显下降，可以建GIN全文索引提速：

1. 先创建聚合所有JSON值的全文索引，用simple分词器避免词干提取导致的匹配偏差：

   CREATE INDEX idx_records_properties_all_values ON records
   USING GIN (
     to_tsvector('simple', (
       SELECT string_agg(value, ' ') FROM json_each_text(properties)
     ))
   );
   

2. 替换查询为全文检索语法，性能比模糊匹配高1~2个数量级：

   scope :keyword_match_json_fast, ->(json_column, search_keyword) {
     where(
       <<~SQL.squish,
         to_tsvector('simple', (SELECT string_agg(value, ' ') FROM json_each_text(#{json_column})))
         @@ plainto_tsquery('simple', :keyword)
       SQL
       keyword: search_keyword
     )
   }
   

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