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