Create a Postgres array ordered by some criteria

[cpinamtz]

How can I create a Postgres array ordered by some criteria? Sort like:

SELECT
    ARRAY_AGG(date ORDER BY date DESC) "dates"
FROM
    ...

I know ARRAY_AGG is not supported in Pypika yet but it's not a problem creating a custom function. However I don't know if it's possible to achieve this with the existent pieces.

[ajustintrue]

I would just create my own version of array_agg as a pypika aggregate function. Something like this should work. Overriding the get_function_sql method lets you put anything you want in the function's sql. Hope this helps.

class ArrayAgg(AggregateFunction):
    def __init__(self, term, order_by_term=None, distinct=False, alias=None):
        self.agg_field = term.get_sql()
        self.order_by = f'ORDER BY {order_by_term.get_sql()}' if order_by_term else ' '
        self.distinct = ' DISTINCT ' if self.distinct else ' '
        super(ArrayAgg, self).__init__('ARRAY_AGG')

    def get_function_sql(self, **kwargs):
        return f"ARRAY_AGG({self.distinct} {self.agg_field} {self.order_by})"

[wolph]

Alternatively here's a version that (ab)uses the AnalyticFunction which already has ORDER BY support:

class ArrayAgg(AnalyticFunction):

    def __init__(self, *terms, **kwargs):
        super().__init__('ARRAY_AGG', *terms, **kwargs)

    def get_special_params_sql(self, **kwargs: typing.Any) -> typing.Any:
        return super().get_partition_sql(**kwargs)

    def get_function_sql(self, **kwargs):
        # We're abusing the OVER(...) syntax to add the ORDER BY clause.
        self._include_over = False
        return super().get_function_sql(**kwargs)

You can use it as: ArrayAgg(column).orderby(other_column)

[conryf]

Adding mine:

class ArrayAgg(DistinctOptionFunction):
    def __init__(self, term, orderby = None, alias=None):
        super(ArrayAgg, self).__init__('ARRAY_AGG', term, alias=alias)
        self.orderby = orderby
    def get_special_params_sql(self, **kwargs):
        return 'ORDER BY {orderby}'.format(
              orderby=self.orderby,
        ) if self.orderby \
        else None