Search over a nested / multi-level Postgres JSON type using Active Record

Question

Suppose I have the json object below

:response_body => {
    "status" => "ok",
    "um" => {
        "a" => "a",
        "b" => {
            "c" => "ok"
        }
    }
}

Example search:

LogService.where(response_body: {"status" => "ok"})

Arthur Felipe · Accepted Answer · 2014-09-17 15:46:53Z

18

If you are using the Postgres as database and the column really is a json type, you can use a search in Rails like this:

LogService.where("response_body#>>'{status}' = 'ok'")

This #>> will walk over the json path and make the search. If you use only #> (with just one >), it will return the remaing json.

Another example, if you want to get the "c" => "ok"

LogService.where("response_body#>>'{um, b, c}' = 'ok'")

I think that´s it.

answered May 12, 2014 at 12:46

Arthur Felipe

1,4361 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1