Postgres JSON/JSONB column NULL or null or None

[joe meiring]

So I've got a model like such and using sqlalchemy v0.9.8

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?

[Michael Bayer]

The none_as_null flag doesn’t apply to comparisons right now (maybe it should), only to INSERT statements. If you actually want to deal with the JSON NULL value explicitly and differentiate that from a column that has no value, you probably want to leave that flag off.

For comparison, assuming you are looking for a non-null value that is the JSON value ’null’, you can force the comparison with text:

print s.query(myModel).filter(myModel.border == text("'null'")).all()






>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[joe meiring]

Great, that works