[Django]-Counting distinct elements in Django ArrayField

by

7👍

✅

This query worked for me:

select unnest(tags) as tag, count(tags) from model group by tag;

I think this ORM query gives the same result:

from django.db.models import Func, F, Count

Model.objects.annotate(tag=Func(F('tags'), function='unnest')).values('tag').order_by('tag').annotate(count=Count('id')).values_list('tag', 'count')

I have created the following table and populated with sample values:

create table model(
    id serial primary key,
    name text,
    tags text[]
);

insert into model(name, tags) values ('a', array['a', 'b', 'c']);
insert into model(name, tags) values ('b', array['b', 'b', 'c']);

select unnest(tags) as tag, count(tags) from model group by tag;

+---+-----+
|tag|count|
+---+-----+
|c  |2    |
|a  |1    |
|b  |3    |
+---+-----+
👤maksadbek

Leave a comment