Skip to content
Django Postgres JSONB Fields support with lookups
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Type Name Latest commit message Commit time
Failed to load latest commit information.


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

2017-05-18:0.0.25 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 ("?")
  1. has_any : if field has any of the specific keys ("?|")
  1. has_all : if field has all of the specific keys ("?&")
  1. contains : if field contains the specific keys and values ("@>")
  1. in or contained_by : if all field key and value contain by input ("<@")
  1. len : the length of the array, transform to int, and can followed int lookup like gt or lt ("jsonb_array_length()")
  1. 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)")
  1. 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

You can’t perform that action at this time.