How to parse complex JSON in power automate

Подписаться 15 тыс.

Просмотров 77 тыс.

50% 1

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

Наука

Опубликовано:

14 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 86

@AlirezaAliabadi 3 года назад

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

@AlirezaAliabadi 3 года назад

Here is the same scenario using Pars JSON ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kk2dfIcwOn8.html

@joakimwasseng8217 2 года назад

@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 4 месяца назад

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

@ARealityStorm 2 года назад

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.

@edmusters2595 11 месяцев назад

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!

@arshad801 3 года назад

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 3 года назад

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

@vishaljhaveri7565 5 месяцев назад

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

@juliantagell1891 3 года назад

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.

@Bzlatar 2 месяца назад

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

@anthonyfawkes3822 2 года назад

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!

@TheAsterii 3 года назад

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

@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!

@javiervelazquez8808 2 года назад

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

@shasikumar2851 2 года назад

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

@mS1x6VIFYIH 2 года назад

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

@jamesdiekman629 3 года назад

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 3 года назад

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

@andydevman 3 месяца назад

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

@dxkeeling 3 года назад

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.

@antonioskokiantonis7051 4 месяца назад

Great content from a great Developer!

@nocodeyuan 2 года назад

Great video, exactly what I’m looking for

@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" } ] } ]

@JY-ni7id Год назад

Thank you so much for the sharing! Love it!

@stivenarrublasilva6674 Год назад

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

@TheMartinBlanc 2 года назад

Just what I needed. Thanks!

@karchunchong7104 3 года назад

great video. like the explanation, easy to understand.

@danieldawson7443 2 года назад

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

@diegodetomini3292 3 месяца назад

Thanks, Julian!

@rishiagrawal9613 2 года назад

Excellent video sir. Thanks a lot for your help.

@tomaszsagan4672 2 года назад

Thank You for this video :)

@georgekontus6730 Год назад

Very useful. Thank you.

@deepakatri1374 3 года назад

thank you for this video, helped a lot

@amirzareei8374 2 года назад

Great video. right to the point

@liyanagesigera6756 3 года назад

Great video thank you

@subrataranjanpal6518 Год назад

Wonderful

@EledinaBonilla-cd8lj 9 месяцев назад

You are awesome 🎉

@chaitanyatagare8027 3 года назад

Very helpful

@sashawarchoon1989 3 года назад

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 3 года назад

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 3 года назад

@@AlirezaAliabadi , thanks for advise!

@Amaralrosa1 3 года назад

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

@krisnantohusodo1624 2 года назад

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++

@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!

@vinamrachandra9611 2 года назад

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?

@frodesighaug6164 3 года назад

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

@Netzcod 5 дней назад

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

@rogersonra 3 года назад

I have seen a few of your RU-vid 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

@nirjharray5990 3 года назад

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 3 года назад

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.

@OddPoliticalBedfellows 2 года назад

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!

@thomasharrison2165 11 месяцев назад

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!

@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?

@ronaktiwari6127 3 года назад

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?

@andromurvanidze1462 3 года назад

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 3 года назад

Yes, this video has your answer. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-kk2dfIcwOn8.html

@rishabhdeshwal6864 2 года назад

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.

@sukhmitbhullar7181 3 года назад

"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?

@n2datasolutions443 3 года назад

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

@vuongdang5769 6 месяцев назад

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

@marounsader318 3 года назад

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 3 года назад

@marounsader318 3 года назад

@@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?

@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)

@mS1x6VIFYIH 2 года назад

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?

@emmanuelakowuah7948 10 месяцев назад

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.

@nagasubbareddypidugu9341 Год назад

Hello sir, how to read nested array?

@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 RU-vid 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.

@SHAli1474 2 года назад

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?

@JeffersonCanedo 2 года назад

Thankyou

@darryljacquifrancis1048 2 года назад

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\":\"

@allsprings3424 2 года назад

good tutorial but too much "I" ;)

@billypham3024 11 месяцев назад

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 2 месяца назад

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 2 месяца назад

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.