How to parse complex JSON in power automate

In this video we use a complex JSON file and parse it using power automate flow. In the end we convert extracted JSON elements into a CSV file and store it to SharePoint online.
Link to the JSON content:
dotnetking.com/files/Xero_dumm...
- 0:00 Introduction and problem definition
- 1:30 Analyzing the JSON content
- 1:59 Developing the Power Automate Flow
- 5:55 Extracting the array from the JSON file
- 7:57 Converting JSON schema to match the csv fields
- 11:20 Converting JSON to csv
- 17:35 Saving the csv extract to SharePoint library
- 19:10 Conclusion
To enroll in my Udemy courses see below link for discount vouchers
sslwsh008.securedata.net/dotn...
Contact me
- LinkedIn / alirezaaliabadi
- Twitter / aaliabadi1
- Instagram / alireza.aliabadi_ig
- Facebook / alireza.aliabadi.fb

[@AlirezaAliabadi]

Please let me know if you want to see the same thing done using visual actions (using Parse JSON).

[@AlirezaAliabadi]

Here is the same scenario using Pars JSON kzbin.info/www/bejne/oZyVlZl_mNyCpJo

[@joakimwasseng8217]

@Alireza, Thank you for the two good videos on how to parse JSON, clear and good pace! Loved them. If I could make a request it would be interesting to know more on how to store/fetch the data in something else than csv/Excel/SharePoint. Dataverse/Azure Table Storage/SQL?

[@nicolneal]

Have a use case for loading complex JSON into Cosmos DB. Would be much appreciated.

[@ARealityStorm]

I have been struggling with a JSON payload all day until you showed variables('object').element in this and it's genuinely saved me SUCH a headache. Thank you so much. Really valuable lessons in the rest of the video too. Very grateful.

[@anthonyfawkes3822]

Absolute legend - thank you so much for this video it has helped me solve a problem that I've had for over a month. THANK YOU!

[@juliantagell1891]

Thanks Alireza, this is a great help. Very much looking forward to seeing how you'd do it including the parse json step (I think I have some idea... but am open to being surprised). You covered the process very well. Someone else had shown me how to do it with an apply to each but I will give your go a try (certainly seems faster!). Cheers.

[@edmusters2595]

Hey my friend, this is the SECOND time coming across this video to help me parse the JSON nonsense that a SharePoint REST API returns. Mission accomplished. :) THANK YOU!

[@Bzlatar]

Truly the best tutorials i have found only for Power Automate. Thank you so much!

[@vishaljhaveri7565]

Best video on JSON Parsing in Power Automate! Thank you Alireza Aliabadi.

[@paulmatkin4788]

Great job explaining this. I'm taking my first steps in power automate and I was able to follow using my own project and get it to work!

[@TheAsterii]

As always, very nice video presentation. Looking forward to part II :)

[@andydevman]

Great tutorials Alireza, Shukri شَكَرَ !!!

[@arshad801]

No Ali Reza this video not only deserve like, but you also need a thumbs up. What i have learned from your video in 20 minutes, i havent learned it in over an year. You have explained it like a real teacher. Thank you so much for sharing your expert knowledge.

[@arshad801]

And now i am going to take your power automate course from udemy

[@javiervelazquez8808]

Hey, just wanted to thank you for this video, very well explained. Thumbs up and subscribed!

[@shasikumar2851]

It's complex, but you made it soo simple... Thank you...!!

[@jamesdiekman629]

Awesome Alireza - very well explained as usual. I recently took Alireza’a expressions course on Udemy and it’s seriously improved my understanding and use of expressions on Power Automate. Highly recommended 👍

[@barunroyghatak4380]

Thank you James I will also registered. Thank you Ali too.

[@nocodeyuan]

Great video, exactly what I’m looking for

[@mS1x6VIFYIH]

A special approach for a specific problem. Congrats for the video! The man has a name and the name is Alireza!

[@antonioskokiantonis7051]

Great content from a great Developer!

[@dxkeeling]

Alireza, Thank you for the work you are doing. It has been very helpful as I begin to learn power automate. It would be nice to see how you visual actions -parse json or csv file in a future video.

[@JY-ni7id]

Thank you so much for the sharing! Love it!

[@TheMartinBlanc]

Just what I needed. Thanks!

[@karchunchong7104]

great video. like the explanation, easy to understand.

[@stivenarrublasilva6674]

Thanks Alireza, I was in a trouble and the video helped me a lot.

[@danieldawson7443]

Thank you so much this is amazing. Extremely helpful :)

[@deepakatri1374]

thank you for this video, helped a lot

[@liam2161]

Thank you. Very useful. I had a complex JSON object I finally got to something sensible. One question is nested arrays and filters. I have a tasks object, for each task it has an array of objects. I ended up with loads of filters in a loop through tasks and then created a manual JSON object. Basically I need to transpose name to the root object with its value. Here's an example; [ { "TaskID":123, "TaskName":"A Name", "Fields":[ { "name":"Address", "value":"street address" }, { "name":"Phone", "value":"123456789" } ] } ]

[@diegodetomini3292]

Thanks, Julian!

[@rishiagrawal9613]

