hey is there a way to create an extension function...

# exposed

[Karl Azzam]

hey is there a way to create an extension function that deals with array aggregate for multiple columns?

i.e. for one column array aggregate this works:

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

it would be the equivalent of doing this

SELECT
    u.id,
    ARRAY_AGG(r.role_id) AS role_id,
FROM
    user_table u
LEFT JOIN
    user_role_association_table r ON u.id = r.user_id
GROUP BY
    u.id

via this in exposed:

transaction {
    val rolePathAgg = ArrayAggFunction(UserRoleAssociationTable.roleId, UUIDColumnType())
    (UserTable leftJoin leftJoin UserRoleAssociationTable)
        .select(
            UserTable.id,
            rolePathAgg,
        )
        .groupBy(UserTable.id)
        .toList()
        .map { .... }
}

but for two column array aggregate such as i want role id and role created at, how can i create an extension
fun that can support this?

SELECT
    u.id,
    ARRAY_AGG(ROW(r.role_id, r.created_at)) AS roles_with_created_at
FROM
    user_table u
LEFT JOIN
    user_role_association_table r ON u.id = r.user_id
GROUP BY
    u.id

[Karl Azzam]

i notice there's a CompositeColumn but its implementing an expression not a column type

[Chantal Loncle]

Hi @Karl Azzam. When customizing a function, the output result of the SQL function needs to be considered to find a match from the existing column types supported by Exposed.

I'm not familiar with the ROW() function, but based on the output of a single column ARRAY_AGG(), I'm assuming that the multiple column variant results in something like [[?, ?], [?, ?], ...]. If that's correct, then the best match would be something like a multi-dimensional array, which the current ArrayColumnType does not support. Here is the feature request for that, if you're interested in tracking and upvoting.

The example you mentioned above would result in arrays of multiple types though, correct? Does PostgreSQL actually support multi-type multi-dimensional arrays?

Based on a quick search for aggregating multiple columns with PostgreSQL, I found 2 alternative approaches that would be supported by Exposed, if you felt inclined: concatenation and json. I've added both implementations below for reference.

Using the basic table below:

object Users : Table("users") {
    val id = integer("id")
    val name = varchar("name", 50)
    val age = integer("age")
}

Concatenation:

class ConcatArrayAggFunction(
    vararg expression: ExpressionWithColumnType<*>
)  : CustomFunction<List<String>>(
    functionName = "ARRAY_AGG",
    columnType = ArrayColumnType(TextColumnType()),
    expr = arrayOf(Concat(" - ", *expression))
)

val dataAgg = ConcatArrayAggFunction(Users.id, Users.name, Users.age)
Users
    .select(dataAgg)
    .forEach {
        println(it[dataAgg])
        // [1 - User A - 11, 2 - User B - 22, 3 - User C - 33]
    }

JSON:
The following example uses kotlinx.serialization and would be simplified if the nested array was of a single type (by using JSON_BUILD_ARRAY instead with the columns directly and specifying serializer<Array<Array<?>>>() with the single type as ?).

class JsonArrayAggFunction<T : Any>(
    serialize: (T) -> String,
    deserialize: (String) -> T,
    vararg val expression: Pair<String, ExpressionWithColumnType<*>>
) : Function<T>(JsonColumnType(serialize, deserialize)) {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) {
        queryBuilder {
            append("JSON_AGG(JSON_BUILD_OBJECT(")
            expression.appendTo { (key, value) ->
                +stringLiteral(key)
                +", "
                +value
            }
            append("))")
        }
    }
}

@Serializable
data class MyData(val id: Int, val name: String, val age: Int)

val dataAgg = JsonArrayAggFunction(
    { Json.Default.encodeToString(serializer<List<MyData>>(), it) },
    { Json.Default.decodeFromString(serializer<List<MyData>>(), it) },
    "id" to Users.id,
    "name" to Users.name,
    "age" to Users.age

)
Users
    .select(dataAgg)
    .forEach {
        println(it[dataAgg])
        // [MyData(id=1, name=User A, age=11), MyData(id=2, name=User B, age=22), MyData(id=3, name=User C, age=33)]
    }

[Karl Azzam]

thanks!