CODEWITHSUNDEEP
pythonjsonpostgresqlnull
Drew
Drew

Reputation: 115

Preserve Python None/NULL when storing JSON in Postgres

I have a PostgreSQL database table that has a JSON datatype column. When I store JSON strings in the database, None is not preserved. This can be reproduced with this code snippet:

import json,psycopg2

dictionary = {}
dictionary[None] = 0

conn = psycopg2.connect("host='localhost' dbname='testdb' user='postgres' 
password='postgres'")
cursor = conn.cursor()

cursor.execute("""INSERT INTO testtable (json_column) VALUES (%s)""", 
(json.dumps(dictionary), ))
cursor.execute("""SELECT json_column FROM testtable""")
result = cursor.fetchall()[0][0].keys()[0]

print result
print type(result)
if result is None:
    print 'result is None'
else:
    print 'result is not None'

The output of this Python code is:

drew@debian:~/python$ python test.py
null
<type 'unicode'>
result is not None
drew@debian:~/python$

How can I store None as a key in the JSON column? The JSON object also has keys for 'None' and 'null', so the value stored must be None or null.

Upvotes: 1

Views: 1169

Answers (1)

user2357112
user2357112

Reputation: 281262

From RFC 7159:

An object structure is represented as a pair of curly brackets surrounding zero or more name/value pairs (or members). A name is a string.

And from the Python docs:

Keys in key/value pairs of JSON are always of the type str. When a dictionary is converted into JSON, all the keys of the dictionary are coerced to strings.

JSON does not have any concept of non-string dict keys. If you want those, you don't want JSON. The closest you'll get with JSON is detecting the string None gets converted to and hoping that you never need to actually use the string 'null' as a dict key.

Upvotes: 4

Related Questions