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
[Question] How to Create a single JSON object from an Array of Objects
powerapps - Convert JSON (Object) to Array - Power automate - Stack Overflow
Solved: Array into json - Power Platform Community
PowerAutomate: Json Array to String back to array. JSON() returns only first entry
In Power Automate, you can use the Select action to convert an array into an object. You can use the item() expression to dynamically reference the elements of the array. Here is an example of how you can achieve this:
- Initialize two arrays, one for the headers and one for the values.
- Add a
Selectaction and set the "From" field to the headers array. - In the "Map" field, use an expression like this:
{item(): items('Select')[item()]} - The output of the
Selectaction will be an object with keys from the headers array and values from the values array.
This method should work for arrays of any length and should be relatively performant.
Once you have your data in an object format, you can use the Parse JSON action to convert it into JSON. From there, you can use a third-party connector like Plumsail Documents to convert the JSON into XML and manipulate it with XPath.
I hope this helps!
Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below
Regards Adeyemi
Chowdy Adeyemi --
Thank you for your interest in my problem I thought I was in a forum for PowerAutomate-Flow and so I didn't mention that. I will edit/update my question.
[Solved - see answer below] Currently setting up a form that sends submission data in through an awkwardly formatted JSON array and I'm wanting to format it into something easy to parse for tying into a CRM. I've been able to use the "select" operation to pair the keys and values the way I want to, but am hitting a wall in terms of knowing how to convert the array that it gives me into a single JSON object containing multiple name/value pairs.
[
{
"Company Name": "ContosoLatoso LLC"
},
{
"First Name": "John"
},
{
"Last Name": "Smith"
},
{
"Email Address": "test@contosolatoso.com"
},
{
"Country": "USA"
},
{
"Type of Company": "Organization using Microsoft 365"
},
{
"How many users need this functionality?": "300"
},
{
"Explain your need for this functionality": "We need it badly"
},
{
"Top features & functionality you think should be included in the product": "We want it muchly"
},
{
"Other Questions": "How much will it cost?"
}
]I would like to take the data from that array and format it into a single JSON object as demonstrated below...
{
"Company Name": "ContosoLatoso LLC",
"First Name": "John",
"Last Name": "Smith",
"Email Address": "test@contosolatoso.com"
//...And so on and so forth for all the objects from the array above...
}Any input on how to go about doing this would be appreciated!
[Edit - Solution] So I posted my question to the Microsoft PowerAutomate forum and got an answer similar to the logic of u/tom_riha 's below, but using Compose blocks instead of init/set variable blocks.
So my flow is now as follows -
-
"HTTP Request/Webhook" trigger step
-
"Select" step
-
takes the very weirdly presented form data and matches the key:value pairs
-
outputs the array I mentioned above that I want to convert
-
-
"Compose" step
-
Uses expression
string(body('Select'))which creates a string from the data (the array mentioned above) from the previous 'Select' step
-
-
"Compose 2" step
-
Uses expression
json(replace(replace(replace(replace(outputs('Compose'),'{',''),'}',''),'[','{'),']','}'))which removes the {}'s, replaces the []'s with {}'s, and then uses the json function to convert the string from the previous "Compose" step's output back to a JSON object -
The output of this step matches exactly what I was looking for.
-
-
"Parse JSON" step
-
Takes the JSON object/output from the "Compose 2" step and parses it into individual properties that I can feed to something like a CRM (I currently just have the Flow bot posting a message to a Teams user with the info for testing)
-
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']
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.