-
Type:
Improvement
-
Resolution: Fixed
-
Priority:
Major
-
Affects Version/s: V4.0_CS02
-
Component/s: Data Aggregation
-
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": ... },
...
]
}