Django实现JSONField内对象列表quantity字段求和的注解查询

核心问题：Django ORM内置的JSON键提取语法不支持遍历长度不固定的JSON数组做聚合，需要借助数据库原生JSON函数，通过自定义Func表达式实现，不同数据库的JSON函数语法存在差异，对应实现如下：

PostgreSQL 后端（Django 3.0+） #

PostgreSQL是Django JSONField支持最完善的数据库，分版本提供两种实现：

PostgreSQL 12及以上版本（推荐写法） #

PG12开始支持JSON路径内置聚合函数，不需要手动展开数组，写法简洁且性能更好：

from django.db.models import Func, F, Value, IntegerField
from django.db.models.functions import Coalesce

qs = MyModel.objects.annotate(
    total_quantity=Coalesce(
        Func(
            F("data"),
            Value("$.items[*].quantity.sum()"),
            function="jsonb_path_query_first",
            output_field=IntegerField()
        ),
        0
    )
)

PostgreSQL 11及以下版本 #

低版本不支持路径内聚合，需要先提取所有quantity值为数组，展开后再求和：

from django.db.models import Func, Sum, F, Value, IntegerField
from django.db.models.functions import Coalesce

class JsonbPathQueryArray(Func):
    function = "jsonb_path_query_array"
    arity = 2

class JsonbArrayElements(Func):
    function = "jsonb_array_elements"
    arity = 1

qs = MyModel.objects.annotate(
    total_quantity=Coalesce(
        Sum(
            JsonbArrayElements(
                JsonbPathQueryArray(F("data"), Value("$.items[*].quantity"))
            ),
            output_field=IntegerField()
        ),
        0
    )
)

MySQL 8.0.17+ 后端 #

MySQL需要通过JSON_TABLE函数把JSON数组映射为虚拟表后做聚合，实现如下：

from django.db.models import Func, F, Value, IntegerField, Subquery, OuterRef, Sum
from django.db.models.functions import Coalesce

qs = MyModel.objects.annotate(
    total_quantity=Coalesce(
        Subquery(
            MyModel.objects.filter(pk=OuterRef("pk"))
            .annotate(
                qty=Func(
                    F("data"),
                    Value("$.items[*].quantity"),
                    Value("$[*]"),
                    function="JSON_TABLE",
                    template="%(function)s(%(expressions)s COLUMNS (qty INT PATH '$')) AS jt"
                )
            )
            .values("pk")
            .annotate(total=Sum("qty", output_field=IntegerField()))
            .values("total")
        ),
        0
    )
)

SQLite 后端（开发环境用，需开启JSON1扩展） #

SQLite需要开启JSON1扩展才能支持JSON函数，把PG低版本实现里的函数名替换为SQLite对应函数即可：

• jsonb_path_query_array 替换为 json_extract
• jsonb_array_elements 替换为 json_each

注意事项 #

• 不要尝试用data__items__0__quantity这类双下划线逐层取键的方式求和，该语法只能取数组固定下标的值，无法遍历长度不固定的数组做聚合。
• 上述实现里的Coalesce函数作用是把data为null、items为空数组场景下的返回值从null转为0，如果业务上需要保留null值，直接去掉外层Coalesce即可。
• 大表场景下不推荐实时查询时做JSON内部聚合，JSON字段的计算无法利用普通索引，查询性能会很差。最优方案是重写模型的save方法，在数据写入时就提前计算好items下quantity的总和，存入单独的IntegerField字段，查询时直接读取该字段即可。

内容的提问来源于stack exchange，提问作者Nalin Dobhal