ActiveRecord constructs incorrect SQL when using JSONB/Hash with Array

Question

I am using jsonb datatype for a column named data. When I query using a simple hash, it works correctly:

[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>

It fails with an incorrect SQL when I use an array like this:

[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'

Is this a known issue with jsonb/ActiveRecord or this can be fixed?

I cannot use Postgres's array functions to access a particular key, as the keys are dynamic and I am using this query to find existing records before creating a new one.

Are you sure that first one works? Sure as in "I did Prediction.where(data: {"innings_no" => 1}).to_a and it gave me the expected results" sure? — mu is too short
– mu is too short, Commented Oct 3, 2016 at 5:08
I think you are right, Rails think data is a table name and innings_no is a column in that table. I thought it is working because I saw a valid #<Prediction::ActiveRecord_Relation:0x3fcb34634e78> result — Raj
– Raj, Commented Oct 3, 2016 at 7:06
So what does data look like? Is it a JSON array, object, something else? — mu is too short
– mu is too short, Commented Oct 3, 2016 at 17:55
It is a Hash object. ActiveRecord accepts Hash for the data column while in create method. But does not accept the same when using it in where clause. — Raj
– Raj, Commented Oct 3, 2016 at 23:13

mu is too short · Accepted Answer · 2016-10-04 23:36:40Z

2

So you have a jsonb column called data that contains things like

{ "innings_no": 6, ... }

To work with that column in a query, you have to use the PostgreSQL JSON functions and operators. In this case, you probably want ->>, which extracts a field as a text value, and a type cast. So something like:

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

The data ->> 'innings_no' is more or less equivalent to data['innings_no'] in Ruby (or JavaScript for that matter), the ::int casts the string that ->> yields to an integer.

Of course, using = inside a little SQL snippet means that you're responsible for modifying the query to account for an array:

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

or:

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

Luckily the = any and in versions will work in either case so you can do this and no worry about

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

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

If, on the other hand, you have a Ruby Hash has you want to find models whose data overlaps that Hash then you can use the @> operator:

@> 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

and a to_json call to build the right hand side. For example:

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

PostgreSQL will automatically cast the ? string to JSONB for you but you could be explicit and say:

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

Your first query:

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

doesn't work because ActiveRecord reserves that argument structure for referencing JOINed tables as you can see in the SQL:

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

so when that ends up in the database, PostgreSQL will be looking for a table called data but won't find one. Of course, this won't fail until it hits that database so it looked fine in pry.

Your second query:

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

fails for the same reason but it fails earlier because, for some reason, the [1,2] array makes ActiveRecord want to know the structure of the data table while it is trying to build the query; the odd looking query on the pg_attribute and pg_attrdef system tables is what ActiveRecord uses to figure out the structure of a table so any time you see a query like that it is ActiveRecord trying to figure out the columns structure of a table.

edited Oct 4, 2016 at 23:36

answered Oct 4, 2016 at 18:38

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raj Over a year ago

Thanks for the answer, but is there a way to compare my Hash with the existing data without looking up individual keys? Thought it could be possible to extract the keys one by one and build a Postgres JSON query to find a match, I would like to do a dumb comparison of = like we do for other regular columns like varchar. Can we convert the whole data to varchar and our Ruby Hash to string and do a = comparison?

mu is too short Over a year ago

Sounds like you're looking for PostgreSQL's @> operator as in my update.

Collectives™ on Stack Overflow

ActiveRecord constructs incorrect SQL when using JSONB/Hash with Array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related