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.

Demo

Copy
# 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:

Avatar
Dominik Schöler
Last edit:
over 2 years ago
by Dominik Schöler
Keywords:
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