Агрегация суммы по элементам списка в Django JSONField

Я хочу вычислить сумму всех элементов списка внутри JSONField через ORM Django. Объекты в основном выглядят следующим образом:

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

Есть несколько проблем, которые заставили меня использовать Raw Query в конце концов (см. SQL запрос ниже), но я хотел бы знать, возможно ли это с ORM от Django.

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

Основные проблемы, с которыми я столкнулся, заключаются в том, что список в JSONField необходимо превратить в набор через jsonb_array_elements. После этого невозможно выполнить агрегатную функцию над результатами. Postgres жалуется:

вызовы агрегатных функций не могут содержать вызовы функций с возвратом набора

Использование LATERAL FROM - как широко предлагается - невозможно в ORM. Даже с методом кверисета .extra() от Django, потому что невозможно указать дополнительную таблицу, которая не цитируется в конечном запросе:

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

Вы можете аннотировать набор запросов значением оценки из JSONField, Cast привести его к целому числу, получить distinct значения и получить sum все, что осталось. Я думаю, что следующий запрос должен решить эту задачу:

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"]

Обратите внимание, что вам все равно придется изменить JSONField_name в соответствии с вашей моделью

Вернуться на верх