Skip to main content

Use GroupBy function

Zuora

Use GroupBy function

  1. Last updated
  2. Save as PDF

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 the Cmd_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 object
  ItemsWithSameA = _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

{{#Items|GroupBy(A,B,C)}}

GroupedItems = Items.GroupBy(A, B, C)

for {A,_Group} in GroupedItems {

  {{Cmd_Assign(ItemsWithSameA,_Group)}}

  ItemsWithSameA = _Group

  {{#ItemsWithSameA}}

  for {B,_Group} in ItemsWithSameA {

    {{Cmd_Assign(ItemsWithSameB,_Group)}}

    ItemsWithSameB = _Group

    {{#ItemsWithSameB}}

    for {C,_Group} in ItemsWithSameB {

      {Cmd_Assign(ItemsWithSameC,_Group)}}

      ItemsWithSameC = _Group

      {{#ItemsWithSameC}}

      for item in ItemsWithSameC {

        {{A}} - {{B}} 

        print(item.A)

        print(" - ")
        print(item.B)

      {{/ItemsWithSameC}}

      }

    {{/ItemsWithSameB}}

    }

  {{/ItemsWithSameA}}

  }

{{/Items|GroupBy(A,B,C)}}

}

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).