cant insert NULL into a json column if column is present

Issue #3159 resolved
Michael Bayer
repo owner created an issue

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()}])

Comments (4)

  1. Michael Bayer reporter

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

  2. Michael Bayer reporter

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

  3. 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.

  4. Log in to comment