ruby-on-rails - 使用 Postgres 查询哈希数组的数组

Question

我有一个模型，其中有一列看起来像这样(示例):

column = [{:A => "1", :B => "2"}, [a, b, c, ..., n]]

其中 a、b、c 和 n 也是哈希，像这样(示例):

a = {:X => "x", :Y => "y"}

目前，如果我调用模型的记录 m，例如，m.column[0][:A] 它返回 => "1"
如果我调用 m.column[1][0]，我会得到 a = {:X => "x", :Y => "y"}

到目前为止，还不错。

现在的问题是，我怎样才能得到模型的所有记录的数组，例如，column[0][:A] = "1" ???

我正在尝试这样的事情(但它不起作用):

Model.where("column[0][:A] = ?", "1")

错误说:

PG::SyntaxError: ERROR:  syntax error at or near ":"

编辑:

column 是一个text 数据类型
在它的模型中它有 serialize :column, Array

Answer 1

您的问题是您正在使用serialize 来存储您的数据，而serialize 只是将一大堆不可查询的YAML 塞入数据库。在 PostgreSQL 中解析 YAML 当然是可能的，但它会很慢而且有点毫无意义。

如果您确实需要存储哈希数组，那么 PostgreSQL 的 jsonb 类型将是您的最佳选择。您不会在数据库中获得符号键，但会获得字符串键，因此此 Ruby 数据:

[{:A => "1", :B => "2"}, {:C => 6, :D => 11}]

看起来像这样的 JSON:

[ { "A": "1", "B": "2" }, { "C": 6, "D": 11 } ]

在数据库中。

一旦数据库中有了 jsonb，就可以使用所有 JSON functions and operators 查询它PostgreSQL 提供者，你甚至可以索引 JSON 以支持更快的查询。在您的情况下，您的查询将如下所示:

Model.where("column->0->>'A' = ?", '1')

在 RHS 上带有整数的 -> 运算符的作用类似于 Ruby 的 Array#[] 方法:

-> int
Get JSON array element (indexed from zero, negative integers count from the end)

对于 RHS 上的字符串，它的作用类似于 Ruby 的 Hash#[]。请注意，-> 返回 JSON，而不是文本或整数。 ->> 运算符与 -> 相同，但它返回文本，因此您可以在最后使用它来使比较更清晰。

或者你可以说:

Model.where("column->0->'A' = ?", '1'.to_json)

将字符串与 JSON 逻辑插入 Ruby。

---

一旦您的数据库模式有意义(即它使用 PostgreSQL 的 jsonb 而不是 Rails 的 serialize)，一切都非常简单，但是您如何从这里到达那里呢？首先从您的模型中删除 serialize :column, Array，然后您需要像这样的三步迁移:

1. 添加一个jsonb列:

   def change
     add_column :models, :column_j, :jsonb
   end
   

2. 从数据库中读取每个列值，手工解压YAML，手工写入JSON:

   def up
     connection.execute('select id, column from models').each do |row|
       a = YAML.load(row['column'])
       connection.raw_connection.exec(
         'update models set column_j = $1 where id = $2',
         [ a.to_json, row['id'].to_i ]
       )
     end
   end
   

   请注意，您不能为此使用模型，因为模型类和数据库不再同意 models 表的结构和格式。

3. 用新的 column_j 替换旧的 column:

   def change
     remove_column :models, :column, :text
     rename_column :models, :column_j, :column
   end
   

当然，您需要在迁移之前备份您的数据库。希望您永远不会再考虑使用 serialize，serialize 是一个可怕的拼凑，看起来是一个简单的解决方案，但很快就会变成一个瞄准您脚下的角色扮演游戏。