Michael Angeletti

Perhaps you have a profiles table with 5-10 really specific columns (e.g., blog_url, pet_name, favorite_framework_1, favorite_framework_2, etc.) with empty values in half the rows? Or, maybe you're a purist like me and you've just left some features out to avoid bloating your tables.

PostgreSQL 9.4 adds the jsonb data type^{[3, 4]}. Now you've got one of the great features of Mongo, et al – the natively supported ability to store and efficiently query data in a valid JSON format – and you can attach it right to a table in an ACID compliant database.

Fast-forward to Django 1.9

Django 1.9 (alpha release is in roughly 1 week) adds support for Postgres's new jsonb data type via JSONField, and that's a bigger deal than you'd guess based on the quick mention it gets in the release notes.

In fact, it's amazing:

Profile.objects.create(
    name='Michael Angeletti',
    info={
        'languages': [
            {
                'name': 'English',
                'level': 'native'
            },
            {
                'name': 'French',
                'level': 'un peu'
            }
        ],
        'websites': [
            'http://orokusaki.posthaven.com',
            'http://stackoverflow.com/users/128463/orokusaki'
        ],
        'city': 'Jupiter'
    }
)

Before JSONField, the above example would require language and website tables and a Profile.city field to be as robust, and the city field would probably remain empty in most rows anyway. HStoreField was added in Django 1.8, but hstore only supports strings (e.g., a list of websites) and you can't nest values. In the above example, HStoreField covers the Profile.city case, but not the others.

In my personal experience developing an ecommerce shopping cart app in the past, I would have used this JSONField to store payment transaction data (e.g., from Stripe), had it been available, but in the shopping cart I probably wouldn't have stored something like an address in this way, since an address is a predictable format. Other things a hosted ecommerce app could use this for are configuring settings for a merchant (e.g., site title, colors, etc.).

Although Django's new jsonb support handles serialization, deserialization and validation for you, the real magic is in the ORM's query support for this new data type:

>>> Profile.objects.filter(info__city='Jupiter')
[<Profile: Michael Angeletti>]
>>> Profile.objects.filter(info__websites__contains='http://orokusaki.posthaven.com')
[<Profile: Michael Angeletti>]
>>> Profile.objects.filter(info__languages__contains={'name': 'French', 'level': 'un peu'})
[<Profile: Michael Angeletti>]
>>> Profile.objects.filter(info__languages__contains={'name': 'French', 'level': 'expert'})
[]