Implementing a Cross-DB JSONField
2020-09-19, 10:35–11:15, Virtual

Tired of dealing with structured data? Want to avoid database migrations? Try JSONField!

This talk explains the implementation of a cross-DB JSONField, a new feature released in Django 3.1, that can be used on all database backends supported by Django.


Having structured data is great, but sometimes you just want to store some data without caring much about the structure. This is especially true when you're prototyping something: you're still unsure how your database will look like, but you know you're going to store something in it. Well, enter JSONField!

JSONField, from the Django docs:

"A field for storing JSON encoded data. In Python the data is represented in its Python native format:
dictionaries, lists, strings, numbers, booleans, and None."

Before 3.1, official support for JSONField only exists through the django.contrib.postgres module, which means it can only be used on PostgreSQL. To use JSONField on other database backends, one needs to use third-party packages such as jsonfield (1k+ stars), django-mysql, and oracle-json-field. A ticket for cross-DB JSONField (#12990) was made 10 years ago. The abundance of third-party packages for JSONField suggests that it's an in-demand feature.

The existing PostgreSQL JSONField uses the built-in jsonb data type. MariaDB and MySQL have a JSON data type along with JSON functions. SQLite can store JSON data as TEXT and use the JSON1 extension which includes JSON functions. Oracle Database also has JSON functions and can store JSON data with the IS JSON constraint.

So, a cross-DB JSONField is feasible, but it can be a bit tricky to implement because each backend deals with JSON data differently. Besides storing and loading Python objects into/from JSON, JSONField also supports some custom lookups and transforms that can be used to query JSON data. This talk will cover how saving JSON data and the custom queries are implemented in the cross-DB JSONField.