SqlAlchemy Postgres JSON how to filter with question mark operator?

Question

I'm struggling to convert this to an ORM filter query:

select count(*) from issues WHERE pending_notifications ? 'flooby';

pending_notifications is a JSONB field containing a simple JSON array.

I'm not sure how to structure the filter using the question mark operator

I believe a Postgres ARRAY would work like this:

query.filter(pending_notifications.any('flooby'))

But I'm using JSONB and the filter syntax is not the same.

Any suggestions

univerio · Accepted Answer · 2016-08-29 02:07:17Z

4

You can use the .op() method to use any verbatim operator:

query.filter(pending_notifications.op("?")("flooby"))

answered Aug 29, 2016 at 2:07

univerio

20.7k3 gold badges73 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ilja Everilä · Accepted Answer · 2020-07-14 06:06:45Z

3

Given that you're using JSONB as column data type, use the has_key() method:

query.filter(pending_notifications.has_key('flooby'))

which maps to the ? operator. The method name is misleading in this context, but PostgreSQL documentation for jsonb operators describes ? thusly:

Does the string exist as a top-level key within the JSON value?

and so has_key() is somewhat aptly named.

An example:

In [21]: t = Table('t', metadata, Column('json', postgresql.JSONB))

In [28]: print(t.c.json.has_key('test'))
t.json ? :json_1

answered Aug 29, 2016 at 11:53

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

Ryabchenko Alexander · Accepted Answer · 2020-02-27 19:05:44Z

0

I can use raw sql in filter

from sqlalchemy import text

db.session.query(Issue).filter(text("pending_notifications ? 'flooby'")

answered Feb 27, 2020 at 19:05

12.7k9 gold badges74 silver badges105 bronze badges