Blog

New JSONField will be added in Django 1.9, it can be used with PostgreSQL >= 9.4. Let’s try to work with it and find out, in what situations it can be useful.

Currently django 1.9 alpha is available, final version is scheduled on December 2015. Alpha can be installed with pip:

       

pip install --pre django
 

For understanding this assume that we have an e-commerce site, where we offer products of different types. For example, laptops and t-shirts. Obviously, such goods will have different attributes: t-shirts will have size, color and laptops – screen size, CPU frequency, hard drive and so on. One of the approaches to design such data in SQL is Entity–attribute–value model (EAV).

currently now we have JSON, so let’s try to organise data using this type.

Create simple model for products:

       
from django.db import models
from django.contrib.postgres.fields import JSONField

class Category(models.Model):
    name = models.CharField(max_length=100)

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.ForeignKey(Category)
    price = models.IntegerField()
    attributes = JSONField()

    def __str__(self):
        return self.name

As we can see, there are several common fields for all products (name, category, price) plus specific to particular product attributes (as JSON field).

Create objects:

       
tshirt = Category.objects.create(name='tshirts')
notebook = Category.objects.create(name='notebook')

# Tshirts
Product.objects.create(name='Silk tshirt', category=tshirt, price=100, attributes={
    'colors': ['red', 'black'],
    'sizes': ['S', 'M'],
    'model': 'polo',
    'material': 'silk',
})

Product.objects.create(name='Bamboo tshirt', category=tshirt, price=120, attributes={
    'colors': ['white', 'yellow'],
    'sizes': ['M', 'L', 'XL'],
    'model': 'poet',
    'material': 'bamboo',
})

# Notebooks
Product.objects.create(name='MacBook Pro', category=notebook, price=2000, attributes={
    'brand': 'Apple',
    'screen': 15.0,
    'speed': 2200,
    'hd': 256,
})

Product.objects.create(name='ATIV Book 9', category=notebook, price=1200, attributes={
    'brand': 'Samsung',
    'screen': 12.2,
    'speed': 2400,
    'hd': 128,
})

Queries

Let’s see, what queries we can make

1. Get t-shirts with both ‘M’ and ‘L’ sizes:

       
>>> Product.objects.filter(category=tshirt, attributes__contains={'sizes': ['M', 'L']})
[]

2. Get t-shirts with both ‘M’ and ‘L’ sizes, both white and yellow colors, with poetry on it (model=poet):

       
>>> Product.objects.filter(category=tshirt,
    attributes__contains={'sizes': ['M', 'L'], 'colors': ['white', 'yellow'],
    'model': 'poet'})
[]

3. Get laptops with CPU frequency 2400 and screen size 12.2

       
>>> Product.objects.filter(category=notebook,
    attributes__contains={'speed': 2400, 'screen': 12.2})
[]

4.Get t-shirts with red color, model polo and with size ‘M’ or ‘L’

       
>>> Product.objects.filter(category=tshirt,
    attributes__contains={'colors': ['red'], 'model': 'polo'},
    attributes__sizes__has_any_keys=['M', 'L'])
[]

5.Get laptops with CPU greater that 2000 and screen larger that 13

       
>>> Product.objects.filter(category=notebook, attributes__speed__gt=2000,
    attributes__screen__gt=13)
[]

Get laptops with CPU frequency equal to 2200 or 2400

       
>>> Product.objects.filter(category=notebook, attributes__speed__in=(2200, 2400))
[, ]

or like this:

       
>>> from django.db.models import Q
>>> Product.objects.filter(category=notebook).filter(
    Q(attributes__contains={'speed': 2200}) | Q(attributes__contains={'speed': 2400}))

Leave a Reply

Your email address will not be published. Required fields are marked *