Search code examples
sqlruby-on-railsrubypostgresql

Search through jsonb column in ActiveRecord

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?

Solution

  • 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