如何用Django ORM对JSONField未知非零键值进行Sum聚合

问题背景 #

你的Portfolio模型中，detail是可空的JSON字段，每条记录的键可能不同，需要对所有非零值的键执行求和聚合。现有纯Python遍历方案在数据量大时性能极差，而KeyTextTransform仅支持固定键，无法满足动态键的需求。

解决方案：借助数据库原生JSON函数 #

Django ORM本身没有直接支持动态键聚合的API，但可以通过自定义数据库函数，利用数据库的JSON解析能力，将JSON字段拆分为键值对行，再分组求和。以下分两种主流数据库实现：

1. PostgreSQL（推荐，Django JSONField默认对应PostgreSQL jsonb） #

PostgreSQL提供jsonb_each函数，可将JSON对象拆分为(key, value)的行记录。我们用自定义Django函数封装它：

from django.db import models
from django.db.models import Func, Sum, Q
from bestbuy.models import Portfolio

# 自定义函数封装PostgreSQL的jsonb_each
class JsonbEach(Func):
    function = 'jsonb_each'
    output_fields = [
        ('key', models.TextField()),
        ('value', models.FloatField()),
    ]

def calculate_sum_of_all_basket_coins():
    # 1. 过滤detail为空或空对象的无效记录
    # 2. 用JsonbEach将detail拆分为键值对行
    # 3. 过滤值为0的条目
    # 4. 按key分组求和
    aggregated = Portfolio.objects.filter(
        Q(detail__isnull=False) & ~Q(detail={})
    ).annotate(
        kv=JsonbEach('detail')
    ).filter(
        kv__value__gt=0
    ).values('kv__key').annotate(
        total=Sum('kv__value')
    )
    
    # 转换为字典格式返回
    return {item['kv__key']: item['total'] for item in aggregated}

2. MySQL 8.0+ #

MySQL 8.0及以上支持JSON_TABLE函数，可将JSON对象转换为关系表。自定义函数如下：

from django.db import models
from django.db.models import Func, Sum, Q
from bestbuy.models import Portfolio

# 自定义函数封装MySQL的JSON_TABLE
class JsonTable(Func):
    function = 'JSON_TABLE'
    template = "%(function)s(%(expressions)s, '$.*' COLUMNS(key VARCHAR(255) PATH '$[0]', value DOUBLE PATH '$[1]'))"
    output_fields = [
        ('key', models.TextField()),
        ('value', models.FloatField()),
    ]

def calculate_sum_of_all_basket_coins():
    aggregated = Portfolio.objects.filter(
        Q(detail__isnull=False) & ~Q(detail={})
    ).annotate(
        kv=JsonTable('detail')
    ).filter(
        kv__value__gt=0
    ).values('kv__key').annotate(
        total=Sum('kv__value')
    )
    
    return {item['kv__key']: item['total'] for item in aggregated}

关键优势 #

• 性能飞跃：所有计算在数据库端完成，无需将大量JSON数据拉取到Python内存处理，数据量越大优势越明显。
• 原生优化：直接利用数据库的JSON处理能力，避免手动解析JSON的额外开销。

注意事项 #

• 确认数据库版本支持对应JSON函数：PostgreSQL 9.4+支持jsonb_each，MySQL 8.0+支持JSON_TABLE。
• 如果detail中的值是字符串类型数字，需在自定义函数中添加类型转换（如CAST(value AS DOUBLE)）。
• 严格过滤无效记录，避免处理空对象或NULL值。

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