Background

You may have a Django app with a model, in which, you are storing some extra/unstructured information in a JSONField. An illustration:

from django.db import models

# Create your models here.
class Person(models.Model):
    first_name = models.TextField()
    last_name = models.TextField()
    extra_information = models.JSONField()

Suppose some of the values you are storing in extra_information are dates/timestamps. For example, one of the records could look like:

>>> Person.objects.first().__dict__
{'_state': <django.db.models.base.ModelState object at 0x110cdcb80>, 'id': 1, 'first_name': 'John', 'last_name': 'Doe', 'extra_information': {'birth_date': '1986-05-14'}}

Problem

You want to use Extract to extract the month. Maybe you want to filter all the Person records, whose birthday is in the month of May.

You would write this query as:

>>> query_set = Person.objects.annotate(
    x=Extract(Cast("extra_information__birth_date", output_field=DateField()), "month")
).filter(x=5)

But, you would run into this error:

django.db.utils.ProgrammingError: cannot cast type jsonb to date
LINE 1: ..."app_person"."extra_information" -> 'birth_date'))::date) AS...

This is because, if you look at the corresponding SQL query, it uses the - > operator to access the birth_date key from the JSON object:

SELECT "app_person"."id", 
       "app_person"."first_name", 
       "app_person"."last_name", 
       "app_person"."extra_information", 
       extract(\'MONTH\' FROM (("app_person"."extra_information" - > birth_date))::date) AS "x" 
FROM   "app_person" 
WHERE  extract(\'MONTH\' FROM (("app_person"."extra_information" - > birth_date))::date) = 5

We want it to use the - >> operator instead. Here is the difference between the two. -> returns the value as a jsonb object, while - >> returns it as text. We want the text value so that we can extract the month from it, after casting it to DateField.

Solution

Use KeyTextTransform. Here is the relevant code, which is self explanatory:

class KeyTextTransform(KeyTransform):
    operator = '->>'
    nested_operator = '#>>'
    output_field = TextField()

So your Django query becomes:

query_set = Person.objects.annotate(x =
    Extract(Cast(KeyTextTransform(
            'birth_date',
            'extra_information'
        ) output_field =
        DateField()), 'month')).filter(
    x = 5)

And the corresponding SQL query:

SELECT "app_person"."id", 
       "app_person"."first_name", 
       "app_person"."last_name", 
       "app_person"."extra_information", 
       extract(\'MONTH\' FROM (("app_person"."extra_information" ->> birth_date))::date) AS "x" 
FROM   "app_person" 
WHERE  extract(\'MONTH\' FROM (("app_person"."extra_information" ->> birth_date))::date) = 5