ActiveRecord создает неверный SQL при использовании JSONB / Hash с массивом

Я использую тип данных jsonb для столбца с именем data. Когда я запрашиваю простой хеш, он работает правильно:

[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 для доступа к определенному ключу, поскольку ключи являются динамическими, и я использую этот запрос для поиска существующих записей перед созданием новой.


postgresql ruby-on-rails jsonb activerecord rails-activerecord
person Lenin Raj Rajasekaran    schedule 03.10.2016    source источник
comment
Вы уверены, что первый работает? Конечно, как в случае с Prediction.where(data: {"innings_no" => 1}).to_a, и это дало мне ожидаемые результаты?   -  person mu is too short    schedule 03.10.2016
comment
Да, могу показать снимок экрана в формате GIF   -  person Lenin Raj Rajasekaran    schedule 03.10.2016
comment
Думаю, вы правы, Rails думает, что data - это имя таблицы, а innings_no - столбец в этой таблице. Я думал, что это работает, потому что увидел верный #<Prediction::ActiveRecord_Relation:0x3fcb34634e78> результат   -  person Lenin Raj Rajasekaran    schedule 03.10.2016
comment
Так как же выглядит data? Это массив JSON, объект или что-то еще?   -  person mu is too short    schedule 03.10.2016
comment
Это Hash объект. ActiveRecord принимает Hash в качестве столбца data в методе create. Но не принимает то же самое при использовании в предложении where.   -  person Lenin Raj Rajasekaran    schedule 04.10.2016

Ответы (1)


arrow_upward
2
arrow_downward

Итак, у вас есть jsonb столбец с именем data, который содержит такие вещи, как

{ "innings_no": 6, ... }

Чтобы работать с этим столбцом в запросе, вы должны использовать функции PostgreSQL JSON и операторы. В этом случае вам, вероятно, понадобится ->>, который извлекает поле как значение text и приведение типа. Так что-то вроде:

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

data ->> 'innings_no' более или менее эквивалентен data['innings_no'] в Ruby (или JavaScript, если на то пошло), ::int преобразует строку, которая ->> дает целое число.

Конечно, использование = внутри небольшого фрагмента SQL означает, что вы несете ответственность за изменение запроса для учета массива:

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

or:

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 Hash, вы хотите найти модели, data которых перекрывает этот Hash, тогда вы можете использовать оператор @>:

@> jsonb
Содержит ли левое значение JSON правильные записи пути / значения JSON на верхнем уровне?
'{"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 пытается выяснить структуру столбцов таблицы.

person mu is too short    schedule 04.10.2016
comment
Спасибо за ответ, но есть ли способ сравнить мой хеш с существующим data без поиска отдельных ключей? Думая, что можно извлечь ключи один за другим и создать запрос Postgres JSON для поиска совпадения, я хотел бы провести тупое сравнение =, как мы делаем для других обычных столбцов, таких как varchar. Можем ли мы преобразовать весь data в varchar, а наш Ruby Hash в string и провести = сравнение? - person Lenin Raj Rajasekaran; 05.10.2016
comment
Похоже, вы ищете оператор @> PostgreSQL, как в моем обновлении. - person mu is too short; 05.10.2016