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.
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.