Excellent video sir. Thanks a lot for your help.

[@amirzareei8374]

Great video. right to the point

[@tomaszsagan4672]

Thank You for this video :)

[@georgekontus6730]

Very useful. Thank you.

[@liyanagesigera6756]

Great video thank you

[@EledinaBonilla-cd8lj]

You are awesome 🎉

[@chaitanyatagare8027]

Very helpful

[@subrataranjanpal6518]

Wonderful

[@Amaralrosa1]

Not only didn't I liked the video but also favourited it

[@vinamrachandra9611]

Thanks for explaining a complex subject in an easy way. Is there a way I can create the SELECT steps with, say 50 fields, without typing through each of them? Can I build the JSON, Power Automate generates behind the scene, in say, an Excel sheet and then paste into the step?

[@rogersonra]

I have seen a few of your KZbin videos Alizera and have been convinced to enroll in some of your Udemy courses. In the meantime, I used this video to help create an HTML table for Posting to a Teams channel. This works and I see the post in the channel. I used one of your other videos to learn how to format the table by adding a style. But when I did this and checked the channel the tble remained unformatted. So I created an email with exactly the same input and the table was formatted correctly. Do you know if the Post to channel does not yet allow formatting? Thanks, Rob

[@davidadams421]

Excellent video! Quick question: what's the difference between the expression context item().element.subelement and item()?['element']? Is one a 'hard coded' data structure reference, the other a query? When would one be useful over the other? Thank you so much for sharing your knowledge!

[@Netzcod]

It is possible to cross information between matrices having a column in common.

[@sashawarchoon1989]

It sounds simple at the first glance but then I realized that instead of using join function I often loop through array and then append item() to string variable🤦‍♂️🤦‍♂️🤦‍♂️ but... at the same time my approach works fine when you need to do some sort of validation against each array item and append to string variable only those items that pass validation ( for example, checking if user is still active/present in Azure AD or O365). Good job, Ali.

[@AlirezaAliabadi]

For Power Automate using apply to each is fine, but for serious enterprise level applications on Logic Apps, Microsoft bills your azure subscription based on the number of actions called looping through items in an array significantly changes the number on you Azure subscription bill. So with loop approach, if some day you need to export the Flow to logic apps platform for any reason, these loops need to be replaced.

[@sashawarchoon1989]

@@AlirezaAliabadi , thanks for advise!

[@krisnantohusodo1624]

Thanks Ali, Great Video as usual. Could you please make a tutorial how to call json pagination in power automate from API, with data more than 5000++

[@OddPoliticalBedfellows]

Thanks for the great video! I am trying to create a flow that updates ALL checklist items (based on JSON object information from Get Task Details) across tasks from Planner, as opposed to one particular task based on a trigger. Do you know if this is possible? Thanks again!

[@stevenvits152]

Thank you again for the superb content. Trying to parse a complex JSON converted from a XML I got stuck in creating the array variable due special characters in the name "variables('Var1').Soap:Envlope". Do you have any suggestions here please?

[@frodesighaug6164]

Hi Alizera:) Great video! Do you know of a solution to use a complex JSON and get the data directly into dataverse table?

[@ronaktiwari6127]

Hi Alireza, a little unrelated question. I have a big json file very much similar to the one you used in this video. The file size is close to 1.69 GB. I am trying to convert it to csv using python. Someone told me that pyspark can help but it does not seem to be working. Can you please direct me to some other way in which maybe I can convert this big json to a csv and then probably do some analysis?

[@n2datasolutions443]

Hey, I have a webhook that sends through content-type: "application/x-www-form-urlencoded" having trouble parsing this as JSON and obtaining the values. The response are all like: "Key": "data[email_type]", "Value": "html" I'm really confused as to how I can extract these values and send them back to my share point list. Any advice or pointers would be very very greatly appreciated

[@rishabhdeshwal6864]

Hello I want to convert complex json file into Excel(XLS) in the json file there are some multiple array. Can anyone suggest me which tool I have to use.

[@thomasharrison2165]

This has been an absolute win. I’ve managed to extract the values. However, I have a further nested values within that array that I am struggling to access. Any help? Thanks again. This video saved me a few days digging!

[@nirjharray5990]

Great step by step explanation. I love your videos and detailed explanation. However, I did not understand the need for the Array variable initialization. Can't we use Select to fetch the values from the JSON object variable initialized in the first step directly?

[@AlirezaAliabadi]

Of course you can. I personally prefer to use extra variables and actions to make the workflow better understandable for others. In most cases I don't maintain the workflows that I design, so adding extra actions and variables just to reduce complexity is always a good practice.

[@sukhmitbhullar7181]

"rows": [ { "elements": [ { "distance": { "text": "92.0 km", "value": 92042 }, "duration": { "text": "1 hour 7 mins", "value": 3996 }, "status": "OK" } ] } ] If I have this structure, how can I retrieve "value" of both the duration and distance object?

[@mS1x6VIFYIH]

Alireza, my csv file has maximum of 10 lines The JSON content is coming from an API request and the parameters are set to 100 results. I ran a test at Postman and it comes with 100 results, but in power automate, the csv only store 10 lines. Do you know what it can be?

