Use GroupBy function
GroupBy
is a function that accepts a list of objects as input and generates a grouped list as output. You can use this function for data transformation, nested loops, and aggregation.
This function can act as a powerful tool to implement the use cases of grouping the total or subtotal. For example, you can use this function to group invoice items with the same charge name together and show the subtotal amount of each charge.
Data transformation
Assume that you have a list-type input Items as follows:
[
{ "A": "a1", "B": "b1", "C": "c1", "D": "d1" },
{ "A": "a1", "B": "b1", "C": "c1", "D": "d2" },
{ "A": "a1", "B": "b2", "C": "c1", "D": "d1" },
{ "A": "a2" }
]
The value object has four named properties: A
, B
, C
, and D
. You can use the Items|GroupBy(A,B,C)
function to return the following output in the rendering result:
[
{
"A": "a1", ①
"_Group": [ ②
{
"B": "b1",
"_Group": [ ③
{
"C": "c1",
"_Group": [
{ "A": "a1", "B": "b1", "C": "c1", "D": "d1" }, ④
{ "A": "a1", "B": "b1", "C": "c1", "D": "d2" }
]
}
]
},
{
"B": "b2",
"_Group": [
{
"C": "c1",
"_Group": [
{ "A": "a1", "B": "b2", "C": "c1", "D": "d1" }
]
}
]
}
]
},
{
"A": "a2",
"_Group": []
}
]
Read the following information to understand how data transformation works in the preceding output:
- ①: Instead of using the value of the
groupBy
property as the key name, like{"a1": [ { "b1": [ { "c1": [.. ] } ] } ], "a2": []}
, the example keeps the original key name, because the example needs to refer to this key value by property name. - ②:
_Group
is a reserved keyword, and it is the hard-coded property name of the grouped list. - ③: Multiple
_Group
properties might exist at different levels, so it is good practice to give each property a meaningful name by using theCmd_Assign
command. - ④: At the deepest level of grouped items, the object schema is the same as the original in the input list.
Nested loops
With the aforementioned information in mind, you can use the following merge field template for nested loops:
{{#Items|GroupBy(A,B,C)}}
{{Cmd_Assign(ItemsWithSameA,_Group)}}
{{#ItemsWithSameA}}
{{Cmd_Assign(ItemsWithSameB,_Group)}}
{{#ItemsWithSameB}}
{{Cmd_Assign(ItemsWithSameC,_Group)}}
{{#ItemsWithSameC}}
{{A}} - {{B}}
{{/ItemsWithSameC}}
{{/ItemsWithSameB}}
{{/ItemsWithSameA}}
{{/Items|GroupBy(A,B,C)}}
If you are not familiar with the Mustache template yet, see the following pseudo-code that explains what it does.
GroupedItems = Items.GroupBy(A, B, C) for {A,_Group} in GroupedItems {
// {A,_Group} is a deconstructed objectItemsWithSameA = _Group for {B,_Group} in ItemsWithSameA {
// B and _Group are key names.ItemsWithSameB = _Group for {C,_Group} in ItemsWithSameB { ItemsWithSameC = _Group for item in ItemsWithSameC { // item is in shape of the original.
print(item.A) print(" - ") print(item.B) } } } }
The following table lists the line-by-line comparison between the Mustache merge field example and its corresponding pseudo-code to help you understand how the nested loops work.
Mustache template |
Pseudo-code |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
} |
Aggregation
If you want to aggregate some numeric fields for a certain group, you have to be aware of the data structure and the variable scope.
For example, if you want to aggregate field E for all items with the same B property, use the following example merge fields containing the GroupBy
function:
{{#Items|GroupBy(A,B,C)}}
{{Cmd_Assign(ItemsWithSameA,_Group)}}
{{#ItemsWithSameA}}
{{Cmd_Assign(ItemsWithSameB,_Group)}}
{{#ItemsWithSameB}}
{{Cmd_Assign(ItemsWithSameC,_Group)}}
{{#ItemsWithSameC}}
{{A}} - {{B}}
{{/ItemsWithSameC}}
{{/ItemsWithSameB}}
{{ItemsWithSameB|FlatMap(_Group)|Sum(E)}} ①
{{/ItemsWithSameA}}
{{/Items|GroupBy(A,B,C)}}
In the line marked with ①:
- ItemsWithSameB is a variable defined in the context of ItemsWithSameA, so you can only refer to the former within the section of ItemsWithSameA.
- The object schema of items in ItemsWithSameB is
{ "C": "..", "_Group": [] }
, so you cannot directly conduct aggregation by using{{ItemsWithSameB|Sum(E)}}
. You have to flatten the_Group
list first. For more information, see FlatMap function for its usage.
Limitations and restrictions
The GroupBy
function supported in HTML invoice templates has the following limitations and restrictions:
- Currently, the
GroupBy
function can support at most six arguments. - If the value of a
GroupBy
property is null, it is treated as blank. - The argument property name can be dotted data path, for example,
InvoiceItems|GroupBy(RatePlanCharge.ChargeType)
.