PowerShell Tutorials : Working with JSON

hi and welcome to this powershell tutorial this is actually a video request from david morgan and he was actually in the stream on saturday may 28th i believe uh yes may 28th and his question was how to process uh certain json outputs that we get we often get json from api calls and that was his example so he actually provided me with an example of the json that would be provided back from the api that he's working with i couldn't get direct access to it but i really wanted to use his example in this as well just because it's a little bit more complex than some of the apis that i have access to uh it might just be useful um just generally for how to process json whether that be json files or returns from api calls since the majority of the time you're going to be getting back json from those api calls so what we're going to be looking at here is this example json file and so this is the response that he would get from his api so we have values here which are actually the rows of data in the database that he's querying the status of the call the columns so these are the column names in the database and then the number of events that came back and omitted events so we're going to go ahead and look at how we can see all these different values how we would work with json in powershell so this will be a little bit more of a deep dive of just working with json in powershell and i'm actually going to go over two ways to actually do what he wanted so what he wanted to do was rebuild this database basically in a powershell object to be able to query it easily and just be able to get more information so there's actually two ways to do this uh there's the way that you're gonna see if you go look at the video on demand on twitch i did it one way but actually further thinking it through i found a different way to do this and it's quite a lot easier uh so david if you're watching this i would probably actually go this route it's a little bit easier uh it's not as complex and actually the amount of efficiency is a lot higher in this one uh so if you're ever dealing with like large data sets this would probably be beneficial all right so let's actually go ahead and get started so the first thing we're going to do is get our response back so this would typically be through an api call of course but in our case since we are using an example i'm actually just going to be fetching our json from the file so let's just go ahead and fetch that here so i'm just going to run that and here is our json that we get back so we're just gonna make sure that we get it back correctly and that looks pretty good so the first step as soon as you get json back in powershell i would first transfer it over to a powershell object so we're going to go ahead and we're going to create a variable called response object and we're going to make that equal to response pipe to convert convert from json and if we run that here and we look at our response object we are going to actually see everything that we saw in our json but we are now going to see it in a nice powershell object format so we have values status columns matching events and omitted events and what this lets us do it really lets us use that dot notation that powershell is known for so if we want to see all the values which is actually the rows we get all the rows here if we want to look at the number of matching events we just do matching events and we can get the number of matching events which is uh free perfect so what we want to do is we want to rebuild that database so the first thing that we're going to want to do is of course grab the rows and grab the columns because we need those values in order to rebuild it so we're going to grab the rows and we're going to make that equal to response object dot values now the reason why i know it's rows and not the exact values is actually if we do uh response object dot values dot count we actually see that we get three but when we just look at the values it does look like we have six values but it's actually only three because each row is its own um cell in that in that array as we can see in the json they are all self-contained it's an array of arrays so it's just a nested array so we know that each value or each entry in that array is actually an entire row and then we have our column so we're going to make that equal to response object dot columns and that we know is just the columns and they have a property of name so we can run that here and then the first method uh which is actually going to be the method that i don't really recommend uh but it does work it's a little bit more complicated and probably has a little bit more overhead on it as well but first we're going to create our export variable and we're going to make that equal to a arraylist to store our data in so we're going to make that system.collection and we're going to make that an empty arraylist and then what we need to do is we need to go through each row and add each row for each column so the way that i would do this is first start by looping through each row so we're going to do four each row in uh so uh so for each row index in zero dot dot rows dot count minus one and we're going to open and close curly brackets so what this is going to do if we actually just run this really quickly we have row index so if we run this we are going to see that we get 0 1 and 2. so we know that the arrays start at zero and then the account is as if you were starting literally from one so we have a count of three so we need to go from zero to two in order to get all those items and then what we're going to be doing is we're going to be creating an entry right here so that's going to be a new object and that's going to be a type the autocomplete did not like me there so that's going to be a type name of ps object and then what we're going to want to do is now we are going to want to loop through each column in order to create that object with the two columns where the two properties in our case and we're going to be assigning the row values in there so we're going to do a for each column index in zero dot dot and then a variable wrapper and then columns dot count minus one for the exact same reason as we did above just to get each column and then what we're going to do is we're going to do an add member the input object is going to be entry and then the member type member type is going to be a note property the name we are going to set that to columns and then we're going to make that a column index so we're referencing the column index that we are at and then dot name and then we're going to go ahead and put in the value and we're going to put this value as rows and we're going to do that as um it's going to be rows index and then column index we're going to do a row index because as we know it is uh a nested array here so we're going to do rows row index and then the column index perfect that should be good and then after that or each here we are just going to go ahead and do a export dot add and we're going to add our entry just fix this here entry and then we are going to actually just display our export variable so let's just run this real quick and this should work here and i forgot to put the dollar sign in front of the columns all right so there it is there's that table that we built so we had our two columns ipn server and then we have each of our rows so the 169.198.1.1 not exists 8.8.8.8 is google 1.1.1.1 is cloudflare.com and if we just go to our example we can see that that aren't like those are our rows and we can see that those were our column names so uh and this is completely independent of the matching events now you could do instead of the rows dot count minus one you could do the matching events minus one uh but this would mean you're definitely counting on that api call to give you that correct amount of matching events and just because i couldn't really validate to make sure that the matching events always match the number of rows i'd rather just use the actual number of values that we get back and use that but as we can see here if we actually just go ahead now and add a array in here and we just do 0.0.0.0 which is a very nice ip address and we just say uh youtube test is the server name so if we save that here we just go ahead and we run that we get our table just fine so it definitely works properly with any number of rows that we have in there so that is the first way to do it and like i said this was the way that i initially thought of it which the first way that you think of something might not always be the best and that's why i'm actually giving both ways giving the way that you guys will see on the twitch live stream if you guys go back to that video on demand but also i'm going to give you a slightly improved version that i thought about shortly after the twitch live stream i wanted to make sure to include it in this video this way you guys get both solutions and depending on the scenario that you guys have like maybe you guys don't have to rebuild the database in this case my next example probably won't be as useful but i just want to show all the different things that you could do with json objects so in order to rebuild that database we can actually use a another type of object and that object instead of being an arraylist is actually going to be a data table which probably already makes more sense for people to use but we're going to do an export and we're just going to say export table is going to be this variable name and we're going to set that to a new object and we're going to set that to system dot data dot data table now we have seen these before but we've mostly used them in our gui example we've actually never used them for just coding scripts to display data but i figured that this was actually a very good time to use those so what we're going to be doing now with this data table is we're going to be adding our columns so what we're going to first do is we're going to do export table dot columns dot add and we're going to add a range of columns since we have an array of our columns we can add a range and our range is going to be columns dot name because we know that we actually have a name since our columns if our columns variable isn't just the name of the columns although it is it's actually an object and it's got a property of names so we just want to make sure that we just pass in the names because if we just do columns.name we can see that we just get a nice array of just the names and that's what it's really expecting so we have columns name here so if we actually just run this right away and we look at what is inside the export table we're gonna see nothing really shows up because we don't have an entry in there yet but what we can do now is we can actually do a four each and we're gonna do for each row in rows so this is a little bit more what we're used to seeing we can actually do export table dot rows dot add and we can add our row all right and then let's just go ahead and let's just output that export table so if we actually just run this code here i think i might uh so there you are so one thing about the rose dot add you're actually going to get an output every time that you add a row so what i would honestly do is just do a square bracket void in front here and a square bracket void in front here as well just to avoid any unnecessary outputs to the screen and now if we rerun it you're going to see that we get a very very nice output of our table that looks exactly like what we did up here but much much more simplified so this is a lot easier to read a lot easier to understand going back to your project compared to all these different indexes using all these different counts creating an object adding the object to the arraylist here it's just very very straightforward you're creating a data table you're adding the columns you're going through each rows that you're getting back and you're adding the rows in the data table now the only important thing to note that this is definitely taking into consideration of course this api call which has the column names in the same order that were returning the values of course if they weren't in the same order this would probably be a lot more difficult to do i would find it very weird that an api would give you the columns and the values in different orders um but this is pretty much powershell and exploring different uh json so really the main takeaway here and i know i've mentioned it before already in my powershell intermediate series but when you're working with json always do the convert from json you're going to get a powershell object and then you can go back to your powershell scripting methods and use the dot notation and then this is just a very cool way to convert what you're getting back into some sort of usable data table uh to easier filter through your results and i mean you don't even have to be querying a database to want to build a database from your results you might have some data in some of your api responses that would make sense to create objects for each entry that you're getting back and create powershell objects for each one and then be able to search through those objects very quickly with a where clause and then output those two files or insert those into databases through the sql module so that is pretty much it for this video so again um this was definitely a collaboration on twitch uh so i would definitely recommend if you can make it out to those uh saturdays it is between 1pm and 4pm eastern standard times i'm usually coding in unity lately on those because i'm trying to learn uh just some game development and learn the unity engine but if you guys have any questions regarding to powershell or anything that we've really done on this channel i will stop what i'm doing and i will answer try to answer you directly on that live stream if i can and you know that kind of just works um my brain a little bit more and it kind of shows you the process that i go through to solve a problem and that itself is probably a lot more useful than some of these tutorials um just because you can see the thought process and the problem breakdown that i go through so always make sure to check that out i will have a link in the description down below for my twitch channel again it's every saturday from 1 to 4 p.m eastern standard time uh so if you guys have any comments or questions about this video or any other topic uh powershell c sharp anything programming related or systems related let me know in the comments below if i have the answer for you and it's a little bit more specific i'll answer you directly in your uh comment if i find that could benefit a lot of people i will make a video just like this one and this way you can see it and um the whole uh subscriber base and viewers can benefit from it as well so make sure to hit that subscribe button hit that like button and hit that notification bell to be notified when that next video comes out and i will see you guys on the next video

Channel: JackedProgrammer

Views: 6,068

Keywords: powershell basics, powershell, windows powershell, programming, coding, scripting, powershell scripting, powershell scripting tutorial, powershell tutorial, data grid, data grid powershell, json tutorial, json powershell, json, parse json powershell, parse json, working with json, how to, powershell api responses, api, json tutorial powershell, data sets powershell, data sets, data

Id: uz7M5cmUdX4

Length: 20min 9sec (1209 seconds)

Published: Thu Jun 16 2022