ruby-on-rails - ActiveRecord 在将 JSONB/Hash 与 Array 一起使用时构造不正确的 SQL

Question

我正在为名为 data 的列使用 jsonb 数据类型。当我使用简单的哈希查询时，它可以正常工作:

[1] pry(PredictionService)> Prediction.where(data: {"innings_no" => 1})

  Prediction Load (1.2ms)  SELECT "predictions".* FROM "predictions" WHERE "data"."innings_no" = 1
=> #<Prediction::ActiveRecord_Relation:0x3fcb34634e78>

当我使用这样的数组时，它因不正确的 SQL 而失败:

[2] pry(PredictionService)> Prediction.where(data: {"innings_no" => [1,2]})

ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "data" does not exist
LINE 5:                WHERE a.attrelid = '"data"'::regclass
                                          ^
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod
                FROM pg_attribute a LEFT JOIN pg_attrdef d
                  ON a.attrelid = d.adrelid AND a.attnum = d.adnum
               WHERE a.attrelid = '"data"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum
from /Users/lenin.rajasekaran/.rvm/gems/ruby-2.3.1@duggout-app/gems/activerecord-4.2.5/lib/active_record/connection_adapters/postgresql_adapter.rb:592:in `async_exec'

这是 jsonb/ActiveRecord 的已知问题还是可以修复？

我无法使用 Postgres 的数组函数来访问特定键，因为键是动态的，我正在使用此查询在创建新记录之前查找现有记录。

Answer 1

因此，您有一个名为 data 的 jsonb 列，其中包含类似

的内容

{ "innings_no": 6, ... }

要在查询中使用该列，您必须使用 PostgreSQL JSON functions and operators .在这种情况下，您可能需要 ->>，它将字段提取为 text 值和类型转换。所以像这样:

Prediction.where("(data ->> 'innings_no')::int = ?", 1)

data ->> 'innings_no' 或多或少等同于 Ruby(或 JavaScript)中的 data['innings_no']，::int 将 ->>> 产生的字符串转换为整数。

当然，在一个小 SQL 片段中使用 = 意味着您负责修改查询以说明数组:

Prediction.where("(data ->> 'innings_no')::int = any(array[?])", [1,2])

或:

Prediction.where("(data ->> 'innings_no')::int in (?)", [1,2])

幸运的是，= any 和 in 版本在任何一种情况下都可以工作，因此您可以这样做而不必担心

innings = 1
Prediction.where("(data ->> 'innings_no')::int = any(array[?])", innings)

innings = [1,2]
Prediction.where("(data ->> 'innings_no')::int = any(array[?])", innings)

---

另一方面，如果您有一个 Ruby 哈希，您想要查找其 data 与该哈希重叠的模型，那么您可以使用 @> 运算符:

@> jsonb
Does the left JSON value contain the right JSON path/value entries at the top level?
'{"a":1, "b":2}'::jsonb @> '{"b":2}'::jsonb

和 to_json 调用以构建右侧。例如:

hash = { :innings_no => 1, :pancakes => 11 }
Prediction.where('data @> ?', hash.to_json)

PostgreSQL 会自动将 ? 字符串转换为 JSONB，但您可以明确地说:

Prediction.where('data @> ?::jsonb', hash.to_json)
# -------------------------^^^^^^^

---

您的第一个查询:

Prediction.where(data: {"innings_no" => 1})

不起作用，因为 ActiveRecord 保留了引用 JOINed 表的参数结构，正如您在 SQL 中看到的那样:

WHERE "data"."innings_no" = 1
table -^^^^
column -------^^^^^^^^^^

所以当它最终出现在数据库中时，PostgreSQL 将寻找一个名为 data 的表，但找不到。当然，这不会失败，直到它到达该数据库，所以它在 pry 中看起来很好。

您的第二个查询:

Prediction.where(data: {"innings_no" => [1,2]})

因同样的原因而失败，但它失败得更早，因为出于某种原因，[1,2] 数组使 ActiveRecord 想知道 data 表的结构当它试图构建查询时； pg_attribute 和 pg_attrdef 系统表上的奇怪查询是 ActiveRecord 用来确定表结构的，所以任何时候你看到这样的查询都是 ActiveRecord 尝试找出表的列结构。