Sum aggregation over list items in Django JSONField

jnns 0
Viewed 98

I'd like to calculate the sum of all elements in a list inside a JSONField via Django's ORM. The objects basically look like this:

[
  {"score": 10},
  {"score": 0},
  {"score": 40},
  ...
]

There are several problems that made me use a Raw Query in the end (see SQL query below) but I'd like to know if it is possible with Django's ORM.

SELECT id,
       SUM(elements.score) AS total_score
FROM my_table,
LATERAL (SELECT
  (jsonb_array_elements('results')->'score')::integer AS score
) AS elements
GROUP BY id 
ORDER BY total_score DESC

The main problems I faced is that the list in the JSONField needs to be turned into a set via jsonb_array_elements. Afterwards it is impossible to run an aggregate function over the results. Postgres complains:

aggregate function calls cannot contain set-returning function calls

Using a LATERAL FROM -- as widely suggested -- is not possible with the ORM. Not even with Django's .extra() queryset method because it is not possible to specify an additional table that is not quoted in the final query:

Model.objects.annotate(...).extra(
    tables="LATERAL (SELECT (jsonb_array_elements('results')->'score')::integer AS score) AS elements"
)
# ERROR: no relation "LATERAL (SELECT ..."
django-orm postgresql django
1 Answers

You can annotate the queryset with the score value from the JSONField, Cast it to an integer, retrieve the distinct values, and get the sum of whatever is left. I think the following query should do the trick:

from django.db.models import IntegerField
from django.db.models import Sum
from django.db.models.fields.json import KeyTextTransform
from django.db.models.functions import Cast

Model.objects.annotate(
    score=Cast(
        KeyTextTransform("score", "JSONField_name"),
        IntegerField(),
    )
).values("score").distinct().aggregate(Sum("score"))["score__sum"]

Note that you will still have to change the JSONField_name according to your model

WoodyG0
Related