-
Type: Improvement
-
Status: Closed
-
Priority: Major
-
Resolution: Fixed
-
Affects Version/s: V4.0_CS02
-
Fix Version/s: V4.0_CSD04
-
Component/s: Data Aggregation
-
Labels:None
For a collection of entities having navigation paths to related entities, adding an aggregated value from the related entities to each entity in the collection today requires a first argument to groupby listing all properties. For example, to retrieve all products together with their total sales amount:
GET ~/Products?$apply=groupby((<key property>,<all other properties of type Product...>),aggregate(Sales/Amount with sum as TotalAmount))
returns all entities from the Products entity collection whose structure now also includes a dynamic property TotalAmount. If the underlying type comprises many properties, this approach becomes rather clumsy.
Options to describe this request in a leaner way could be:
A) Do the aggregation inside an expand transformation with a nested compute argument that operates on the entities of the navigation target. The determined result is then added to the result as expanded navigation property:
GET ~/Products?$apply=expand(Sales,compute(aggregate(Amount with sum) as TotalAmount))&$select=*,Sales/TotalAmount
returns
{ "@odata.context": "$metadata#Products", "value": [ { "ID": ..., "Name": ...", ... "Sales": [{ "TotalAmount": ... }] }, ... ] }
(this option requires an extension of expand to allow applying compute to related entities)
B) Allow $it as first argument to groupby meaning that each entity of the input set becomes a separate subset on its own:
GET ~/Products?$appy=groupby($it,aggregate(Sales/Amount with sum as TotalAmount))
returns
{ "@odata.context": "$metadata#Products", "value": [ { "ID": ..., "Name": ...", ... "TotalAmount": ... }, ... ] }