Skip to Content

In this blog, I would like to share another new functionality planned to release in Service Layer 9.2 H Patch 03. It is about the oData grouping, which is an important extension to the original oData protocol.

Grouping behavior is triggered using the query option apply and the groupby keyword. This keyword specifies the grouping properties, a comma-separated list of one or more single-valued property paths that is enclosed in parentheses. The same property path SHOULD NOT appear more than once; redundant property paths MAY be considered valid, but MUST NOT alter the meaning of the request.

1 Simple group

Simply enclose the group properties with parentheses. For example, to group the orders by CardCode, DocEntry, send a request like:

GET /b1s/v1/Orders?$apply=groupby((CardCode, DocEntry)) 

Or

/b1s/v1/Orders?$apply=groupby((Orders/CardCode, Orders/DocEntry)) 

On success, the response is like:

{

  "odata.metadata": "$metadata#Orders(CardCode, DocEntry)",

  "value": [

    {

      "odata.id": null,

      "CardCode": "c001",

      "DocEntry": 2

    },

    {

      "odata.id": null,

      "CardCode": "c002",

      "DocEntry": 3

    },

    {

      "odata.id": null,

      "CardCode": "c001",

      "DocEntry": 5

    },

    {

      "odata.id": null,

      "CardCode": "c001",

      "DocEntry": 6

    }

  ]

}

The equivalent SQL on HANA is:

SELECT T0."CardCode", T0."DocEntry" FROM "ORDR" T0 GROUP BY T0."CardCode", T0."DocEntry" 
2 Group with aggregation method

SL also supports to combine grouping with aggregation. For example, to aggregate the DocNum property on grouping CardCode, send a request like:

GET /b1s/v1/Orders?$apply=groupby((CardCode), aggregate(DocNum with sum as TotalDocNum)) 

On success, the response is like:

{

  "odata.metadata": "$metadata#Orders(CardCode,TotalDocNum)",

  "value": [

    {

      "odata.id": null,

      "CardCode": "c001",

      "TotalDocNum": 8

    },

    {

      "odata.id": null,

      "CardCode": "c002",

      "TotalDocNum": 2

    }

  ]

}

The equivalent SQL on HANA is:

SELECT T0."CardCode", SUM(T0."DocNum") AS "TotalDocNum" FROM "ORDR" T0  GROUP BY T0."CardCode" 
3 Group with aggregation method and filter

SL allows to filter before grouping. These two operations are separated by a forward slash to express that they are consecutively applied. For example, to filter before grouping with aggregation method, send a request like:

GET /b1s/v1/Orders?$apply=filter(Orders/CardCode eq 'c001')/groupby((CardCode), aggregate(DocNum with sum as TotalDocNum)) 

On success, the response is like:

{

  "odata.metadata": "$metadata#Orders(CardCode,TotalDocNum)",

  "value": [

    {

      "odata.id": null,

      "CardCode": "c001",

      "TotalDocNum": 8

    }

  ]

}

The equivalent SQL on HANA is:

SELECT T0."CardCode", SUM(T0."DocNum") AS "TotalDocNum" FROM "ORDR" T0  WHERE T0."CardCode" = 'c001' GROUP BY T0."CardCode" 

[Note] The filter option can also be specified as below, which is functionally equivalent.

GET /b1s/v1/Orders?$apply=groupby((CardCode), aggregate(DocNum with sum as TotalDocNum))&$filter=(Orders/CardCode ne 'c001')

As you know, Hana has excellent performance on the aggregation and grouping operation. Being able to expose this via Service Layer provides a convenient way for customers to better leverage this advantage of Hana.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply