How To Parse a JSON File Using PowerShell (Video Tutorial)

Related: How To Filter SQL Server Data in Windows PowerShell

The following transcript has been lightly edited for clarity.

Transcript:

Brien Posey: One of the more critical and yet often underappreciated skills with regard to PowerShell is knowing how to parse a JSON file. There are a couple of reasons why this is so critical.

First, if you query an API, the results of that query are often going to be returned in JSON format. So, it's important to know how to handle the output resulting from that API query.

Now, even if you're not building full-blown applications in PowerShell, and you never touch an API, there is another reason why it's so important to know how to handle a JSON file. The reason is simple: PowerShell is often used as a Windows management tool, and very often configuration files for applications, web apps, and things like that are stored in JSON format. So, it's important to know how to go into a JSON file and extract a value so that you can take action based on that value.

That's similar to what I have on screen right now. What I've got here is an example JSON file. This is what a configuration file might typically look like. Incidentally, this is just an example file. And I got this example file from this URL right here: www.json.org/example.html. There are several different JSON files that you can download and experiment with to your heart's content.

Let's suppose that we wanted to create a PowerShell script that would go in and look at an image’s file name. Well, if you look at this particular JSON file, you can see that we have a file name stored right here. It's Images/Sun.png. So, let's suppose that we had a PowerShell script, and, for whatever reason, we needed to extract this file name and be able to alter it in some way. How might we go about doing that? Well, it's actually simple to do. In fact, you only need a couple of lines of code.

Here's my script. You can see that the first line is nothing more than just a comment showing where I got the example JSON file from:

# Sample data from: https://json.org/example.html

The second line is where we get into the heart of the script:

$Data=Get-Content ‘C:\Scripts\JSON.TXT’ | ConvertFrom-JSON 

The first thing that I'm doing is I'm creating a variable called $Data. Then I'm setting this variable equal to Get-Content, which is a way that you can tell PowerShell that you want to read a file. The file that I'm reading is C:\Scripts\JSON.TXT. And if you notice, my example JSON file was named JSON.TXT.

Once I've read that file into the $Data variable, the next thing that I'm doing is running ConvertFrom-JSON. That's a native PowerShell cmdlet that is used to convert JSON data into a PowerShell object. 

The next line of code that I have is simply $Data. The only thing that that line of code does is display the contents of the $Data variable on the screen just so that you can see what it looks like.

From there, we have two more lines of code, and I'll get into those in just a second. For right now, I'm going to comment out these lines of code and save the file. Let's go ahead and run the PowerShell script.

As it stands right now, we're only going to be executing these two lines of code. Let's look at what they do. When I execute the script, you can see that we have a single object that's returned, and that object is called widget. Then within that object, we have an array:

Widget 
------ 
{debug=on; window=; image=; text=}

You can see debug equals on. Then, we have window equals, and there's no value. Image equals, and again, no value. And text equals, and again, there's no value.

Let's refer back to our JSON file. You can see that at the very top, we have “widget,” and that just happens to be the name of the object that was returned by PowerShell. Then, if we look underneath widget, we can see that the JSON file is constructed hierarchically. And so, we have various nodes. We have debug, window, image, and text. Then if you look at the PowerShell output, the array contains debug, window, image, and text. So, PowerShell was returning the top-level elements of this hierarchy.

Now, if you're wondering why we have debug=on but don't have a value shown for window, image, or text, the reason for that has to do with the contents of the file. If you look at the file, you can see that debug has a value associated with it, and that value is “on” there. On the other hand, window doesn't have a value associated with it. Image and text don’t either. The one thing that makes debug different from window, image, and text is that window, image, and text contain other items beneath them within the hierarchy.

What we're trying to do is get to the image source, and more specifically its value, which is the file name of this image. So, how do we go about doing that? Well, let's go back to our script. Right now, what we've done is read in our JSON file and performed a conversion on that JSON file to turn it into a PowerShell object. Then, we displayed that object using $Data.

Now, if we want to display a specific item within the $Data variable, which contains the entire JSON file formatted as a PowerShell object, then we have to do things just a little bit differently. So, what I'm going to do is I'm going to comment out the $Data line. Then, I'm going to remove the comment symbols that are put in the next two lines of code. So, the next line of code that we have creates a variable called $Image. And we're pointing that image at a very specific spot within the data array.

$Image=@($Data.widget.Image.Src) 
$Image

So, you can see the @ symbol after the = sign. In PowerShell, anytime you see an @ sign after the = sign, it indicates that you're working with an array.

What we're doing is referencing a very specific location within that array. Specifically, we're referencing $Data. That's the name of our variable that corresponds to this PowerShell object. So, we're looking inside of the $Data variable, and then we're referencing widget.Image.Src.

So, with that in mind, let's look at our JSON file. Now, as you'll recall, “widget” was at the top of our hierarchy, and we had several different elements beneath it. The next level down, working toward the item that we're trying to get is “image.” If you look at our script, we're referencing $Data, which is the PowerShell object that we've created; then .widget, which is the top level of the hierarchy within the JSON file; then .Image, which is the next level beneath that; and then the next thing that we have is .Src.

.Src just happens to be the item that has the value we're interested in. If you look back at the JSON file, you can see that “src” is indeed the name of an item within this hierarchy. So, what we're doing with that line is retrieving the value that's associated with src, and that value is Images/Sun.png – the specific item that we're trying to retrieve. And then the last line of code simply displays the contents of the $Image variable.

So, let me go ahead and save the script. Let's rerun it. Now you can see that the script returns the item that we're interested in – specifically Images/Sun.png, or the file name associated with the image defined within this fictitious configuration file.

So, that's how you parse a JSON file using PowerShell.