Preserve Python None/NULL when storing JSON in Postgres

Question

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.

You're doing it wrong. You should use a JSON column type in PostgreSQL... — Antti Haapala
– Antti Haapala, Commented Aug 4, 2017 at 20:48
You can't have None (or null, the JSON equivalent) be a key in a JSON object. Your dict simply isn't compatible with the JSON format. — user2357112
– user2357112, Commented Aug 4, 2017 at 20:49

Community · Accepted Answer · 2021-10-07 08:58:39Z

4

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.

edited Oct 7, 2021 at 8:58

CommunityBot

11 silver badge

answered Aug 4, 2017 at 20:56

user2357112

286k32 gold badges490 silver badges569 bronze badges

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

2 Comments

Drew Over a year ago

Is there another dictionary-like datatype that can handle non-string keys?

user2357112 Over a year ago

@Drew: You may be interested in YAML.

Collectives™ on Stack Overflow

Preserve Python None/NULL when storing JSON in Postgres

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related