This can be achieved using the Parse JSON, Select and Join actions.
The finished flow will be trigger by a button and contain the following actions:
- Initialize variable - Initialize a variable with the data we want to manipulate
- Parse JSON - Assigns a schema to the data allowing us to more easily access the array
- Select - Converts the object array into a string array
- Join - Concatenates all the items within the string array
and look like this:
Each action needs to be implemented as follows:
1. Initialize variable
First add an initialize variable action with the following values:- Name: data
- Value: The JSON you want to process (as noted in the answer the "@" characters should be removed)
2. Parse JSON
You then need to add a Parse JSON action to tell power automate the schema of the data assigned in the initialize variable action. This will allow us to more easily reference the array in the next step. The values should be set to:- Content: The data variable
- Schema:
{
"type": "object",
"properties": {
"odata.context": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object"
}
}
}
}
3. Select
Now add a Select action. This action is normally used to transform one array of objects. However, we can use it to return an array of strings. To do this click the Switch Map to text mode button.
Although you can achieve the desired result by using the expression item()['displayName'], it's possible to construct more complex string by combining the values within the object. For example, we could combine the displayName and mail properties by setting the map value to "@{item()['displayName']} - @{item()['mail']}".
4. Join
Finally use a Join action to concatenate the string array produced by the Select action with a specific separator (in your case a comma and space character , ).
This can be achieved using the Parse JSON, Select and Join actions.
The finished flow will be trigger by a button and contain the following actions:
- Initialize variable - Initialize a variable with the data we want to manipulate
- Parse JSON - Assigns a schema to the data allowing us to more easily access the array
- Select - Converts the object array into a string array
- Join - Concatenates all the items within the string array
and look like this:
Each action needs to be implemented as follows:
1. Initialize variable
First add an initialize variable action with the following values:- Name: data
- Value: The JSON you want to process (as noted in the answer the "@" characters should be removed)
2. Parse JSON
You then need to add a Parse JSON action to tell power automate the schema of the data assigned in the initialize variable action. This will allow us to more easily reference the array in the next step. The values should be set to:- Content: The data variable
- Schema:
{
"type": "object",
"properties": {
"odata.context": {
"type": "string"
},
"value": {
"type": "array",
"items": {
"type": "object"
}
}
}
}
3. Select
Now add a Select action. This action is normally used to transform one array of objects. However, we can use it to return an array of strings. To do this click the Switch Map to text mode button.
Although you can achieve the desired result by using the expression item()['displayName'], it's possible to construct more complex string by combining the values within the object. For example, we could combine the displayName and mail properties by setting the map value to "@{item()['displayName']} - @{item()['mail']}".
4. Join
Finally use a Join action to concatenate the string array produced by the Select action with a specific separator (in your case a comma and space character , ).
I did the following ...
Loaded your JSON (minus the @ symbols because PA doesn't like them) into a variable so I can loop over it.
I then created two other variables, one to hold the concatenated string as it's being worked on and the other to hold the end result.
I then looped over the array of values and pulled out the displayName property and appended it (with a leading comma) to the Concatenated String variable.
Expression 1 = variables('Data')['value']
Expression 2 = item()['displayName']
The final step is to then remove the leading comma with an expression.
Expression = substring(variables('Concatenated String'), 1)
Result
Flow
PowerAutomate: Json Array to String back to array. JSON() returns only first entry
PowerAutomate: Json Array to String back to array. JSON() returns only first entry
Using a Power Automate flow, how do I convert JSON array to a delimited string? - Stack Overflow
Parse JSON output into a usable string
So I've been trying to tackle this for a while and am a little stuck.
-
The flow reads output from Excel into an Array (using Filter Array)
-
I use String() to convert the Array to a single, very long string (Still in the jsonFormat)
So from
[{
"@odata.etag": "",
"Item": "12345",
"figures": "12",
"sourcename[Customername]": "Timmy"
},
{
"@odata.etag": "",
"Item": "567",
"figures": "9",
"sourcename[Customername]": "James"
}
]This then becomes flat and is 1 very long string. (I can't use SELECT as Key names randomly have a prefix to them.
{"@odata.etag": "","Item": "12345","figures": "12","sourcename[Customername]": "Timmy"},{"@odata.etag": "","Item": "567","figures": "9","sourcename[Customername]": "James"}3. I then use Compose > and the the replace() function to Analyse the long string and remove "Sourcename[" and ]
So far so good!
1 very long Json String and it's now in the right format (Customername)
--
This is where I run into trouble.
When I use Json() function to convert it back , I only get the very first Object (Where the customer name is Timmy.
anytime I use
json(outputs('compose_3')) 'Compose_3 being the very long Json string (string format).I get this issue.
If I try splitting the string, I end up which a whole heap of "/" in the String (Which is what you expect when you convert a string to Array, you get a badly formatted Array.
"{\"@odata.etag\"\: \"\",\ etc These are escape characters in the backend (That compose normally filters out visually), but using the Split, it brings them to the front of view.
So I'm stuck on the correct way to handle this very long string and getting it to appear as multiple entries in ParseJson etc. I tried adding a manual "Apply To Each" but kept hitting the same issue with JSON() only returns Timmy and not any other records.
Any thoughts on what I can try here? This is the last step to get this working correctly.
So I've been trying to tackle this for a while and am a little stuck.
-
The flow reads output from Excel into an Array (using Filter Array)
-
I use String() to convert the Array to a single, very long string (Still in the jsonFormat)
So from
[{
"@odata.etag": "",
"Item": "12345",
"figures": "12",
"sourcename[Customername]": "Timmy"
},
{
"@odata.etag": "",
"Item": "567",
"figures": "9",
"sourcename[Customername]": "James"
}
]This then becomes flat and is 1 very long string. (I can't use SELECT as Key names randomly have a prefix to them.
{"@odata.etag": "","Item": "12345","figures": "12","sourcename[Customername]": "Timmy"},{"@odata.etag": "","Item": "567","figures": "9","sourcename[Customername]": "James"}3. I then use Compose > and the the replace() function to Analyse the long string and remove "Sourcename[" and ]
So far so good!
1 very long Json String and it's now in the right format (Customername)
--
This is where I run into trouble.
When I use Json() function to convert it back , I only get the very first Object (Where the customer name is Timmy.
anytime I use
json(outputs('compose_3')) 'Compose_3 being the very long Json string (string format).I get this issue.
If I try splitting the string, I end up which a whole heap of "/" in the String (Which is what you expect when you convert a string to Array, you get a badly formatted Array.
"{\"@odata.etag\"\: \"\",\ etc These are escape characters in the backend (That compose normally filters out visually), but using the Split, it brings them to the front of view.
So I'm stuck on the correct way to handle this very long string and getting it to appear as multiple entries in ParseJson etc. I tried adding a manual "Apply To Each" but kept hitting the same issue with JSON() only returns Timmy and not any other records.
Any thoughts on what I can try here? This is the last step to get this working correctly.
You need to specify this as your expression into the loop ...
body('Send_an_HTTP_request')?['value']
That should give you what you want.
Based on above answer I just want to do an small update:
body('<HTTP_ACTION_NAME>')?['<Body_array>']
<HTTP_ACTION_NAME>: How you can the action were you request data to API
<Body_array>: How your array is named inside the body answer
Example:
Body Answer from HTTP request:
Then, my expression should be:
body('getAllDefects')?['entities']