Posted over 6 years ago. Visible to the public. Linked content.

How to query PostgreSQL's json fields from Rails

PostgreSQL offers a really handy field type: json. You can store any JSON there, in any structure.

While its flexibility is great, there is no syntactic sugar in Rails yet. Thus, you need to manually query the database.


# Given a Task model with 'details' json column Task.where("details->>'key' = ?", "value") # matches where 'details' contains "key":"value" Task.where("details->>'{a,b}' = ?", "value") # matches where 'details' contains "a":{"b":"value"} Task.where("details->'a'->>'b' = ?", "value") # same as above, but via operator chaining

-> returns JSON, whereas ->> returns text. Note that the key(s) must be passed with single quotes.

Updating records is not supported for json columns Archive . jsonb columns can be updated, but that needs a different syntax.

Does your version of Ruby on Rails still receive security updates?
Rails LTS provides security patches for unsupported versions of Ruby on Rails (2.3, 3.2, 4.2 and 5.2).

Owner of this card:

Dominik Schöler
Last edit:
over 4 years ago
by Dominik Schöler
array, pg, psql, read
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Dominik Schöler to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more