How to parse complex JSON in power automate

[Music] hello i have a course on udemy called master power automate expressions in two hours that course is all about working with expressions json uh parsing the content and working with every single node that you have inside the internal messages that goes from one action to another action inside power automate regardless the question that i got from julian one of my students i put it on the screen but bottom of the story is that he says okay you're telling all these things but can you show us how you yourself process a complex request like this that he actually was kind enough to send me the original file that is trying to process and he asked me to show an example of how i go through an actual request and process it and turn it into a csv or an excel sheet and here we go with this question i thank julian and i would like you to go to the comments and thank him for providing the question and also the material so that i can go through it without any hassle now let's get to work let's start with the complex json that i received for this video the json file is just one object opening and closing curly bracket god's sake here and inside it there are a few elements that starts from i you know what forget about it i don't have nerves to do that i just copy it and i open it in json beautifier here so if i go to code beautify i can just copy and paste it here and if i click on treeview it gives me all the properties that i have in this object so i have id status provider name date time utc which none of them really matters but under that i have something called invoices invoices is an array of 76 elements and each one of them is a separate invoice so what we are trying to do we are trying to convert these invoices to a csv file there was no point to really going through all the elements inside this json object i just picked a few of them type invoice id invoice number and all these things you can see them here so you have type invoice id invoice number and blah blah blah but there are a few elements as of arrays like for example payments credit notes and all those things i cannot really flat them out in a csv file so i just skip it at the moment but to show you how i work with the arrays i take it to the next level and i pick one item called contact contact is not an array but inside contact which is an object by itself if i expand it there are seven members inside it and one of them is phones for this specific one phones is empty but i want to get some contact information like contact name contact id and contact phones so if you really look into that one other than all these basic fields i have contact id contact name contact phones and the others are all at the same level of the invoice object let's start the work to do that i go back to flow.microsoft.com and i click on create and as simple as it sounds i create an instant flow and i call it complex json parse i pick manually trigger flow and i click on create so my flow is created i just need to bring all that object inside this flow i really don't want to call it from anywhere else to keep it simple i create a variable initialize variable and i call it invoices object and as the name says okay let me just rename this properly rename and i paste it here type it's an object so i call it an object i go back here and i copy the entire object from here copy and i bring it back inside power automate and i click on the value and i just paste it here if it doesn't respond don't worry because it's a huge object and it takes a while for power automate to really validate it we're good let me just scroll up to the top of the page and i just close it and again closing it takes a few seconds great so now if i go back here you see we decided that we really don't need idea status and all those things but we need to have direct access to this invoices so i go back to the power automate flow i click on add new step and i want to create another variable and this variable should only hold invoices and the type has to be an array so i call it invoices here so these are the actual invoices i just rename it and done type this time obviously it's going to be an array but to get to this array i need to get this element of the entire object to do that i come back here i click on the values i click on expression and i start writing variable and i open the bracket and i need to provide the variable name and that variable name is going to be in voices object this name the name of the variable that we created and then inside it i need to refer to dot and this is the name invoices so if i just paste it here it gets this variable which is invoices object and i get the invoices element out of the entire message that i received probably from a service and i put it inside an array i don't need any casting converter or anything else because invoices by itself is an array here this is square bracket and it shows you it's an array great so let me go back here and i click on okay and let's save and test it so i click on test i'll perform the trigger action click on test run flow done so now if i go to initialize invoices you will see i have an array of objects fantastic now let's go back and continue because now i need to convert this array to something like a csv structure but before doing that i need to have a simplified version of this json array to do that i'm using select so let me click on edit i don't need this guy anymore i click on new step and i use select select can convert one json format to another json so when i want to pick the values from the initialized invoices i click on here and i get the values from invoices remember this one was the array select and now i need to map it for mapping i can simply provide the field and values from the previous arrays which is actually navigated through for every single item and i can bring the values here let's start with one of them if i go back to my excel sheet the first element that i want here is going to be type so let me go back to flow so i say type and for the value i need to get the type of the current item let me take a look here so the current item is going to be something like here and the type is what i need for every current item that is being processed by this select so here i click here i click on expression because i have to write expression here so i would say item we put the brackets dot type i click ok done so the first element is added here you can see type is added second one is invoice id that i need to add here so great let me copy it here now i want to show you my skills in copying and pasting face i go back here for the second one i need to get this invoice id so basically i'm looking for a property called invoice id of the current item which is exactly at the root level of this invoice object i go back here just like before expression and i say item which refers to the current item that is being processed in json expressions dot and i put invoice id here okay i'm good is that anything else invoice number maybe okay let's do this one too i go back here invoice number and if i go back again to expression i can get the current item which again i refer to that by item dot invoice number just to make sure that the invoice number matches the field that i have in the actual json great so so on and so forth i really don't want to dig into the details of it you can continue with the other fields let me just save it and test it and see what we get out of this select test run flow done so basically at the moment if i expand the select the output is an array that contains type invoice id invoice number and everything goes to the end fantastic now how can i convert it to csv piece of cake so close this select new step and i search for csv par csv is encoding this is not what we need but create csv table i can simply click on it and i can say just get it as the output from select output from select done save and i can test it run flow done so this csv is what i got type invoice id boost number and blah blah blah i can simply save it in a simple file and i have a csv representation of all those records but let's go back to the sweet part i examined this json and i realized that for all of them the phone is empty so basically phones is empty let me just put some dummy values here for example i would say one two three comma another element is going to be maybe another number and maybe another number i want to show you how you can concatenate all these elements and add it to the phones field inside that json so i come back here under select oh gosh i gotta click on edit so click on select expand it one thing that i want to pick up now is going to be contact id maybe let me just add them so we can have a reference to them oh i didn't include the invoice number great okay invoice number item click on expression again i call it item just like before dot in voice number i click on ok so invoice number is selected contact id same thing so i look for again on the expression tab i look for item which refers to current item and i say dot come back here contact id is not directly under the object so the invoice object has a property called contact we need to refer to this one first so i come back here and then dot inside contact i have something called contact id which i need to get it here and add it to the other one so now i have the contact id which is inside the contact object let me click ok and we test it and see what we get out of it and then i will take it to the next level test test run flow done so if i expand the create csv table now you see i have the contact id as well so invoice number which is this one and contact id is a weird a grid file great field which doesn't really matter at the moment now i need to do the same thing with phones but phones is not just single string phones is an array that we need to convert it to string now let's see how we can do that i come back here again click on edit i call it contact phone and here i need to get the contact phones so just like before i click on expression i start writing item dot what i'm looking for is under contact so i write contact but under contact i have phones so object contact and i have phones here so i pick that phone here this one returns an object while here i expect a string which is the concatenated values of all the phones to concatenate all the array elements with a separator inside powerautomate we use a function called join i open the bracket so as the first parameter it's expecting a collection or an array comma second parameter is a delimiter let's assume that we want to separate them by forward slash so single quote i put forward slash and i close it so it will be the concatenated phone numbers each one of them is selected by a forwarder slash i can even make it better and include a space in between them so let me click on ok save so let me just take a copy of it completely copy initialize invoices object so i clicked on it let's wait for it to expand so again i select all of it delete and i paste the new values that now it contains the phone numbers and just like before i scroll up to the top of the page close this we are all good and we have phone numbers here oh gosh it takes a lot of time to process this i click on save and let's test it this time test run flow and done now if i expand the create csv table in the output and if i go to the end of it you see for the one record that actually we have a combination of phone numbers this file these values are here and if you want to send it to sharepoint it's very simple just add a new step use create file [Music] and you can create a file directly inside sharepoint let me just pick up one website for example power automate workshop for the folder path i can click on the folder i go to share documents just click on it and i want to put it in the root of shared documents i give it a name i say test invoices dot csv and file content is coming from the create csv table which is the create csv table output save and now we can actually see how it works test continue and run flow done completed now let's go to sharepoint inside sharepoint i click on documents and you will see the test invoices csv and if i click on it you will see all these fields here of course it's not formatted because it's csv but everything is here even the contact id and contact phone for this one single one and that's it and i seriously think this video deserves a like please don't hesitate and push that like button okay seems like we are done but the reality is that no we aren't uh the process of this video was mainly based on the fact that you are quite comfortable working with json and expressions and i try to avoid visual content or visual actions as much as possible and try to use the minimum number of actions to move this forward there is a reason behind it but i really don't want to get there in case you want to see how i would have done that with visual actions like for example pars json or join action or things like that let me know in the comments and maybe in the next video i put that together and i will show you how you can do it with not so much of coding thank you for watching if you are not a subscriber you may consider subscribing so that you won't miss the next video and if you already liked the video thank you so much take care and stay tuned to the next video you

Channel: Alireza Aliabadi

Views: 23,494

Keywords: parse complex json power automate, Power automate, parse JSON, Flow, Power automate select, JSON to csv, Power Automate join, power automate select action, power automate select from array, power automate select value from array, power platform, power automate array, power automate object, power automate csv, power automate json to, power automate json to excel, power automate flow how to parse json, parse complex json, power automate complex json, microsoft flow tutorial

Id: JVDDKsboOks

Length: 20min 42sec (1242 seconds)

Published: Tue Nov 17 2020