JSON Array in Power Automate - Walkthrough lesson with examples
Vložit
- čas přidán 7. 08. 2024
- Learn how to visualize a JSON Array using an online tool, retrieve key values, filter an array, discover the length, and retrieve a list of all values for a particular key e.g. Email. I will walk you through with a demonstration by building a Cloud Flow in Power Automate.
Want to submit an idea or feedback? forms.office.com/r/4EqE7VHVfH
Fancy a challenge and learn more about JSON and techniques? Check out • Power Automate Switch,...
00:00 Intro
00:32 Example Array
02:00 Cloud Flow Demos
02:35 Retrieve single key from Array (Elements cannot only be selected with integer index)
07:48 Filtering an Array
10:00 Apply To Each on Results of Filter Array
11:38 Length of an Array
13:12 List of all Emails from Array
14:51 Other Demo Links and Summary
• Simplify the Case and ... Using an Array for If Else Cases/Conditions
• Parse a CSV to JSON Ar... Parsing a CSV to an Array
• Save file attachments ... Saving File Attachments to Custom Paths using an Array Please buy me a coffee www.buymeacoffee.com/DamoBird365 ☕ - Věda a technologie
Excellent, I was scratching my head on this one for a while.
This was fantastic. I was able to sort out my flow in no time with your excellent tutorial. Keep up the great work.
Amazing video, learned a ton of stuff. Thank you, so much good stuff here!!
Thanks for the quick but effective examples and going into the expressions, explaining what they are doing. Your finger was pointing in the right direction, and now, after watching this, I am also heading in the right direction. Cheers
If you want to learn a bit more about JSON and try some challenges, take a look here
czcams.com/video/G3Q1WuZTWuY/video.html
@@DamoBird365 HI Damian, I’m having an issue with objects/arrays. I have a flow that triggers when a form is submitted, the flow gets the responses but not all the questions have answers due to branching. I would like to filter out all the blank responses so that I can put the rest of the questions that do have responses into a html table to email. I’ve tried parse json, select, filter array and apply to each but can’t seem to get it to work. I imagine this would be a fairly routine task but I haven’t been able to find a solution. Might be a useful video idea, I know I would love to see it 😂
Thank you - that was so clear and concise.
That Was So Clear man ! thank you
Simple, to the point and awesome 👌
Thank you for taking the time a year ago to create and post this. I found it while trying to build a solution in Power Automate for my local baseball association and it was extremely helpful to me. It was very clear and easy to follow and I appreciate your approach of building the solution step by step.
Nice one Scott 👍 what else have you got planned for your baseball team? Feel free to reach out if you don’t find the video you’re looking for.
@@DamoBird365 I have a few forms that coaches can use to request practice time and game time changes. I then use power automate to automatically reply as well as copy the other members of our board who need to be notified.
This video was super helpful thank you! I subscribed
Thank you so much. This worked for me.
Nice and easy Damo... Love it! Thanks...
Thank you very much Franklin - any time 😉
Thanks a lot! That was very useful!
No problems Marisa, really glad it was of help. 👍
Thanks buddy this was really helpful :)
This is great, thank you!
Thank you Gabe 👍
Thanks a lot, you save my Day
very very useful, thank you so much ! subscribed as well :)
Cheers Hannes, if you’ve got any video ideas, feel free to email me ideas@damobird365.com
Lovely video ^^ Thank you! It's a very helpful video and you have a very clear way of explaining. 🙏👌
Thank you Maria 👍
Thank you so much, this is really helpful
Make sure you check out czcams.com/video/G3Q1WuZTWuY/video.html for more tips on JSON and Data Expressions.
Awesome
Great video , thank you 👍🏼
Did you see my other video on json czcams.com/video/G3Q1WuZTWuY/video.html
@@DamoBird365 Not yet .. but I'm watching your other one about Repurposing the array to grab unique values which I need to do, and that one is great as well. thank you. Such a big learning curve here for someone new to Power Automate. thanks again
so well explained
Thank you Soumavo 😉
Thank you, I was pulling my hair out. You saved me from a nasty call tomorrow.
Hey Don, anytime. Hope you’re sorted. 👍
@@DamoBird365 yes, thank you so much. You really gave me the base I needed to build and improve my existing flows.
If you’re looking to learn more about arrays and efficiency, check out 👀 Unlock the Power of Arrays in Power Automate: Learn Common Techniques & Solve 5 Challenges!
czcams.com/video/G3Q1WuZTWuY/video.html
Very clear explanations, great content, thank you. I wonder, have you made any content showing how to change properties within the arrays (for example to keep track of a yes/no state for a specific object)? At first thought sounds like something i would only be able to do using array variables.
You could check out challenge 2 here: Unlock the Power of Arrays in Power Automate: Learn Common Techniques & Solve 5 Challenges!
czcams.com/video/G3Q1WuZTWuY/video.html and check out setproperty() learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#setProperty
@@DamoBird365 Thank you! I had already watched the video and with this suggestion i realized all that was needed is to change the expression from "addProperty(...)" to "setProperty()"
Thanks for detailed video
I am trying to export data from Power BI Report to CSV which contains a date field and the formatting of date is 2004-01-14T18:30:00.000Z like this instead of dd-mm-yyyy. Is there a way to format a field in the array?
Formatdatetime www.damobird365.com/formatdatetime-and-formatting-a-string-date/
Thanks. What if we have a xls file with property attributes like rent,address,no of bedrooms. How can we convert this into an json array pls. I’m trying to use this json in adobe doc generation api to create a table from the json array.
If it’s 2003 xls, I’m not aware of any method to convert. If it’s xlsx, you can use office scripts if no table.
Hi, thank you for this video. It has really help me a lot. One question when you iterate to give you all emails the output is an array of all emails. How can I change that instead of returning a array with all email can return a simple text with all emails?
Once you have that array of emails, simply use the expression JOIN() on the array. It will join those values based on another string. For instance join(SelectOutput,';') which will give you a semicolon separated list of emails.
@@DamoBird365 Thank you for the respond. I was trying in service now to view it as a list but the '
' doesnt seems to work. Dont know if you have a solution for that! Thanks again!
Thanks for this Detailed example. Using Power Automate I am doing following operations as per requirement.
Trigger condition : When email trigger with csv attachment
- In .CSV file there is no empty row in between content
- Using select operations mapping key and value pairs.
But after execution of select operation , in output I am seeing empty value.
Is there any value to restrict empty row value check in select operation
"From" option dynamically?
Also Parse JSON is failing due to empty , so I added "null" value in Parse JSON scheme.
After adding this value , I could not see return values(Name,Date,Email) from csv
Kindly help.
Hi there, you can use the Filter Array action to remove null rows, where value not equal to '' or null. Also, in your expression, do you include ? in the path. e.g. triggerbody()?['value'] will return null if not found, whereas triggerbody()['value'] will cause your flow to fail if the value is not returned in your JSON array. Therefore trying adding ? to your expression.
Hi Damien, Excellent video, good pace and great information. I was able to follow everything without issue but I'm dealing with a more complex array. One that gets returned from the Get Items SharePoint action. I'm trying to get to a list of emails like what you were doing but the column I'm getting these email values from is a people column with multi select enabled. In looking at the object that comes from the Get Items action, what I'm trying to get to is Object -> body -> value -> PeopleColumn -> Email. I just want to get a list of these emails so I can use them in a CC of an email. Any suggestions?
You want to use select in text mode, then a join in the result. See here JSON Arrays in Power Automate: Learn Common Techniques & Solve 5 Challenges!
czcams.com/video/G3Q1WuZTWuY/video.html
@@DamoBird365 Thank you for responding. I checked out the video and understand the method but it does not work for my scenario because what I'm trying to get to is two layers deep. I can use the select statement to get to the multi select people column and that returns an array of all of the people for that record I'm looking at but I can't get to their email address in that array. I keep getting the error that the Email cannot be selected because array element require a integer index. I can share the redacted array returned from the select action item if that helps.
@DamoBird365 I figured out what the issue / difference was. For a SharePoint people picker column, when you make that column multi-select, it changes how it stores the data from an object to an array of objects. In order to get to the emails you need to use two nested apply to each actions. What a pain. Search "Get items for each user in multiple people picker field (Power Automate)" to get to the article that cleared it up for me. Maybe there is a more efficient way to do this but at least it works.
👍
I'm having this exact same problem, but w forms. This would be trivial to do in C# or Powershell, but its a total nightmare to do in Power Automate. The nested replace method to clear out the formatting from the multi-select output is rather terrible. @@kurthenderson6848 My issue is a I have a multi-select form, with 4 users and an array with the 4 users and their emails. I am trying to figure out how to build a CC list that matches up the name-email parts as well for recipients of an email that collects the form responses. I'll check out that article thank you.
I have a question on reading Excel file from OneDrive Busisness. When I use List rows Present in a Table, however, when I upload a new file, it reads the old data. Is there any best way to read from Excel stored from OneDrive to SQL?
If you use the trigger when a file is created, you should be able to dynamically reference the new file. I’ve no experience of importing data into sql. Maybe try the forum powerusers.microsoft.com/
@@DamoBird365 Thank you for your response. I use when file is created, but it always read from Cache. The issue is from the read side, the SQL part works perfect. I will try the forum.
Hi,
I am getting the below error, can you please help.
The execution of template action 'Apply_to_each' failed: the result of the evaluation of 'foreach' expression '@triggerBody()?['entity']?['Power BI values']' is of type 'Null'. The result must be a valid array.
Powerautomate will return null if the object doesn’t exist when using ? In the expression. I would take a look at your trigger body history output, check the entity and power bi values path.
i want to know how to sort array elements in descending order.
Not so easy in Power Automate, albeit you can use office scripts or a custom connector written in c#. I might do my end version of a video if there’s enough interest.
I would like to take your last step, List All Emails from Array, and use that for a similar flow store each IMAGE URL from my array to a separate field on a Sharepoint list. I need each URL to be in a separate field because my next step is to Populate a Word Document, where I will Get Content from each URL and save it to a distinct Word QuickPart field. How would I handle this, and is it the correct approach? Thank you!
Hey Michael. I would have said that you would need to have a column for each url, will that be fixed or at least limited to a maximum number? You can then use an apply to each and a compose to get the file content and create another array outside the apply to each using outputs(), known as Pieter technique which I demo in latest video. You can then call them by integer value from your array. Ie ?[0]. Word doesn’t support repeating parts for images but if they are in fixed positions I guess it will work. What’s your use case? Sounds interesting.
@@DamoBird365 Thanks for replying! Yes, I have 3 arrays, with a maximum of 5 images each. My use case specifically is I'm capturing survey data using Microsoft Forms, which allows for attachment reponses, but drops them into arrays automatically. I then store that data directly to a Sharepoint list where my team does qa work (one of those tasks being to check each image). After QA is complete, I replicate the clean data out to my client sites and generate an executive summary for each survey in Word/PDF format, and would like to include the images in the summary doc. Correct, repeating parts is the big hangup here. I've been looking at initializing distinct variables for the 15 images, and as you said loading a corresponding index value from the array to each variable, but it seems like I would need a condition on each loop to make sure I'm grabbing an index number that exists in the array. I will check out the Pieter technique, but it sounds like I'm not that far off.. woohoo!
@@michaelwaxman2067 if you get stuck, drop me an email ideas@damobird365.com. 👍 sounds like an interesting solution.
This is great and helpful, In my case I want to extract the child value("value": "386") inside an element, but I’m getting blank value when I pass the attribute name(value) but getting the value (926) when I pass the objectTypeAttributeId.
{
"workspaceId": "",
"globalId": "",
"id": "123233",
"objectTypeAttribute": {
"workspaceId": "",
"globalId": "",
"id": "926",
"name": "ID",
"label": false,
"type": 0,
"description": "",
"defaultType": {
"id": 0,
"name": "Text"
},
"editable": true,
"system": false,
"sortable": true,
"summable": false,
"indexed": true,
"minimumCardinality": 0,
"maximumCardinality": 1,
"removable": true,
"hidden": false,
"includeChildObjectTypes": false,
"uniqueAttribute": false,
"options": "",
"position": 0
},
"objectTypeAttributeId": "926",
"objectAttributeValues": [
{
"value": "386",
"displayValue": "386",
"searchValue": "386",
"referencedType": false
}
],
"objectId": "2524"
}
386 is the first object {} with key name value of the objectattributevalues array [] first(output(‘compose’)?[‘objattvalue’])?[‘value’]
great video. I have a question that I got a result from Power Automate like this and I just want to get name only, how do I do that? See a result "Business Owner:[{"_id":"4101FD8D-xds-dfsg-ABD2-3B960FE2050A","_type":"Person","name":"Rick, Brown"}]. Thank you
Looks like your business owner is an array [] and therefore you want to get the first() Business Owner and then the name field. Something like first(expression to business owner)?['name']
What if i would like to select only selected keys(columns) and corresponding values from a initial array ie. I would like to have and array of names and emails. (I have a http request which result od 160 columns and i need only some of them)?
That’s exactly what select can do for you but sometimes you can limit the columns returned by the api too, like graph. Check out czcams.com/video/afqvGAb20Dw/video.htmlsi=XjII21bFGrC6atyM
@@DamoBird365 thank You very much for the answer and the whole content. Will watch it for sure. Great tutorials! Help me a lot so far