HStoreField

See also

HStoreField

class HStoreField ( ** options )

A field for storing key-value pairs. The Python data type used is a dict . Keys must be strings, and values may be either strings or nulls ( None in Python).

To use this field, you’ll need to:

  1. Add 'django.contrib.postgres' in your INSTALLED_APPS .

  2. Setup the hstore extension <create-postgresql-extensions> in PostgreSQL.

You’ll see an error like can't adapt type 'dict' if you skip the first step, or type "hstore" does not exist if you skip the second.

Note

On occasions it may be useful to require or restrict the keys which are valid for a given field. This can be done using the KeysValidator .

Querying HStoreField

In addition to the ability to query by key, there are a number of custom lookups available for HStoreField .

We will use the following example model: 

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

class Dog(models.Model):
    name = models.CharField(max_length=200)
    data = HStoreField()

    def __str__(self):
        return self.name

Key lookups

To query based on a given key, you can use that key as the lookup name: 

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie'})

>>> Dog.objects.filter(data__breed='collie')
<QuerySet [<Dog: Meg>]>

You can chain other lookups after key lookups: 

>>> Dog.objects.filter(data__breed__contains='l')
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>

If the key you wish to query by clashes with the name of another lookup, you need to use the hstorefield.contains lookup instead.

Warning

Since any string could be a key in a hstore value, any lookup other than those listed below will be interpreted as a key lookup. No errors are raised. Be extra careful for typing mistakes, and always check your queries work as you intend.

contains

The contains lookup is overridden on HStoreField . The returned objects are those where the given dict of key-value pairs are all contained in the field. It uses the SQL operator @> . For example: 

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador', 'owner': 'Bob'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})
>>> Dog.objects.create(name='Fred', data={})

>>> Dog.objects.filter(data__contains={'owner': 'Bob'})
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>

>>> Dog.objects.filter(data__contains={'breed': 'collie'})
<QuerySet [<Dog: Meg>]>

contained_by

This is the inverse of the contains <hstorefield.contains> lookup - the objects returned will be those where the key-value pairs on the object are a subset of those in the value passed. It uses the SQL operator <@ . For example: 

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador', 'owner': 'Bob'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})
>>> Dog.objects.create(name='Fred', data={})

>>> Dog.objects.filter(data__contained_by={'breed': 'collie', 'owner': 'Bob'})
<QuerySet [<Dog: Meg>, <Dog: Fred>]>

>>> Dog.objects.filter(data__contained_by={'breed': 'collie'})
<QuerySet [<Dog: Fred>]>

has_key

Returns objects where the given key is in the data. Uses the SQL operator ? . For example: 

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})

>>> Dog.objects.filter(data__has_key='owner')
<QuerySet [<Dog: Meg>]>

has_any_keys

Returns objects where any of the given keys are in the data. Uses the SQL operator ?| . For example: 

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'owner': 'Bob'})
>>> Dog.objects.create(name='Fred', data={})

>>> Dog.objects.filter(data__has_any_keys=['owner', 'breed'])
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>

has_keys

Returns objects where all of the given keys are in the data. Uses the SQL operator ?& . For example: 

>>> Dog.objects.create(name='Rufus', data={})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})

>>> Dog.objects.filter(data__has_keys=['breed', 'owner'])
<QuerySet [<Dog: Meg>]>

keys

Returns objects where the array of keys is the given value. Note that the order is not guaranteed to be reliable, so this transform is mainly useful for using in conjunction with lookups on ArrayField . Uses the SQL function akeys() . For example: 

>>> Dog.objects.create(name='Rufus', data={'toy': 'bone'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})

>>> Dog.objects.filter(data__keys__overlap=['breed', 'toy'])
<QuerySet [<Dog: Rufus>, <Dog: Meg>]>

values

Returns objects where the array of values is the given value. Note that the order is not guaranteed to be reliable, so this transform is mainly useful for using in conjunction with lookups on ArrayField . Uses the SQL function avalues() . For example: 

>>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'})
>>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'})

>>> Dog.objects.filter(data__values__contains=['collie'])
<QuerySet [<Dog: Meg>]>