cant insert NULL into a json column if column is present

[Mike Bayer]

the serializer grabs None if it is present. for backwards compat we should support null() which currently also blows up:

from sqlalchemy import create_engine, Column, Table, MetaData, func, select, null
from sqlalchemy.dialects.postgresql import JSON

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
c = e.connect()
t = c.begin()

table = Table('json_test', MetaData(), Column('data', JSON))
table.create(c)

c.execute(table.insert(), [{"data": {"foo": "bar"}}, {"data": None}])

assert c.scalar(
    select([func.count('*')]).
    select_from(table).where(table.c.data == null()))


# probable workaround, also fails to invoke
#c.execute(table.insert(), [{"data": {"foo": "bar"}}, {"data": null()}])

[Mike Bayer]

-Fixed bug where Postgresql JSON type was not able to persist or otherwise render a SQL NULL column value, rather than a JSON-encoded 'null'. To support this case, changes are as follows:

• The value :func:.null can now be specified, which will always result in a NULL value resulting in the statement.

• A new parameter :paramref:.JSON.none_as_null is added, which when True indicates that the Python None value should be peristed as SQL NULL, rather than JSON-encoded 'null'.

Retrival of NULL as None is also repaired for DBAPIs other than psycopg2, namely pg8000.

fixes #3159

→ <<cset 3c6ff6adaec2>>

[Mike Bayer]

-Fixed bug where Postgresql JSON type was not able to persist or otherwise render a SQL NULL column value, rather than a JSON-encoded 'null'. To support this case, changes are as follows:

• The value :func:.null can now be specified, which will always result in a NULL value resulting in the statement.

• A new parameter :paramref:.JSON.none_as_null is added, which when True indicates that the Python None value should be peristed as SQL NULL, rather than JSON-encoded 'null'.

Retrival of NULL as None is also repaired for DBAPIs other than psycopg2, namely pg8000.

fixes #3159

→ <<cset 0e2fab96f23f>>

[joe meiring]

This issue is pretty old, but how exactly do you query for a null value in a JSON/JSONB column?

session.query(myModel).filter(myModel.jsonColumn == None) does not work.

[Mike Bayer]

try sqlalchemy.null() instead.