[@andromurvanidze1462]

I have somewhat similar Json which I get using HTTP request on daily bases, but instead of creating CSV I need to create(or update based on the productID) multiple records in Dynamics 365 (using CDS). Is there any way I could do that?

[@AlirezaAliabadi]

Yes, this video has your answer. kzbin.info/www/bejne/oZyVlZl_mNyCpJo

[@vuongdang5769]

I have a question that how I can select the: Description, UnitAmount,AccountCode.... Count you explain about this point?

[@TheCarter117Gaming]

Man im getting frustrated…. What if you need to get info out of a array within the element? How can you flatten it where it will add a line in the excel with the iterated info… like you have a json object that has, FirstName, LastName, and a Kids array with Name and birthday… and i want to create an excel sheet that creates a row for each kids info but it also still lists that parents info (which would be the same in each row)

[@nagasubbareddypidugu9341]

Hello sir, how to read nested array?

[@emmanuelakowuah7948]

Hello @Alireza please i have designed a card using adaptive card want to render on outlook using power automate but it does not render. can you help me.

[@marounsader318]

hi alizera, why cant i do the second variable directly and put the array i need rather than put 2 variables, one for the whole json code nad the other for what i want?

[@AlirezaAliabadi]

Of course you can. I personally prefer to use extra variables and actions to make the workflow better understandable for others. In most cases I don't maintain the workflows that I design, so adding extra actions and variables just to reduce complexity is always a good practice.

[@marounsader318]

@@AlirezaAliabadi thank you for the explanation, makes sense alot! I have one question, in a case i faced is that the sample i gave for the schema one of the values are text, but not always filled and when its empty the flow throws error, as parsing is expecting a value string, how can i make it dynamic to accept empty or string value?

[@SHAli1474]

Hi AliReza I have recently come across your channel and really appreciate your efforts. I'm also going to take the Udemy Course of yours (already enrolled via my Company Portal). Would you be able to guide us on adding JSON data (actually passed through from Power Apps via Flow) to Excel File. This would be a complex JSON object similar to your JSON object but with only 3 level. First Level we have Account No. Second Level we have all Invoices. Third Level we have all partial payments made against each invoice. Can this be added to Excel, in a way that everything is grouped on Account (+/-), then Invoices (+/-) and finally Payments (+/-)? I understand it is quite challenging for mere mortals like us but I believe you are a guru and a subject matter expert and can help :). If you think I should post this question on the Q&A section on the Udemy Course, please let me know. Thanks and kind regards.

[@ashwinkumar5223]

How to create a schema for JSON file using python?

[@darryljacquifrancis1048]

Hu Alireza how do I parse a json coming from a call to an Azure function that returns json but within that json is a property that has json in it. example: the property name is Response: Example data I the JSON. "response": "{\"conflictOfInterestRequestType\":\"Hospitality\",\"dateOfOffer\":\"2021-10-12T16:00:00.000Z\",\"orgName\":\"df\",\"firstName\":\"

[@andrewblock4116]

Is it possible to get the actual Variable names in Power Automate? As in can you create an array that contains the words "Type", "InvoiceID", etc...?

[@AlirezaAliabadi]

Can you explain more? This is an interesting topic for a KZbin video.

[@andrewblock4116]

@@AlirezaAliabadi Sure. I'm working on the inventory system for my company right now because we work on Android phones. I have made a Sharepoint list with some fields in it to manage the devices in our inventory. The fields columns in the list are "Asset Tag ID", "Asset Name", "Model Number", "Serial Number", and "Device Category". I'm working on a PowerApp to perform cascaded search queries, so I want to be able to put a drop-down into my PowerApp that allows me to choose which of the columns in the list that I'm going to filter by. It would be great if I could create a flow that is Triggered by the PowerApp (an on-show script probably) that looks at the list, sees what columns are in it, and loads them as possible choices in the drop-down. Then you can pick, for instance "Device Category" from the drop down, so that will be the column that you filter by. This would then bring up a cascaded drop down with the Categories in it like "Samsung Phone", or "Samsung Tablet" etc.

[@JeffersonCanedo]

Thankyou

[@allsprings3424]

good tutorial but too much "I" ;)

[@billypham3024]

great video and I have a question and hope you can help in Parse Json I got this result "[{\"_id\":\"BAD5541A-9FF8-xxx-xxx-xxx\",\"_type\":\"Person\",\"name\":\"Phan, John\"}], how do I configure just to get name only I don't want to display id or person and hope you can help. Thank you

[@CMo_w]

I noticed you were using xxx.yyy.zzz.aaa format. Is there an advantage to using the xxx.yyy.zzz.aaa format versus the ['xxx']?['yyy']?['zzz']?['aaa'] format and how does using the xxx.yyy.zzz.aaa format handle nulls? I believe that is why we can utilize the '?' between the brackets, right? Thanks for sharing 🙂

[@AlirezaAliabadi]

Yes. There is a difference. The syntax that I use, throws error if the node is missing. I use the ? format when I expect missing data. I'd rather get errors that stops the flow and I can get straight to that.