Using Postgres::array_agg function to aggregate string columns

Jan 12, 2021 1 min leitura
  • Tags:
  • postgres
  • array
  • agg
  • function
  • sqlalchemy

Thanks to @copquevictor I learned a very handy Postgres aggregate function: array_agg. To understand it, suppose you have to notify stakeholders of reports and your query outputs the table below:

| Report    | Email                  |
| --------- | ---------------------- |
| Report #1 | johndoe@gmail.com      |
| Report #1 | jack@gmail.com         |
| Report #2 | contact@lisadesign.com |
| Report #3 | harry@hogwarts.com     |
| Report #3 | anderson@gmail.com     |

As you can see, we have a One-to-Many relationship: one report is of interest of one or more emails. You can notify the emails by (i) iterating over the rows (one by one) OR (ii) you can group the emails by the report. For the second case, wouldn’t it be nice to obtain the result below?

| Report    | Email                                    |
|-----------|------------------------------------------|
| Report #1 | [johndoe@gmail.com, jack@gmail.com]      |
| Report #2 | [contact@lisadesign.com]                 |
| Report #3 | [harry@hogwarts.com, anderson@gmail.com] |

Thanks array_agg that can be accomplished. For our fictional table, the query would be something like the query below:

SELECT reports.id, array_agg(stakeholders.email) FROM reports
INNER JOIN stakeholders ON reports.id = stakeholders.report_id
GROUP BY reports.id;

Pay attention that you must provide a GROUP BY clause in order to aggregate the emails.

from sqlalchemy import String
from sqlalchemy.sql.functions import array_agg
from sqlalchemy.dialects.postgresql import ARRAY

from models import Stakeholder, Report

def execute():
    emails_agg = array_agg(Stakeholder.email, type_=ARRAY(String)).label(
        "emails"
    )
    return (
        db.session.query(Report, emails_agg)
        .join(Stakeholder)
        .group_by(Report.id)
        .all()
    )

Pretty handy, right?

References:

* Créditos da imagem de destaque: Nam Anh

Comentários