时间戳

首页 > 解决方案 > 带有 json 数组的 find_by 使用 IN 执行查询而不是搜索整个数组

sql - 带有 json 数组的 find_by 使用 IN 执行查询而不是搜索整个数组

问题描述

我有一个带有 jsonb 字段（坐标）的模型（区域）：

  create_table "areas" do |t|
    t.jsonb "coordinates"
  end

现在如果我插入一个数组：

Area.create(coordinates: [1, 2])

它工作正常，但正在搜索它：

Area.find_by(coordinates: [1, 2])

使用 IN 执行 SQL 查询，例如：

SELECT "areas".* FROM "areas" WHERE "areas"."coordinates" IN ($1, $2) LIMIT $3  [["coordinates", "1"], ["coordinates", "2"], ["LIMIT", 1]]

我尝试搜索[[1, 2]]，[[[1, 2]]]但我总是得到IN查询。

（可能是轨道问题）

标签： sqlruby-on-railspostgresqlruby-on-rails-6

解决方案

查询 JSON/Array/Hstore 类型列时必须使用 SQL 字符串。例如：

Event.where("payload->>'kind' = ?", "user_renamed")

它们不是查询界面的“本机”部分，因为由于键入和不同的实现，创建查询比普通列涉及更多。ActiveRecord 只会为这些类型创建查询，就好像它可以理解的普通列类型一样。请参阅活动记录和 PostgreSQL。

查询 json 列中的数组可以通过以下方式完成：

WHERE coordinates @> ANY (ARRAY [1,2,3]::jsonb[]);

但是 - 也许你应该重新考虑一下是否首先使用 JSON 列是个好主意，因为查询会很糟糕？JSON/JSONB 类型适用于数据不符合模式但不应替代正确的关系数据库建模的某些特殊任务。不必要的 JSONB 是一种反模式。

此外，如果这些是地理坐标，则您将这些数字表示为整数或浮点数，但除了添加 a 之外，没有办法在 JSON 中指定类型，.并且解析器决定2.0是浮点数还是整数。

如果您为坐标设置单独的表，您实际上可以使用 ActiveRecord 关联，并具有一定程度的数据规范化、索引、引用完整性等。哦，我有没有提到你实际上可以像一个理智的人一样查询你的数据？

class Area
  has_many :coordinates
end

# rails g model coordinate area:references lat:decimal lng:decimal
class Cooridinate
  belongs_to :area
end


Area.joins(:coordinates).where(coordinates: { lat: 1, lng: 1 })

您还可以使用PostGIS 扩展，它为坐标、多边形和其他类型的地理资料提供专门的列类型。

推荐阅读