ruby on rails - 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"})

Answer 1

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.