如何在PostgreSQL LIKE查询中正确转义双引号查询YAML内容

问题核心原因 #

你查询无结果的核心原因是audited_changes字段存储的是YAML格式文本，并非JSON格式，你参考的{"hide_on_map"=>[false, true]}是Ruby Hash对象的打印格式，不是实际存储在text字段中的内容：

• 实际YAML存储的键不会带双引号，你加双引号做LIKE匹配自然找不到对应记录
• YAML中数组的存储格式通常是分行带-的格式，不是JSON的方括号inline格式，所以你直接匹配[false, true]也会匹配失败

方案1：模糊匹配（兼容所有PostgreSQL版本，无需额外函数） #

根据实际YAML存储格式调整匹配字符串即可：

Rails侧写法 #

# 适配数组分行存储的标准YAML格式
target_fragment = "hide_on_map:\n- false\n- true"
like_query = ActiveRecord::Base.send(:sanitize_sql_like, target_fragment)
Audited::Audit.where(auditable_type: 'Lot').where('audited_changes LIKE ?', "%#{like_query}%").count

原生PSQL写法 #

SELECT COUNT(*) FROM audits 
WHERE auditable_type = 'Lot'
AND audited_changes LIKE '%hide_on_map:%
- false
- true%';

如果你的YAML是inline压缩格式，直接把匹配字符串改成%hide_on_map: [false, true]%即可。

方案2：精准解析查询（无漏判误判，无需改表结构） #

PostgreSQL 11及以上版本内置yaml_to_json函数，可以直接把text类型的YAML内容转为JSON格式查询，不需要修改字段类型，没有额外复杂度：

原生PSQL写法 #

SELECT COUNT(*) FROM audits
WHERE auditable_type = 'Lot'
AND yaml_to_json(audited_changes) -> 'hide_on_map' ->> 0 = 'false'
AND yaml_to_json(audited_changes) -> 'hide_on_map' ->> 1 = 'true';

Rails侧写法 #

Audited::Audit.where(auditable_type: 'Lot')
  .where("yaml_to_json(audited_changes) -> 'hide_on_map' ->> 0 = 'false'")
  .where("yaml_to_json(audited_changes) -> 'hide_on_map' ->> 1 = 'true'")
  .count

该方案相比模糊匹配性能更高，也不会出现字段名、值内容巧合匹配的误判问题。

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