Is there an equivalent SQL array_agg grouping function in expression engine


#1

Hi,
In SQL we can group on a column while aggregate in an array another column (like other functions: max, count, avg …). Trying the same exact with evaluate function in the projection key, I get an error which is not exactly referring to the array_agg function, and instead pointing to the column grouped by : Can't group on a projection that isn't handled by the db engine: 'servicePoint'".

my query:
var R = RegisterMeasurementSeries.evaluate({ projection: "servicePoint, array_agg(latest)", group: "servicePoint", filter: "(measurementCategory=='DailyConsumption') ", limit: 100 });

Instead, I would write a JS code to do so. Map on unique ServicePoint IDs and stack different latests. Always feasible

Thank’s in advance.


#2

This is may help Projection with sum