Array integer[]: how to get all distinct values in a table and count them?

I'm not so good with SQL (PostgreSQL). Here is what I want to do:

I have a table, fields:

id SERIAL
inet INET
ports integer[]

 id |    inet    | ports 
----+------------+------------
  2 | 1.2.2.1    | {80}
  1 | 1.2.3.4    | {80,12}
  ...

How can I

• get all used "ports" values in this table: 80, 12

• count how many inet addresses are on specific port:

Like this:

port  | count
--------+------------
 12     | 1
 80     | 2
  ...

If anyone is looking for a Django version of it:

class Unnest(Func):
    function = 'UNNEST'

Model.objects \
.annotate(port=Unnest('ports', distinct=True)) \
.values('port') \
.annotate(count=Count('port')) \
.order_by('-count', '-port')

You can use UNNEST.

select unnest(ports) as port, count(*) from foo group by port;

Using more than one UNNEST in the same query (or the same select list, anyway) is confusing and is probably best avoided.

StackExchange Database Administrators Q#126412, answer score: 14