@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?
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!
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.
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 👍
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.
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.
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.
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.
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.
"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?
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!
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.
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.
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?
Great video. Not sure if I need to parse the JSON but I'm attempting to put together this string. Is your way a better way? I need to do this for dozens of IDs. I'm not sure how to use the outputs to pull from Excel or if you can recommend another way. Within the JSON I'm looking for the ID and from there looking to output a "Detail" columns. Not sure where to go from here. outputs('List_rows_present_in_a_table')?['body/value']
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\":\"
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?
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.
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?
@@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.
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!
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?
I have seen a few of your RUclips 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
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 🙂
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.
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!
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)
I don't normally write comments but this video was an absolute saver. I learned so much. I was wasting time using combinations of Parse JSON compose and for loops getting to nested elements. nothing was working well and it was running very slowly. Thank you 👍🙏
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" } ] } ]
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?
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
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.
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?
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?
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.
@@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?
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
Please let me know if you want to see the same thing done using visual actions (using Parse JSON).
Here is the same scenario using Pars JSON
ruclips.net/video/kk2dfIcwOn8/видео.html
@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?
Have a use case for loading complex JSON into Cosmos DB. Would be much appreciated.
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!
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.
And now i am going to take your power automate course from udemy
Best video on JSON Parsing in Power Automate! Thank you Alireza Aliabadi.
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!
It's complex, but you made it soo simple... Thank you...!!
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 👍
Thank you James I will also registered. Thank you Ali too.
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.
Not only didn't I liked the video but also favourited it
As always, very nice video presentation. Looking forward to part II :)
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.
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.
@@AlirezaAliabadi , thanks for advise!
Great video, exactly what I’m looking for
Thank you so much for the sharing! Love it!
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.
Thanks, Julian!
Excellent video sir. Thanks a lot for your help.
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.
Just what I needed. Thanks!
It is possible to cross information between matrices having a column in common.
great video. like the explanation, easy to understand.
Thank You for this video :)
Very useful. Thank you.
Thank you so much this is amazing. Extremely helpful :)
Great video. right to the point
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++
I have a question that how I can select the: Description, UnitAmount,AccountCode.... Count you explain about this point?
thank you for this video, helped a lot
Very helpful
"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?
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!
How to create a schema for JSON file using python?
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.
Hello sir, how to read nested array?
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.
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?
Great video. Not sure if I need to parse the JSON but I'm attempting to put together this string. Is your way a better way? I need to do this for dozens of IDs. I'm not sure how to use the outputs to pull from Excel or if you can recommend another way. Within the JSON I'm looking for the ID and from there looking to output a "Detail" columns. Not sure where to go from here.
outputs('List_rows_present_in_a_table')?['body/value']
Without going into the details watch this video before making the final decision
ruclips.net/video/kk2dfIcwOn8/видео.html
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\":\"
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?
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.
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?
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...?
Can you explain more? This is an interesting topic for a RUclips video.
@@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.
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!
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?
I have seen a few of your RUclips 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
Thankyou
good tutorial but too much "I" ;)
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 🙂
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.
Excellent! Liked and subscribed
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!
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)
Hi Alizera:) Great video! Do you know of a solution to use a complex JSON and get the data directly into dataverse table?
I don't normally write comments but this video was an absolute saver. I learned so much. I was wasting time using combinations of Parse JSON compose and for loops getting to nested elements. nothing was working well and it was running very slowly. Thank you 👍🙏
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"
}
]
}
]
Great tutorials Alireza, Shukri شَكَرَ !!!
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?
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
Hey, just wanted to thank you for this video, very well explained. Thumbs up and subscribed!
Truly the best tutorials i have found only for Power Automate. Thank you so much!
You are awesome 🎉
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.
A special approach for a specific problem.
Congrats for the video!
The man has a name and the name is Alireza!
Thanks Alireza, I was in a trouble and the video helped me a lot.
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!
Wonderful
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?
Yes, this video has your answer.
ruclips.net/video/kk2dfIcwOn8/видео.html
Great content from a great Developer!
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?
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.
@@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?
Great video thank you
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