Read more

How to query PostgreSQL's json fields from Rails

Dominik Schöler
January 11, 2016Software engineer at makandra GmbH

PostgreSQL offers a really handy field type: json. You can store any JSON there, in any structure.

Illustration money motivation

Opscomplete powered by makandra brand

Save money by migrating from AWS to our fully managed hosting in Germany.

  • Trusted by over 100 customers
  • Ready to use with Ruby, Node.js, PHP
  • Proactive management by operations experts
Read more Show archive.org snapshot

While its flexibility is great, there is no syntactic sugar in Rails yet. Thus, you need to manually query the database.

Demo

# 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 Show archive.org snapshot . jsonb columns can be updated, but that needs a different syntax.

Posted by Dominik Schöler to makandra dev (2016-01-11 15:25)