CODEWITHSUNDEEP
pythonsqlalchemy
Alex
Alex

Reputation: 1345

SQLAlchemy ? operator on JSONB key

I have a simple table with a JSONB column. In that field I have a key: key and the value of the key can be either a list with ["val1", "val2"] or single string "val2".

I got a raw SQL query that retrieves when a value in key is "val2":

SELECT t.c FROM t WHERE t.c->'key' ? "val2"

However, I am struggling to replicate this in SQLAlchemy query:

I have a session

query = session.query(t).filter(t.c.has_key('val2'))

which results in

SELECT t.c FROM t WHERE t.c ? 'val2' but I want ? operator on ->.

The only way to get -> (as far as I could find) is by using filter(t.c['key2']). However, then I can't perform has_key() I get:

AttributeError: Neither 'JSONElement' object nor 'Comparator' object has an attribute 'has_key'

Any thoughts?

Upvotes: 5

Views: 6884

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52949

Older versions of SQLAlchemy's JSONB columns produce JSONElement objects on item access that seem to lack certain json comparator methods, such as has_key(). The JSONB type was introduced in version 1.0 and backported to 0.9.7, but I think this was fixed in 1.1. In the mean time if you are unable to upgrade your SQLAlchemy, you can work around this using the generic op() operator function:

query = session.query(t).\
    filter(t.c['key2'].op('?')('val2'))

and when you've finally managed to upgrade you can just

filter(t.c['key2'].has_key('val2'))

as expected.

Upvotes: 6

Related Questions