Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them, it only takes a minute:

SQLAlchemy filtering nested JSON data in PostgreSQL JSONB

up vote 2 down vote favorite

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
30.2k15102200

asked Jul 3 at 3:07

Nightsuki
113

I believe SQLAlchemy does not have JSONB specific expressions yet and you might need to write the selection as raw SQL. – Mikko Ohtamaa Jul 3 at 5:35

dict = {"facebook": {"openid": "123456789"}} user = cls.query.filter(User.auth.contains(dict)).first() it takes effect – Nightsuki Jul 12 at 7:20

JSONB operators: docs.sqlalchemy.org/en/latest/dialects/… – dukebody Oct 12 at 12:50

add a comment |

Your Answer

Name

Post as a guest

Name

discard

By posting your answer, you agree to the privacy policy and terms of service.

asked	4 months ago
viewed	263 times