it seems exposed doesn't support array aggregate f...

# exposed

[Karl Azzam]

it seems exposed doesn't support array aggregate function in postgres?

I have a 1 to many relationship between a user_table and user_attributes_table

SELECT
    u.id AS user_id,
    ARRAY_AGG(ua.entity_path) AS paths
FROM
    foo_schema.user_table u
        JOIN
    foo_schema.user_attributes_table ua ON u.id = ua.user_id
GROUP BY
    u.id

I instead am doing this where i get multiple rows per user for each user's entity path.

SELECT
    u.id AS user_id,
    ua.entity_path AS path
FROM
    foo_schema.user_table u
        JOIN
    foo_schema.user_attributes_table ua ON u.id = ua.user_id
GROUP BY
    u.id, ua.entity_path

[Chantal Loncle]

Hi @Karl Azzam That's correct, ARRAY_AGG would need to be implemented using a custom function, something like this:

class ArrayAggFunction<T>(
    expression: ExpressionWithColumnType<T>,
    delegate: ColumnType
) : CustomFunction<List<T>>(
    functionName = "ARRAY_AGG",
    columnType = ArrayColumnType(delegate),
    expr = arrayOf(expression)
)

The argument passed to delegate should be the matching ColumnType used for the ua.entity_path column. An instance of this function could then be provided to select() in the join query:

// for example, if entity_path stored strings using varchar()
val pathAgg = ArrayAggFunction(UserAttributeTable.entityPath, VarCharColumnType())
val join = // ...
join
    .select(UserTable.id, pathAgg)
    .groupBy(UserTable.id)
    .map { }

[Karl Azzam]

awesome thanks again!