Grouping & Sorting Data in Boomi

On many occasions, one might need to group and sort data before sending it to its final destination. How can we perform that in an iPaaS like Boomi. That’s something that we will look at in this article where we dig deep into grouping & sorting Data in Boomi.

Assuming we have a set of records, similar to the one below where we are required to group the data by a field or more (order number and line number in this case), then sort the result set by some other field (amount ascending in this case). How do we do that?

First, let’s take a look on the source data, here’s a sample of how it could look like. Note that this could be of any format (Json, CSV, XML, etc.), it won’t matter. For our case we went with JSON:

[
  {
    "orderNumber": "PO123",
    "orderDate": "2023-05-18",
    "lineNumber": "0001",
    "amount": 2600
  },
  {
    "orderNumber": "PO124",
    "orderDate": "2023-05-19",
    "lineNumber": "0001",
    "amount": 2350
  },
  {
    "orderNumber": "PO123",
    "orderDate": "2023-05-18",
    "lineNumber": "0001",
    "amount": 900
  },
  {
    "orderNumber": "PO124",
    "orderDate": "2023-05-19",
    "lineNumber": "0001",
    "amount": 1500
  },
  {
    "orderNumber": "PO123",
    "orderDate": "2023-05-18",
    "lineNumber": "0002",
    "amount": 1800
  },
  {
    "orderNumber": "PO124",
    "orderDate": "2023-05-19",
    "lineNumber": "0002",
    "amount": 500
  },
  {
    "orderNumber": "PO123",
    "orderDate": "2023-05-18",
    "lineNumber": "0002",
    "amount": 3500
  },
  {
    "orderNumber": "PO124",
    "orderDate": "2023-05-19",
    "lineNumber": "0002",
    "amount": 2200
  }
]

After grouping by order number and line number, then sorting by amount, this is how it should look like:

[
  {
    "orderNumber": "PO124",
    "orderDate": "2023-05-19",
    "lineNumber": "0002",
    "amount": 2700
  },
  {
    "orderNumber": "PO123",
    "orderDate": "2023-05-18",
    "lineNumber": "0001",
    "amount": 3500
  },
  {
    "orderNumber": "PO124",
    "orderDate": "2023-05-19",
    "lineNumber": "0001",
    "amount": 3850
  },
  {
    "orderNumber": "PO123",
    "orderDate": "2023-05-18",
    "lineNumber": "0002",
    "amount": 5300
  }
]

Let’s break our requirement into two separate tasks and explain how to do each of them in isolation.

Grouping Data in Boomi

Step 1: Create a unique identifier for the record

The first step we need to do is to create a unique identifier for each record containing the fields you want to group by. This will allow you to later sum the amounts by this field. To do that, we created a map where we used the Concat function to concatenate our “group by” fields into a single field as follows:

Note that we had to create a new profile with the identifier field to allow us to store this concatenated field

Step 2: Sum the Amount field

The second step in this process is to Sum the amounts by this newly created identifier field. To do that we created another map component and used the Sum function to sum the values of the amount field.

Note how we used the Reset value of the Sum function to be the identifier field. This will ensure the values are summed up by this identifier only.

Step 3: Remove Duplicates

The third step in this process is to remove the duplicate records that resulted from the previous step. Remember we had an X number of records initially (8 in this case), and after the summation, we still have the same number, but with identical identifier value and amount. So we need to remove the duplicate records. In order to do that, we needed to transform the Json payload into a Flat File payload and use the “Enforce Unique” property in the identifier field. We had to do that because such a property doesn’t exist in the JSON profile. So we created an identical Flat File profile, then we did set this property on the identifier field as shown below

After that, we created a map component just to transform from our JSON format to the Flat File format

Step 4: Revert back to JSON Format

Last step in the grouping task is just to revert back to the JSON format and drop the additional identifier field we created for the sake of grouping. To do that, we had to create one final map component that transforms from the Flat File profile to the Json profile

Sorting Data in Boomi

Now that we have grouped our data, we want to sort the amount field in ascending order. Unluckily, there is no straight-forward way to do that in Boomi, so we had to write some code which does the trick. The good news is that this snippet is generic and can be used for any Json payload. So, we added a “Data Process” shape into our canvas and used the below script in the custom scripting definition:

import java.util.Properties; 
import java.io.InputStream; 
import groovy.json.JsonSlurper; 
import groovy.json.JsonOutput 
for( int i = 0; i < dataContext.getDataCount(); i++ ) { 
  InputStream is = dataContext.getStream(i); 
  Properties props = dataContext.getProperties(i); 
  def payload = is.getText("UTF8"); 
  def json = new JsonSlurper().parseText(payload); 
  def sortedJSON = json.sort { a,b -> a.amount <=> b.amount} 
  def json_str = JsonOutput.toJson(sortedJSON); 
  dataContext.storeStream(new ByteArrayInputStream(json_str.getBytes()), props); 
}

Note that if you wanted to sort the other way around (descending order), all you need to do is flip the a.amount and b.amount.

Test the Process

The final version of our process should look like the below:

Let’s hit the “Test” button in order to test our process. First note the original payload:

You can view the data before being sorted

And then the final sorted data in the last step of the process

Conclusion

In this article we have covered the technique to group and sort data through the Boomi iPaaS platform. As you may have noticed, it requires a bit of steps to do that, but it’s certainly doable without much effort. You can revert back to this technique to group and sort data of any format and type, whether it is Json, CSV, or XML type, it doesn’t matter. Same technique can be applied. Happy learning!