如何从PostgreSQL提取Django模型JSONField的唯一(foodid, fieldname)对？

问题背景 #

我们有一个包含JSONField字段food的Django模型，对应PostgreSQL表里存了约1000条数据，JSON结构固定为food->foodid->'field'->fieldname->{....}（其中foodid和fieldname是动态变化的值）。现在需要从数据库中提取所有唯一的(foodid, fieldname)对。

之前尝试的代码如下：

Model.objects.annotate(
 foodid=Func(
 F('food'),
 function='jsonb_object_keys',
 output_field=models.CharField()
 )
 ).annotate(
 field_name=Func(
 Value('food__') + F('foodid') + Value('__field'), 
 function='jsonb_object_keys',
 output_field=models.CharField()
 )
 ).values_list('food_id', 'field_names')

这段代码里第一个annotate能正常提取出所有foodid，但第二个annotate想用生成的foodid去获取对应的fieldname时，不管是拼接字符串路径还是用F('foodid__field')，都会让PostgreSQL误以为要去表中查找名为foodid的字段，根本实现不了需求。

解决方案 #

要实现这个需求，得利用PostgreSQL的JSONB函数逐层解析嵌套结构：先展开food的顶层键（也就是foodid），再根据每个foodid定位到对应的field对象，最后提取该对象的所有键（fieldname），最后去重即可。

完整代码实现

from django.db.models import Func, F, Value
from django.db.models.fields import CharField

unique_pairs = Model.objects.annotate(
    # 第一步：提取food字段的所有顶层键，即foodid
    foodid=Func(
        F('food'),
        function='jsonb_object_keys',
        output_field=CharField()
    )
).annotate(
    # 第二步：根据foodid定位到food->foodid->field，再提取该对象的所有键（fieldname）
    fieldname=Func(
        Func(
            F('food'),
            F('foodid'),
            Value('field'),
            function='jsonb_extract_path',
            output_field=CharField()
        ),
        function='jsonb_object_keys',
        output_field=CharField()
    )
).values_list('foodid', 'fieldname').distinct()  # 去重获取唯一对

为什么之前的代码不行？ #

之前尝试拼接字符串路径的思路完全错误：Django的F表达式在annotate中生成的是数据库层面的字段别名，不能直接当作JSON路径的一部分拼接。而jsonb_object_keys需要接收一个实际的JSONB对象作为参数，不是字符串格式的路径。用jsonb_extract_path可以动态根据foodid的值定位到嵌套的field对象，再提取其键值就没问题了。

内容的提问来源于stack exchange，提问作者Салават Абдудин