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.

Dominik Schöler almost 6 years ago
This website uses short-lived cookies to improve usability.
Accept or learn more