Getting JSON data with PowerShell

While we often work with tabular data, there are many times we’re faced with JSON data.

JSON, short for JavaScript Object Notation, is a relatively concise way of representing objects as strings. Over the past few decades, we’ve seen JSON data become more popular than traditional more structured data structures, such as XML. In fact, JSON is so popular that when I work with external APIs, I find that JSON is the most common format they send back.

If you’ve not seen JSON before, the following is a sample JSON response provided by the ICanHazDadJoke.com website, a meme site designed to provide random “dad jokes” to visitors.

Here’s a sample dad joke in the JSON format provided by the website:

{
  "id": "D5wAA5o4TCd",
  "joke": "What do you call a careful wolf? Aware wolf.",
  "status": 200
}

This JSON object has three properties – id, joke, and status. The id and joke properties both have string values, while status is numeric and appears to represent a 200 OK REST status code. This object represents a single random joke from the website and, sadly, represents my sense of humor somewhat accurately.

While dad jokes aren’t a necessity in life (depending on who you ask), working with APIs that return JSON is nearly unavoidable. As you perform your data science and software engineering experiments, it’s almost certain that you’ll need to call APIs that return JSON data.

Let’s take a look at how to fetch JSON data using PowerShell, by making an HTTP GET call to ICanHazDadJoke.com to get another dad joke.

We’ll start with a PowerShell code cell to create the headers for our GET request:

$obj =
  "System.Collections.Generic.Dictionary[[String],[String]]"
$headers = New-Object $obj
$headers.Add("Accept", "application/json")
$headers

Since we end the cell with the $headers variable, Polyglot Notebooks will display its value below the cell:

Key    Value
---    -----
Accept application/json

You don’t always need to add custom headers when making an HTTP GET call to an API, but this site requires it in order to get back a JSON result instead of HTML.

Next, we’ll make the actual GET request to the API:

$url = "https://icanhazdadjoke.com"
$response = Invoke-RestMethod -Uri $url `
    -Method Get -Headers $headers
$response

This uses the headers we defined earlier to make the GET request, stores the result of the call in $response, and displays the current value of $response.

Each time you make a GET request to this API, it returns a different joke. Figure 3.8 shows a sample of a rather “punny” response about parenthood:

Figure 3.8 – A regrettably bad joke shown in the API result

Here, $response is a PSObject, but we want to convert it to the JSON format so that it’s easier to share with other language kernels.

To handle the conversion, we can send the response to the ConvertTo-Json cmdlet, converting the PSObject to a string of JSON text representing the object.

We do this with the following PowerShell code cell:

$json = $response | ConvertTo-Json
$json

This converts the value to JSON and stores it in our appropriately named $json variable. The value is then displayed as follows:

{
  "id": "fiyPR7wPZDd",
  "joke": "When does a joke become a dad joke? When it becomes apparent.",
  "status": 200
}

As you can see, we now have JSON representing our joke (which does not get better with repetition).

We could now work with the JSON data in PowerShell, or we could create a variable in the C# kernel set to the current value of $json in the PowerShell kernel, by running the following code in a C# code cell:

#!set --value @pwsh:json --name json

With the json variable in the C# kernel containing the JSON, we can then deserialize it into a .NET object.

First, let’s define a class to represent the object with a C# code cell:

public class DadJoke
{
    public string Id {get; set;}
    public string Joke {get; set;}
    public int Status {get; set;}
}

Next, we’ll perform case-insensitive deserialization from the json string using System.Text.Json, with another C# code cell:

using System.Text.Json;
JsonSerializerOptions options = new();
options.PropertyNameCaseInsensitive = true;
DadJoke joke = JsonSerializer.Deserialize<DadJoke>(json, options);
joke

When this cell runs, we can see that the results of our PowerShell API call are now available as an object in the C# kernel, as shown in Figure 3.9:

Figure 3.9 – The joke interpreted as an object

You may have noticed that this chapter discussed both Newtonsoft.Json and System.Text.Json. Both are valid and powerful ways of serializing and deserializing objects in .NET. While Newtonsoft.Json is the most popular .NET package on NuGet.org, with 4.6 billion downloads at the time of writing, Microsoft’s newer System.Text.Json is equally powerful. Pick whichever approach you and your team prefer, and use it in your projects.

Before we move on to other data sources, let’s talk briefly about how you can take objects, such as our joke variable here, and create a new DataFrame out of them.