SQLAlchemy filtering nested JSON data in PostgreSQL JSONB

Question

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()

Answer 1

use this approach to filter nested jsonb columns:

user = session.query(User) \
    .filter(text("CAST(auth->'facebook'->>'openid' AS TEXT) = :openid")) \
    .params(openid=openid) \
    .first()