Postgres filter JSON column by zero in SQLAlchemy

Question

So, I have a model like this:

class myModel(Base):

    id = Column(Integer, primary_key=True)
    border = Column(JSONB)

How can I query strings that don't have a border? I tried:

filter(myModel.border != None) #nope
filter(myModel.border != 'null') #nope
from sqlalchemy import null
filter(myModel.border != null()) #nope

The value is apparently stored postgresas JSON encoded null value . It definitely gets serialized back to python Nonewhen creating the instance, but I don't know how to request it. It looks like you can set none_as_nullin a column, i.e.:

Column(JSONB(none_as_null=True))

Which replaces JSON-encoded nullwith null, but this seems weird for all columns. What am I missing here?

edit: it should be mentioned that it is v0.9.8 sqlalchemy

Answer 1

PostgreSQL jsonb_typeof, json. , jsonb_typeof(myModel.border) != 'null'. PostgreSQL

Answer 2

border JSON null, PostgresQL null:

from sqlalchemy.sql.expression import text
filter(myModel.border != text("'null'"))

( SQLAlchemy==1.1.14)

Answer 3

you can save this value using null ()

>>> from sqlalchemy import null

>>> filter(myModel.border != null())