How to add elements of an array, based on a condition, in azure logic apps?

Question

I would like to add up the values of an array, based on a condition.

{ "name": "alfons", "value": 100, "condValue": "D90" }, 
{ "name": "alfons", "value": 300, "condValue": "D10" }, 
{ "name": "alfons", "value": 200, "condValue": "D90" }, 
{ "name": "max", "value": 600, "condValue": "D90" }, 
{ "name": "max", "value":100, "condValue": "D90" }

In relation to this example array I want to add the values if the conditional value and the name of an object match. In this example my ideal output would be:

{ "name": "alfons", "value": 300, "condValue": "D90" }, 
{ "name": "alfons", "value": 300, "condValue": "D10" }, 
{ "name": "max", "value": 700, "condValue": "D90" }

What I did so far: I'm filtering the payload array (the real data I'm fetching is an array with about 700 elements) in a for each loop for the individual names. This array I filter again for the conditional Value in another for each loop (nested in the first one).

Now my question is how do I add the values up and compose a new array so it doesn't blow up in my face since I'm looping three times here.

My idea was to compose a json object everytime the loop finished and append it to an array variable, but i would again end up with tons of duplicates.

"Execute JavaScript Code" unfortunately isn't an option.

Solution:

I couldn't test the proposed solution with the Advanced Data Operations connector but it appears very sound.

I resolved the task with logic-app base functionality like this:

logic app flow

I start by looping through the names and filtering the payload for the current name.

Then I loop through each object in the payload array, writing the condValue of the current object into a variable condValueContainer and reset a sum variable to 0. I open up a third loop, through the payload array again. Here I check the condValue of the current array object with the condValueContainer. If true, I increment the sum variable by the value of the current object.

The result is:

{ "name": "alfons", "value": 300, "condValue": "D90" },
{ "name": "alfons", "value": 300, "condValue": "D10" },
{ "name": "alfons", "value": 300, "condValue": "D90" },
{ "name": "max", "value": 700, "condValue": "D90" },
{ "name": "max", "value": 700, "condValue": "D90" },

So I just have to get rid of the duplicates. I empty the payload array. Then I loop through the payloadSorted array. Whithin I check whether payload does not contain the current object of payloadSorted. If that's the case I append it to payload.

I end up with this result, as I needed it:

{ "name": "alfons", "value": 300, "condValue": "D90" },
{ "name": "alfons", "value": 300, "condValue": "D10" },
{ "name": "max", "value": 700, "condValue": "D90" },

This whole construct may be inefficient, especially when running somewhat bigger arrays. So I will probably come back to it to slim it down. For now I'm happy it's working. If you have any suggestions on how to make this more efficient with logic-app base functionality please let me know.

score 0 · Answer 1 · answered Aug 08 '23 at 11:32

Yep, use the Aggregate operation in the Advanced Data Operations connector.

https://learn.microsoft.com/en-us/connectors/advanceddataoperatio/

https://www.statesolutions.com.au/aggregate/

https://www.statesolutions.com.au/pricing/

Note: With the below, the Data variable is your data (as an array) that you provided.

Result

[
  {
    "name": "alfons",
    "condValue": "D10",
    "value": 300
  },
  {
    "name": "alfons",
    "condValue": "D90",
    "value": 300
  },
  {
    "name": "max",
    "condValue": "D90",
    "value": 700
  }
]

this looks pretty straight forward, thanks for the suggestion! I ended up solving the task with logic app base functionality. I'll update the post shortly. — PorcoRosso, Aug 09 '23 at 06:20

How to add elements of an array, based on a condition, in azure logic apps?

1 Answers1

Result