Example of basic method of aggregation of array elements on PostgreSQL

Preface

• This article applies to PostgreSQL
• The way to achieve it is to useunnestWill

Table jounal structure example

id      author
 2    [Zhang San, Li Si, Wang Wu]
 2    [Zhang San, Li Si]
 3    [Zhang San]

Aggregation results

item  cnt
Zhang San    3
Li Si    2
Wang Wu    1

sql statement

sql statement writing method one

select item, count(item) as cnt
from (select unnest() as item from journal) as tmp
group by item

sql statement writing method two

select item, count(distinct id) as cnt
from journal, unnest() as item
group by item

sql statement writing method three

select item, count(item) as cnt
from journal, unnest() as item 
group by item

Related links

• How to group result by array column in Postgres?
• Group by unique items in an array column and count records that contain each item

The above is the detailed content of the basic method of PostgreSQL aggregation of array elements. For more information about PostgreSQL array elements aggregation, please pay attention to my other related articles!