如何在PostgreSQL/Ruby on Rails中查询嵌套JSON属性

你遇到的问题很常见——当JSON字段里包含数组时，直接用->>就没法直接定位数组内的元素了。下面给你几种针对PostgreSQL（从你用的->>操作符能看出来是PostgreSQL环境）的解决方案，结合Rails的Model查询来实现：

方法1：展开数组后关联查询（最灵活） #

如果需要匹配数组中任意一个元素的key1或key2值，你可以用jsonb_array_elements（如果你的字段是json类型就换成json_array_elements）把数组展开成独立的行，再做过滤：

# 查找foo1数组中存在key1等于"目标值"的记录
Model.joins("JOIN LATERAL jsonb_array_elements(attribute->'foo1') AS arr ON true")
     .where("arr->>'key1' = ?", "目标值")
     .distinct # 必须加这个，不然一个数组里多个元素匹配会返回重复的原记录

如果要同时匹配key1和key2的组合值，只要修改where条件即可：

Model.joins("JOIN LATERAL jsonb_array_elements(attribute->'foo1') AS arr ON true")
     .where("arr->>'key1' = ? AND arr->>'key2' = ?", "目标key1值", "目标key2值")
     .distinct

方法2：用@>操作符精确匹配数组元素 #

如果你要找的是数组中包含某个完整元素的记录（比如整个{key1: "v1", key2: "v2"}对象存在于foo1数组里），可以用PostgreSQL的包含操作符@>，这种方法还能利用索引提速：

target_element = { key1: "v1", key2: "v2" }
Model.where("attribute @> ?", { foo1: [target_element] }.to_json)

方法3：用JSON路径查询（PostgreSQL 12+） #

如果你的PostgreSQL版本是12及以上，还可以用更直观的JSON路径查询，适合复杂的匹配条件：

# 查找foo1数组中任意元素的key1等于"目标值"的记录
Model.where("jsonb_path_exists(attribute, '$.foo1[*] ? (@.key1 == $val)', jsonb_build_object('val', ?))", "目标值")

这里的$.foo1[*]表示遍历foo1数组的所有元素，? (@.key1 == $val)是过滤条件，jsonb_build_object用来安全传递参数，避免SQL注入风险。

额外提醒 #

• 尽量把你的JSON字段类型改成jsonb而不是json，jsonb支持更多操作符和索引，查询效率会高很多；
• 如果你经常需要查询数组内的属性，建议给attribute字段创建GIN索引：

  # 在迁移文件中添加索引
  add_index :models, :attribute, using: :gin
  

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