Posted about 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.

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

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