9

I'm storing Nesting JSON down as jsonb, but I have no idea how to select nested json with an uncertain value.

e.g.:

{
    "facebook": {
        "openid": "123456789",
        "access_token": "6EFD26B0A868E3BB387E78851E42943F"
    }
}

I know the value of openid but access_token is uncertain.

I tried the following but it raises an error.

cls.query.filter(User.auth["facebook"]["openid"].astext == openid).first()
Mikko Ohtamaa
  • 82,057
  • 50
  • 264
  • 435
Nightsuki
  • 91
  • 1
  • 6
  • 8
    `dict = {"facebook": {"openid": "123456789"}}` `user = cls.query.filter(User.auth.contains(dict)).first()` it takes effect – Nightsuki Jul 12 '15 at 07:20
  • JSONB operators: http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#sqlalchemy.dialects.postgresql.JSONB – dukebody Oct 12 '15 at 12:50
  • 1
    Related: https://stackoverflow.com/questions/28216125/sqlalchemy-filter-according-to-nested-keys-in-jsonb. To access nested objects you can use `User.auth["facebook", "openid"].astext == "123456789"` also. – Ilja Everilä Dec 02 '19 at 09:23
  • 1
    contains operator does not support 'like' filtering, it's not case insensitive and won't search for partial string :( – michalc Oct 07 '21 at 16:00

0 Answers0