CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
AkwMak
AkwMak

Reputation: 167

sqlalchemy JSON query rows without a specific key (Key existence)

When using sqlalchemy with postgresql, I have the following table and data:

 id |   data   
----+----------
  1 | {}
  2 | {"a": 1}
(2 rows)

How do I find row(s) that does not have a key. e.g. "a" or data["a"]?

Give me all objects that does not have the key a.
 id |   data   
----+----------
  1 | {}
(1 row)

self.session.query(Json_test).filter(???)

Upvotes: 7

Views: 6647

Answers (1)

klin
klin

Reputation: 121919

If the column type is jsonb you can use has_key:

session.query(Json_test).filter(sqlalchemy.not_(Json_test.data.has_key('a')))

For both json and jsonb types this should work:

session.query(Json_test).filter(Json_test.data.op('->')('a')==None)

Upvotes: 11

Related Questions