Posted over 4 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. jsonb columns can be updated, but that needs a different syntax.

Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Owner of this card:

Dominik Schöler
Last edit:
over 2 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