Experimental jsonb support by Charl P. Botha <cpbotha@vxlabs.com>

1. Introduction

django-pgjson is a little library that add a bridge for PostgreSQL json field to Django.

Is a little brother of django-hstore

2. Project Maturity

Since django-pgjson is a young project there can be some API breakage.

3. Install

This section covers a installing django-pgjson and its requirements.

3.1. Requirements

  • Python 2.7 or 3.3+

  • Django 1.6 and 1.7

  • psycopg2 2.5.0+

  • PostgreSQL 9.2+

3.2. Supports

  • All json types. Including simple strings, floats, integers, booleans and null’s.

  • Works well with python serialization.

  • Experimental: jsonb support in PostgreSQL >= 9.4

3.3. Limitations

  • Only supports PostgreSQL >= 9.2

  • PostgeSQL 9.4 is required for jsonb.

  • No django admin support at this momment.

  • Limited set of django 1.7 lookups (will be improved with postgresql 9.4)

3.4. Stable

pip install django-pgjson

3.5. Development

pip install -e git+git://github.com/niwibe/django-pgjson#egg=django-pgjson

4. Setup

No additional steps are required, just use a field.

4.1. Note to South users

If you keep getting errors like There is no South database module south.db.None for your database., add the following to settings.py:

SOUTH_DATABASE_ADAPTERS = {'default': 'south.db.postgresql_psycopg2'}

5. Usage

The library provides these classes:

  • django_pgjson.fields.JsonField
    An ORM field which stores python dict in an json type column.

  • django_pgjson.fields.JsonBField + Same as above, but using the new jsonb field in PostgreSQL >= 9.4. Experimental.

5.1. Model setup

Model definition is straightforward:
from django.db import models
from django_pgjson.fields import JsonField

class Something(models.Model):
    name = models.CharField(max_length=32)
    data = JsonField()  # can pass attributes like null, blank, ecc.

Now data attribute works like python standard dictionary.

PostgreSQL json field also can store arrays and any json valid value but, django_pgjson at this momment only handles python dict.

5.2. Querying

5.2.1. Key or Index lookup

If you are using django 1.7, you can use index or key lookup for filtering
>>> Something.object.bulk_create([
...     Something(data={"name": "foo"}),
...     Something(data={"name": "bar"})
... ])
>>> Something.objects.filter(data__at_name="foo").count()
It also works for array indexes
>>> Something.object.bulk_create([
...     Something(data=[1,2,3,4]),
...     Something(data=[4,5,6,7])
... ])
>>> Something.objects.filter(data__at_0=4).count()
Same example but using Django < 1.7
>>> Something.objects.extra(where=["data->>'name' = %s"], params=["foo"]).count()

5.2.2. Array length lookup

With same data as previous example:

With Django >= 1.7
>>> Something.objects.filter(data__array_length=4).count()
With Django < 1.7
>>> Something.objects.extra(where=["json_array_length(data) = %s"],
...                         params=["foo"]).count()
At this momment, with postgresql 9.3 as last stable version there is only a limited set of native operators for json type. When postgresql 9.4 is released, more lookups will be added.

5.2.3. jsonb containment lookup

If you are using django 1.7 and PostgreSQL >= 9.4, you can use the special jsonb containment operator, which can be accelerated by a GiN index on the jsonb field.

Use the containment operator as follows:
>>> Something.object.bulk_create([
...     Something(data={"name": "foo", "tags": ["sad", "romantic"]}),
...     Something(data={"name": "bar", "tags": ["sad", "intelligent"]})
... ])
>>> Something.objects.filter(data__jcontains={"tags": ["sad"]}).count()

We will implement support for more of the jsonb operators in the near future. See http://www.postgresql.org/docs/9.4/static/datatype-json.html for more information on what’s possible, and feel free to send a pull request.

6. Developers

6.1. Running tests

Assuming one has the dependencies installed, and a PostgreSQL 9.0+ server up and running:

python runtests.py

6.2. Documentation

The documentation is written using asciidoc and if you want build it you should install the following dependencies: gnu make, asciidoc and pygments.

Build documentation
cd doc/
chromium index.html
Deploy documentation
git push -u origin gh-pages
git checkout master

6.3. How to contribute

  • Follow PEP8, Style Guide for Python Code

  • Fork this repo

  • Write code

  • Write tests for your code

  • Ensure all tests pass

  • Document your changes

  • Send pull request

6.4. Deprecation policy

At any momment of time, django-pgjson developers will mantain support for two versions of django.