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

opyate · Accepted Answer · 2017-12-20 14:56:34Z

5

This works for both border as JSON null and PostgresQL NULL:

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

(tested with SQLAlchemy==1.1.14)

answered Dec 20, 2017 at 14:56

opyate

5,4581 gold badge40 silver badges66 bronze badges

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

1 Comment

dappiu Over a year ago

Doesn't work for me with PostgreSQL 9.6 and SQLAlchemy 1.1.x or 1.2.x. It's throwing this error

ProgrammingError: operator does not exist: json <> unknown HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

I had success importing null from sqlalchemy and comparing with null()

Devi · Accepted Answer · 2018-05-09 06:09:18Z

4

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))

answered May 9, 2018 at 6:09

Devi

5,5232 gold badges36 silver badges28 bronze badges

1 Comment

Oleg Over a year ago

This one only works when the model specifies JSONB(none_as_null=True) or JSON(none_as_null=True). See JSON.none_as_null

Igor Rusinov · Accepted Answer · 2015-04-16 06:06:47Z

3

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

answered Apr 16, 2015 at 6:06

Igor Rusinov

312 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Use JSON.NULL. From the docs:

To insert or select against a value that is SQL NULL, use the constant null():
from sqlalchemy import null
conn.execute(table.insert(), json_value=null())
To insert or select against a value that is JSON "null", use the constant JSON.NULL:
conn.execute(table.insert(), json_value=JSON.NULL)

So in your case,

from sqlalchemy import JSON

myModel.query.filter(myModel.border != JSON.NULL)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Sep 19, 2018 at 20:00

raylu

2,8263 gold badges21 silver badges26 bronze badges

Comments

Vitalizzare · Accepted Answer · 2024-03-25 16:55:23Z

-1

from sqlalchemy import null

#If you try to insert data in db:  
YourModel(my_json_column = null())

#get data by filter: 
select(Table).where(Table.my_json_column.is_(None))

edited Mar 25, 2024 at 16:55

Vitalizzare

7,63611 gold badges23 silver badges49 bronze badges

answered Mar 25, 2024 at 10:48

Andry Heh

1

Comments

Eds_k · Accepted Answer · 2017-11-23 02:32:54Z

-2

you will be able to persist this using the value null()

>>> from sqlalchemy import null

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

answered Nov 23, 2017 at 2:32

Eds_k

1,20413 silver badges13 bronze badges

1 Comment

dappiu Over a year ago

It works for me with SQLAlchemy 1.1 or 1.2 and PostgreSQL 9.6 using the type sqlalchemy.dialects.postgresql.JSON While it doesn't work the suggestion of using the comparison with text("'null'").

Collectives™ on Stack Overflow

Filter postgres JSON column by null value in SQLAlchemy

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related