Rails search thru JSON attribute

Question

I have a Foo model. It has keywords.

create_table "foos", force: :cascade do |t|
  t.json "keywords"
end

The keywords are stored as an array: ["quick", "brown", "fox"]

I was wondering how to choose foos with one of the keywords being "fox".

I was watching this video and it said to use something like WHERE details -> 'tags' ? 'techie'

But this only works because details in their example is a hash

{
  "contacts": [...],
  "tags": [...]
}

I'm not digging through a hash, I just want to search through an array. I've tried this:

Foo.where("keywords -> 'fox'")

but it raises

ActiveRecord::StatementInvalid: PG::DatatypeMismatch: ERROR:  argument of WHERE must be type boolean, not type json
LINE 1: SELECT  "foos".* FROM "foos" WHERE (keywords -> 'fox') LIM...
                                              ^
: SELECT  "foos".* FROM "foos" WHERE (keywords -> 'fox') LIMIT $1

A tutorial suggests to use ?| array[:keys] like so:

Foo.where('keywords ?| array[:keys]', keys: ['brown', 'fox'])

But I get the same kind of error

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: json ?| text[]
LINE 1: SELECT  "foos".* FROM "foos" WHERE (keywords ?| array['fox...
                                                       ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT  "foos".* FROM "foos" WHERE (keywords ?| array['fox','quick']) LIMIT $1

Ideas anyone?

Answer 1

You have to use JSONB data type instead of JSON. Here is a good topic.

Compare

> select '["a", "fox", "c"]'::jsonb ?| array['fox','quick'] as exists;

 exists
--------
 t
(1 row)

to

> select '["a", "fox", "c"]'::json ?| array['fox','quick'] as exists;

ERROR:  operator does not exist: json ?| text[]
LINE 1: select '["a", "fox", "c"]'::json ?| array['fox','quick'] as ...

P.S. And then you have an option to index your keywords column.