Support augmenting entities with aggregated values from related entities

    • Type: Improvement
    • Resolution: Fixed
    • Priority: Major
    • V4.0_CSD04
    • 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": ... },
          ...
        ]
      }
      

            Assignee:
            Unassigned
            Reporter:
            gerald.krause1
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated:
              Resolved: