Skip to main content

Django Postgres JSONB Fields support with lookups

Project description

# django-pgjsonb
Django Postgres JSONB Fields support with lookups

Originaly inspired by [django-postgres](

Change Logs
2017-09-13: 0.0.29
Fix JsonAdapter Python2 incompatible

2017-09-11: 0.0.28
Fix contained_by contains empty {}
Fix error of has_any/ has_all
Fix lookup ```filter(meta={})```

2017-08-31: 0.0.27
Fix as_{} lookup for python3

2017-08-31: 0.0.26
Fix has lookup after Django 1.10

Supress exception when drop index and the index already removed.

2017-03-14: 0.0.24
Add support for __near lookup with postgres earthdistance plugin, Thanks to @steinliber

2016-06-01: 0.0.23
Fix value from select_json not been decode from json introduce by 0.0.18

2016-03-24: 0.0.22
Fix error #11 remove the unexpect decode float to Decimal

2016-03-19: 0.0.21
Fix error #10

2016-03-09: 0.0.20
Add the array length for select_json

2016-03-08: 0.0.19
fix when add a json field with db_index=True and it's fail to generate the create index sql

2016-03-01: 0.0.18
we want to be able to use customize decoder to load json, so get avoid the psycopg2's decode json, just return raw text then we deserilize by the field from_db_value

2016-03-01: 0.0.17
patch the django serilizer to not return the stringifyed result

2015-07-23: 0.0.16
Add support for ./ inspectdb

2015-06-10: 0.0.15
Add support for db_index to add GIN index


`pip install django-pgjsonb`


from django_pgjsonb import JSONField

class Article(models.Model):

Encoder and Decoder Options
by define decode_kwargs and encode_kwargs you can use your customize json dump and load behaveior, basicly these parameters will just pass to json.loads(**decode_kwargs) and json.dumps(**encode_kwargs)

here is an example for use [EJSON]( to store native datetime object

import ejson

class Article(models.Model):

Add Index
[new add in 0.0.15]

jsonb field support gin type index to accelerator filtering. Since JSON is a data structure contains hierarchy, so the index of jsonb field will be more complicate than another single value field. More information, please reference [Postgres document 8.14.4](


When set db_index as True and do not set db_index_options, it will generate default GIN index, most case it's enough.

When specify ```db_index_options={"only_contains":True}```, the index will be as the non-default GIN operator class jsonb_path_ops that supports indexing the ```contains``` operator only, but it's consume less space and more efficient.

When specify the path parameter in db_index_options, ```db_index_options={"path":"authors__name"}```, then index will generate to the specify path, so that ```Article.objects.filter(meta__authors__name__contains=["asd"])``` can utilize the index.

So you can create multiple index in one JSONField, just pass the db_index_options parameter as a list that contains multiple options, it will generate multiple correspond indexes. Empty dict stand for the default GIN index.

###Contains a wide range of lookups supported natively by postgres

1. `has` :if field has specific key *`("?")`*


2. `has_any` : if field has any of the specific keys *`("?|")`*

3. `has_all` : if field has all of the specific keys *`("?&")`*

4. `contains` : if field contains the specific keys and values *`("@>")`*

5. `in` or `contained_by` : if all field key and value contain by input *`("<@")`*

6. `len` : the length of the array, transform to int, and can followed int lookup like gt or lt *`("jsonb_array_length()")`*

7. `as_(text,int,float,bool,date,datetime)` : transform json field into specific data type so that you can follow operation of this type *`("CAST(FIELD as TYPE)")`*

8. `path_(PATH)` : get the specific path, path split by '_' *`("#>")`*

Article.objects.filter(meta__path_author_articles__contains="show me the money")

Extend function to QuerySet

JSON_PATHS in the format of paths separated by "__",like "meta__location__geo_info". It will use the queryset's `extra` method to transform a value inside json as a field.
If no field_name provided, it will generate a field name with lookups separate by _ without the json field self's name, so `select_json("meta__author__name")` is equal to `select_json(author_name="meta__author__name")`


This operation will translate to sql as

SELECT "article"."meta"->'location'->'geo_info' as "geo", "article"."meta"->'author'->'name' as "author_name"

[new add in 0.0.20]
You can also select the length of a json array as a field by use Length object

from django_pgjsonb.fields import Length

After select_json, the field_name can be operate in values() and values_list() method, so that

1. select only one specific value inside json
2. to group by one value inside json

is possible.


# select only "meta"->'tags'

# GROUP BY "meta"->'author'->'name'

support geo search in jsonb

**require**: postgresql plugin:

1. cube

2. earthdistance

3. to install these two plugin, run command below in psql

CREATE EXTENSION earthdistance;

how to save location json record

{"location": [30.2, 199.4]} # just keep a latitude, longitude list


Article.objects.filter(data__location__near=[39.9, 116.4,5000]) # latitude,longitude,search range


Article.objects.filter(data__location__near='39.9,116.4,5000') # latitude,longitude, search range

**Alert**: if you don't pass exact number of params, this filter will not be used

**for more earthdistance**, see [Postgresql Earthdistance Documentation](


#####For more information about raw jsonb operation, please see [PostgreSQL Documentation](

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
django-pgjsonb-0.0.32.tar.gz (10.3 kB) Copy SHA256 hash SHA256 Source None

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page