Appended Variable String to JSON transformation

[Markus44]

Hello Community,

I do have an issue with the following situation.

I have an Apply to Each which is appending a string variable.

After the Apply to Each, I have a Compose that outputs the final string variable.

This is what the output looks like:

[{\"Article\":\"...\",\"Project\":\"...\",\"Type\":\"...\",\"Task\":\"...\",\"Name\":\"...\"},{\"Article\":\"...\",\"Project\":\"...\",\"Type\":\"...\",\"Task\":\"...\",\"Name\":\"...\"}],[{\"Article\":\"...\",\"Project\":\"...\",(AND SO ON) "}],

As you can see all values inside one of the Apply to Each loops are separated by } , { and the different loops are separated by }] ,[ {

Thanks to @ManishSolanki I was able to transform the string into a JSON by using the following formula:

json(replace(Outputs('Compose'),concat('],',decodeUriComponent('%0A'),'['),','))

However, I then only get the JSON for the entries of the first loop.
What comes behind the first loop is missing.

Do you have any ideas?

Thanks in advance!

Kind regards

Markus  

[fchopo]

Hi @Markus44 

Maybe the problem is in the "append" in the "apply to each", rather in the output compose action. Could you share how are you appending the items in the "apply to each"? Are you using an array object or a string variable?

Ferran

[Markus44]

Hello @fchopo,

thanks for your response!

Please find below a screenshot of the flow:

An example of one of the "Map Tables" - Loops:

This is what the appended string variable looks like for this loop.

{
    "name": "DataCollector",
    "value": "[{\"Article\":\"...\",\"Project\":\"...\",\"Type\":\"...\",\"Task\":\"...\",\"Name\":\"...\"},{\"Article\":\"...\",\"Project\":\"...\",\"Type\":\"...\",\"Task\":\"...\",\"Name\":\"...\"}],"
}

Maybe this helps?
If something else is needed for better understanding, please let me know.

Thanks in advance!

Kind regards

Markus

[fchopo]

Hi @Markus44 

Some notes:

1) Instead of using a string variable, I would use an array variable.

2) Do you need to filter data inside the "apply to each" or outside?

Hope that helps!

Ferran

[Markus44]

Hello @fchopo ,

thanks again for sharing your comments.
I tried to change the variable to type array, however, I then get issues with the "replace" function later on during the JSON compose

json(replace(Outputs('Compose'),concat('],',decodeUriComponent('%0A'),'['),','))

InvalidTemplate. Unable to process template language expressions in action 'Compose_JSON' inputs at line '0' and column '0': 'The template language function 'replace' expects its first parameter 'string' to be a string. The provided value is of type 'Array'. Please see https://aka.ms/logicexpressions#replace for usage details.'.

To the second question:
Yes, the filter is needed inside the Apply to Each. 

So, any idea would be much appreciated.. 🙂

Thanks and regards

Markus

[fchopo]

Yes, that's normal. The thing here is trying to understand what you want to achieve:

1) You get a list of items from somewhere

2) You filter this list on some conditions

3) You add the output to a variable.

In conclusion, could you post a sample on the input data and the output?

[Markus44]

Hello @fchopo ,

trying to provide some additional info, hope that helps:

Detailed look into the Apply to each

In general I have three SQL tables that I need to match.

In the apply to each I look for all open tasks a user has which is why I need to filter it inside the apply to each.

Kind regards

Markus

[fchopo]

OK! So now, you can append the output of the map tables to the array variable, and then you should have something like this:

[
{"Article":"...","Project":"..."},
{"Article":"...","Project":"..."},
{"Article":"...","Project":"..."}
]

Then, you wouldn't need to add the "replace" action.

[Markus44]

Hello @fchopo 

Unfortunately something does not work as expected.

When I do this the array variable inside the apply to each looks like this:

And then the Compose of the Variable outside the Apply to each outputs something like this:

All values inside one of the Apply to Each loops are separated by } , { and the different loops are separated by }],","[{

[fchopo · Accepted solution]

So, you are having different arrays in the same variable. I would try to store them in different variables and join them like it's shown here: Power Automate Join or Merge Arrays with Speed - YouTube

If too complicated, maybe you can use the "replace" approach, although I'm not sure if that's the best solution.

Hope that helps!
Ferran 

[Markus44]

Hello @fchopo 

thanks a lot for the response and for providing the link.
I tried to rebuild to flow following the instructions and I do get promising results. I still need to figure out if the outcome is the same.

After joining the arrays I get one big array now that looks like this

[
  {
    "Article": "xxx",
    "Project": "xxx",
    "Type": "xxx",
    "Task": "xxx",
    "Name": "Bob",
    "Email": "xxx"
  },
  {
    "Article": "xxx",
    "Project": "xxx",
    "Type": "xxx",
    "Task": "xxx",
    "Name": "Chris",
    "Email": "xxx"
  },
  {
    "Article": "xxx",
    "Project": "xxx",
    "Type": "xxx",
    "Task": "xxx",
    "Name": "Bob",
    "Email": "xxx"
  }
]

In this example Bob has two different entries.
I would like to loop through the array and then send an Email to Bob showing him his two entries, Chris gets his own Email and I only show him his data.

Any suggestion on how I loop through the array most efficiently?

Thanks and regards

Markus