sql - Django JSONField，如何获取存储在列表中的值的出现次数

Question

数据库是postgres 9.4在我的表中，我使用的是 JSONField

class Item(models.Model):
    item = models.CharField(max_length=200)
    data = JSONField()

示例数据 JSONField 的 3 条记录:

{"color": ["yellow", "blue", "red"], "size": ["S", "L"], "material": "cotton"}

{"color": ["white", "blue", "gray"], "size": ["XL", "L"], "material": "cotton"}

{"color": ["yellow", "gray", "red"], "size": ["L", "XL"], "material": "cotton"}

我的目标是创建一个列表，其中包含每种出现的颜色和大小的计数:

颜色:黄色 2蓝色 2红色 2灰色 2白1

尺寸:大号 3特大21个

这可能吗？什么是性能方面的最佳解决方案。

到目前为止，我设法生成了一个包含所有出现列表的列表:

Item.objects.values_list('data__color').distinct().annotate(num=Count('data_color'))

颜色:

["yellow", "blue", "red"], 1

["white", "blue", "gray"], 1

["yellow", "gray", "red"], 1

Answer 1

在 Django 中使用 ORM 对 JSON 字段执行如此复杂的查询没有很好的支持。完全使用 ORM 来做这件事并不是直截了当的。最好在这里使用原始 sql 查询。在SQL查询方面，可以使用

SELECT jsonb_array_elements(colors::jsonb) as color_count, count(*)
FROM (SELECT meta ->> 'color' as colors
      FROM core_dummymodel) AS tbl
GROUP BY color_count;

获取颜色计数和类似的尺寸查询。

SELECT jsonb_array_elements(sizes::jsonb) as sizes_count, count(*)
from (SELECT meta ->> 'size' as sizes
      FROM core_dummymodel) AS tbl
GROUP BY color_count;

这是颜色的结果: