Django 2.2中PostgreSQL JSONField数组的分组聚合求和问题

在PostgreSQL 9.4中用Django 2.2的JSONField存储了数组形式的JSON数据，数据库有多条这类记录。需要把所有记录里JSON数组中的对象按team_members_name分组，统计每个用户的rating总和，最后通过DRF接口返回结果。目前的代码只能处理单个JSON对象，没法遍历数组和多条记录，试过多种方法都没成，甚至考虑重构模型，求可行方案。

JSON示例：

[{"rating": 6, "companyvalue_id": 188, "team_members_name": "pidofod tester", "users_teammember_id": 2793}, {"rating": 7, "companyvalue_id": 207, "team_members_name": "pidofod tester", "users_teammember_id": 2793}, {"rating": 4, "companyvalue_id": 207, "team_members_name": "xakir tester", "users_teammember_id": 2795}]

现有尝试代码：

def data_rating(self):
    model = apps.get_model('model', 'ModelName')
    model_count = model.objects.all()

    return model_count.objects.annotate(
        rating=Cast(
            KeyTextTransform("rating", "data"),
            IntegerField(),
        )
    ).values("rating").distinct().aggregate(Sum("rating"))["rating__sum"]

不需要重构模型，下面两种方法可以解决问题，优先推荐数据库层面的处理，性能更好。

方法1：数据库层面直接统计（高效推荐） #

PostgreSQL提供了json_array_elements（如果是jsonb类型就用jsonb_array_elements）函数，可以把JSON数组拆成单独的行，之后就能分组求和了。

方式A：原生SQL实现 #

直接用Django的数据库连接执行原生SQL，简洁高效：

from django.db import connection
from rest_framework.response import Response

def get_rating_summary(self):
    with connection.cursor() as cursor:
        # 替换model_modelname为你的实际表名（格式：app名_模型名）
        cursor.execute("""
            SELECT 
                elem->>'team_members_name' AS username,
                SUM((elem->>'rating')::integer) AS total_rating
            FROM 
                "model_modelname",
                json_array_elements("data") AS elem
            WHERE 
                "data" IS NOT NULL AND "data" != '[]'
            GROUP BY 
                username
            ORDER BY 
                total_rating DESC;
        """)
        rows = cursor.fetchall()
    
    # 转成前端需要的格式
    result = [{"team_members_name": row[0], "total_rating": row[1]} for row in rows]
    return Response(result)

方式B：Django ORM封装实现 #

如果不想写原生SQL，可以用ORM结合自定义函数来实现：

from django.db.models import Func, Sum
from django.db.models.expressions import RawSQL
from rest_framework.response import Response
from your_app.models import ModelName  # 替换成你的模型

# 封装PostgreSQL的json数组展开函数
class JsonArrayElements(Func):
    function = 'json_array_elements'
    template = "%(function)s(%(expressions)s)"

def get_rating_summary(self):
    queryset = ModelName.objects.filter(data__isnull=False, data__ne='[]') \
        .annotate(elem=JsonArrayElements('data')) \
        .annotate(
            username=RawSQL("elem->>'team_members_name'", []),
            rating=RawSQL("(elem->>'rating')::integer", [])
        ) \
        .values('username') \
        .annotate(total_rating=Sum('rating')) \
        .order_by('-total_rating')
    
    # 转换格式返回
    result = [
        {"team_members_name": item['username'], "total_rating": item['total_rating']}
        for item in queryset
    ]
    return Response(result)

如果你的JSONField是jsonb类型，把JsonArrayElements里的function改成jsonb_array_elements即可。

方法2：Python内存中统计（适合小数据量） #

如果数据库层面操作有限制，也可以把数据拉到Python里处理，适合数据量不大的场景：

from collections import defaultdict
from rest_framework.response import Response
from your_app.models import ModelName

def get_rating_summary(self):
    rating_total = defaultdict(int)
    # 批量获取所有非空的JSON数组
    records = ModelName.objects.filter(data__isnull=False, data__ne='[]').values_list('data', flat=True)
    
    for data_array in records:
        for item in data_array:
            username = item.get('team_members_name')
            rating = item.get('rating', 0)
            if username and isinstance(rating, int):
                rating_total[username] += rating
    
    # 转成返回格式
    result = [
        {"team_members_name": name, "total_rating": total}
        for name, total in rating_total.items()
    ]
    return Response(result)

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