How do I use PostgreSQL 9.2 JSON data type in Django?

Question

PostgreSQL 9.2 has native json data type support. How do i create a django model that can use this data type as one of the model fields?

Answer 1

[Nb. A lot happened since the question was posted so I thought updating the answer too - now including new Django-postgresql and PostgreSQL 9.3 information]

PostgreSQL 9.3

JSON support in PostgreSQL(see the doc is becoming more and more interesting. It's now possible to search on the JSON fields as if they were database columns! This support is still basic and not on par with standard column operators (see before), as such it's going to be fidgely to use through Django ORM.

PostgreSQL 9.2

The JSON data type is basically text in pgSQL 9.2. What's added is JSON validation - useful, but not head turning.

**Django and PostgreSQL JSON

A recent Kickstarted backed development for advanced PostgreSQL support in Django was funded a few months ago. It will include advanced support for the JSON type, but probably only in 9.4:

The Postgres team have recently merged support for a jsonb datatype - binary stored JSON. It is quite likely that I will delay JSON support until Postgres 9.4 is out and only support the jsonb data type. There are several reasons for this, the most significant being that the current json data type is severly limited in its implementation, lacking even an equality operator. This means that some parts of Django annotation code generate invalid queries (see this report) and also means that a __exact lookup has to be forbidden. To handle all these edge cases properly in Django would result in a huge amount of complexity, and the benefits you gain over just storing json in a text field are actually quite limited. 9.4 is due out towards the end of this year, so as a result JSON fields are likely to only feature in the 1.8 release.

Source: mjtamlyn blog

Psycopg also now supports natively the JSON field (as well as HSTORE).

** DIY in Django **

In django it's pretty easy to create your own model field that uses a special Database type, see field db_type().

Please notice that:

1. this would only work under pgSQL - you are limiting your code portability (though you can conditionally return a 'text' type on other databases)
2. you will currently mostly gain only database-side json validation
3. and of course the pleasure of retrieving directly a JSON object!

But be careful that Python do not have /direct/ support for JSON, so you are not really skipping the json dump/load cost in python; you do avoid string conversion - but I haven't checked the internals or benchmarked to see if there is any real difference.

With PostgreSQL 9.3 and following looks like things might become more interesting

You could also start from the django JSONField and get a lot of boilercode plate already, simply override the db_type method.

What sounds, in my opinion, more interesting and worth a database lock-in (I love pgSql, in any case!), is the option in pgSQL to retun data as a JSON structure using array_to_json and row_to_json. This would require a more serious change to querysets though, which sounds out of the scope of your question.

Answer 2

As of Django 1.9, JSONField is available in the django.contrib.postgres.fields module that ships with Django. This field uses PostgreSQL field type jsonb (not json).