Aggregating List elements with the Same values in Alumio

dawid.kazibut · June 28, 2024, 3:11pm

Hello,

I have an order JSON with two order lines that contain the same SKU. I need to aggregate the lines with the same SKU. Could you please advise how to do it?

Input:
{
“Lines”: [
{
“Id”: 79,
“Quantity”: 1,
“MerchantProductNo”: “5895”
},
{
“Id”: 80,
“Quantity”: 1,
“MerchantProductNo”: “5895”
},
{
“Id”: 80,
“Quantity”: 1,
“MerchantProductNo”: “5666”
}
]
}

Expected Output:
{
“Lines”: [
{
“Id”: 79,
“Quantity”: 2,
“MerchantProductNo”: “5895”
},
{
“Id”: 81,
“Quantity”: 1,
“MerchantProductNo”: “5666”
}
]
}

Gugi · July 1, 2024, 12:27pm

Hi @dawid.kazibut

Welcome to the Alumio forum.

You can group objects with the same criteria, such as the same MerchantProductNo, by using the Group Records transformer.

Result:

{
  "Lines": {
    "5666": [
      {
        "Id": 80,
        "Quantity": 1,
        "MerchantProductNo": "5666"
      }
    ],
    "5895": [
      {
        "Id": 79,
        "Quantity": 1,
        "MerchantProductNo": "5895"
      },
      {
        "Id": 80,
        "Quantity": 1,
        "MerchantProductNo": "5895"
      }
    ]
  }
}

Then, you can loop through the Lines array to get the aggregate of the Quantity value of the grouped records. We suggest to use JMESPath sum function to do so.

Values of the Value setter.

{
  "Id": "&{[].Id | [0]}",
  "Quantity": "&{[].Quantity | sum(@)}",
  "MerchantProductNo": "&{[].MerchantProductNo | [0]}"
}

Result:

{
  "Lines": {
    "5666": {
      "0": {
        "Id": 80,
        "Quantity": 1,
        "MerchantProductNo": "5666"
      },
      "result": {
        "Id": 80,
        "Quantity": 1,
        "MerchantProductNo": "5666"
      }
    },
    "5895": {
      "0": {
        "Id": 79,
        "Quantity": 1,
        "MerchantProductNo": "5895"
      },
      "1": {
        "Id": 80,
        "Quantity": 1,
        "MerchantProductNo": "5895"
      },
      "result": {
        "Id": 79,
        "Quantity": 2,
        "MerchantProductNo": "5895"
      }
    }
  }
}

Then, we should remove the non-aggregated objects, as you will only need the result property.

Result:

{
  "Lines": {
    "5666": {
      "Id": 80,
      "Quantity": 1,
      "MerchantProductNo": "5666"
    },
    "5895": {
      "Id": 79,
      "Quantity": 2,
      "MerchantProductNo": "5895"
    }
  }
}

The Lines array is now an object (associated array) whose keys are strings. We can convert it to an indexed array using a mapper called “List: Get values”.

Result:

{
  "Lines": [
    {
      "Id": 79,
      "Quantity": 2,
      "MerchantProductNo": "5895"
    },
    {
      "Id": 80,
      "Quantity": 1,
      "MerchantProductNo": "5666"
    }
  ]
}