Search through jsonb column in ActiveRecord

Question

My User model have jsonb column which names is raw. It looks like this:

{"client"=>{"tokens"=>["asdasd"]}}

Now I want to find a user by a token which is in raw["client"]["tokens"]. How can I do that?

Answer 1

I usually first craft such queries in a SQL console and then convert it to ActiveRecord.

You can navigate hash keys in Postgres. The query

SELECT
  raw #> '{client,tokens}'
FROM users

will return just the tokens array from that path. Now we will need to check if it contains the value we are looking for. The query

SELECT
  raw #> '{client,tokens}' ? 'asdasd'
FROM users

will select t for those row that have a matching token. Now you can move this to the WHERE section:

SELECT
*
FROM users
WHERE  raw #> '{client,tokens}' ? 'asdasd'

And if this selects what you expect, then you can convert it to AR:

User.where("config #> '{client, tokens}' ? :token", token: 'asdasd')

Note that I can not use ? for parameter substitution and use :token instead. Also note that this only works in JSONB (Postgres 9.4+ https://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE)

Update:

You should(tm) (I have not tested this) get along with:

CREATE INDEX index_tokens ON users USING GIN ((raw #> '{client, tokens}'));

See https://www.postgresql.org/docs/9.6/static/datatype-json.html#JSON-INDEXING for more details

Update2:

Another way to query would be:

raw @> '{"client": {"tokens": ["asdasd"]}}'   

Which should be able to use a simple GIN index on the raw column (which uses more space than the expression index described above).

CREATE INDEX index_user_raw ON users USING GIN (raw)

Again: details see the JSON INDEXING link above. And use a Query Visualizer to see the differences. I like http://tatiyants.com/pev