CODEWITHSUNDEEP
ruby-on-railsjsonpostgresqlactiverecord
user8494
user8494

Reputation: 101

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

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"})

Upvotes: 9

Views: 3535

Answers (1)

Arthur Felipe
Arthur Felipe

Reputation: 1436

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.

Upvotes: 18

Related Questions