Array aggregation

[MaxEtMoritz]

Summary

It would be great if there is an aggregation type array that groups all values into an array column.

The current aggregation types mostly make sense for numerical data only (except count) but are pretty useless for e.g. strings and will fail for generic object columns.

Basic example

e.g. in Demo Database:

Wanting to join Customers with Orders while aggregating the order ID for each customer to an array.

(in that example one would probably want to go on joining Order with OrderDetails with Products and e.g. get a list of product names each customer has ordered, but that would be a bit too many joins for a simple example...)

even though the data is numerical in this case, summing up IDs etc. is also pretty useless.

see IDBStudio

Motivation

I have a one-to-many relationship between two tables and would like to join them, aggregating a column of string values to a string array.

[ujjwalguptaofficial]

So you want orderIds value in an array right ?

[ujjwalguptaofficial]

added in v- 4.5.0 @MaxEtMoritz . Thanks for the well explanation - I think this is going to be an amazing feature although it is small.

I have added list aggregator option. Here is query example -

select({
    "from": "Customers",
    join: {
        with: 'Orders',
        on: 'Customers.customerId=Orders.customerId'
    },
    groupBy: 'customerId',
    aggregate: {
        list: 'orderId'
    }
});

Please check and confirm if its working well.

[MaxEtMoritz]

Thanks for adding it, works good in the demo database.

wanted to test it out on my use case, but since the tables i planned to join are quite large i wanted to join with a limit to get a faster result. but what happened was that the join seems to not respect the set limit, so used up all the RAM and i got no result at all.

but i will open a different issue for this.