Issues

Issue #3159 resolved

cant insert NULL into a json column if column is present

Mike 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. Mike 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. Mike 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. reptilicus

    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