Filter postgres JSON column by null value in SQLAlchemy

Question

So I've got a model like such:

class myModel(Base):

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

How can I query for rows that don't have a border? I've 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 in postgres as a "JSON encoded null value". Its definitely getting serialized back to a python None when instantiated, but I have no idea how to query against it. It looks like you can set none_as_null on the column, i.e.:

Column(JSONB(none_as_null=True))

Which replaces the JSON encoded null with a SQL null, but that seems strange to have to do on all columns. What am I missing here?

edit: should mention this is v0.9.8 of sqlalchemy

Answer 1

from sqlalchemy>=0.7.9, you could use the filter operator .isnot instead of comparing constraints like this - filter(myModel.border.isnot(None))

To get models with border nulls, do filter(myModel.border.is_(None))

Answer 2

PostgreSQL has function jsonb_typeof, that returns string type of json value. So you can filter null values as jsonb_typeof(myModel.border) != 'null'. You can find details in the PostgreSQL documentation