CODEWITHSUNDEEP
sqldjangopostgresql
embee
embee

Reputation: 13

Django JSONField, how can i get the count of occurences for values stored in a list

Database is postgres 9.4 In my table i'm using a JSONField 

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

Example data JSONField of 3 records:

{"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"}

My goal is to create a list which contains the count of each occuring color and size:

color: yellow 2 blue 2 red 2 gray 2 white 1

size: L 3 XL 2 S 1

Is this possible ? What would be the best solution in terms of performance.

So far i managed te produce a list containing all occuring lists with:

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

color: 

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

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

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

Upvotes: 1

Views: 727

Answers (1)

Atul Mishra
Atul Mishra

Reputation: 2018

There isn't very good support to perform such complex queries on JSON fields using ORM in Django. Doing this completely using ORM will not be straight forward. It's better to use raw sql query here. In terms of SQL query, you can use 

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;

to get the colors count and a similar query for sizes. 

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;

Here is the outcome for colors:

enter image description here

Upvotes: 2

Related Questions