Showing results for 
Search instead for 
Did you mean: 

Help Using Group By!

New Contributor

Dear Community

I need your help with understanding the group by in the attached simple pipeline.

I am supplying the Json data and sorting and grouping it by ID as in the picture but for each Group, i.e. ID. Then I need to first determine if a type of address exists e.g. “BUS” business or “RES” residential and then transform, e.g. via a mapper, to output both the business and residential addresses to Mapper variables $Business and $Residential output as one document/record

Thank you very much for your help in advance,
Andre M

JsonSAMPLE groupBy_Sample


New Contributor

This is the groupby. I would like to know what I need to add in the mapper or router snaps please.


Former Employee

The GroupBy snap will collect input documents into arrays inside of a single output document. Once that is done, you’ll need to use the expression language to manipulate the arrays, so placing a Router after the GroupBy is probably not going to do what you want. If you want to use snaps to do the manipulation, you’ll need to move the Router to the head of the pipeline. I’m going to attach an example pipeline that shows both approaches: using the expression language and using the Router.


There’s a few different ways to transform the addresses in the expression language in a Mapper snap. I’ll mention a couple here:

You can use the match operator with a pattern for each type of address. For example, to extract the “BUS” address and assign it to the variable Value you would use the following and map it to the $Business property:

match $group {
    [..., { Address: "BUS", Value }, ...] => Value

To handle the residential address, you’d make a new mapping and copy the above expression and replace “BUS” with “RES”. Note that if there was no address of a particular type, the match will return null.

Another approach would be to transform the grouped address array into an array of key/value pairs and then construct a new object using the Object.extend() method. The method can be used to transform each element in the array. In this case, we’re looking to create a pair with the desired property name mappings and the value. Here’s the full expression:

{}.extend($ =>
      match elem.Address {
         "BUS" => "Business",
         "RES" => "Residential"

Note that in this approach, if an address type is missing, the property will not show up in the output document.

Snaps (and expressions)

If you want to use a Router to handle the different types of addresses, you’ll need to put that before the Sort and then use a Union to bring all the documents back into the same stream. Then, after the GroupBy, you’ll need to use the Object.extend() method again to merge the elements of the $group array into a single object.


Note that you need to use the spread operator (…) to pass the elements of the array as the arguments to the function. Otherwise, the array will be treated as the value to be merged into the object with the element indexes used as the object properties.

GroupByExample_2018_11_15.slp (22.3 KB)

Hi tStack

Thank you very much my friend. Really appreciate the feedback.

Before you posted this i used both the router and mapper as follows. Do you foresee any issues with this?


Thank you

Each of the two router mappers has the config below,