Understanding and Exploiting JSON Web Services Data with PowerShell

Last month, in "Getting Started with REST and PowerShell," I showed you how to use PowerShell’s Invoke-WebRequest to query a RESTful service that reported the height of a river in near-real time. That let me introduce accessing RESTful services with Invoke-WebRequest and then using Xpath to extract the one useful nugget of data from the sea of XML returned by the service. This month, I'll show you Bing's RESTful geolocation service, which delivers its data not in XML but in something called JavaScript Object Notation (JSON) and works best with Invoke-WebRequest's sibling cmdlet, Invoke-RestMethod.

Bing's geolocation service will convert latitude/longitude pairs to addresses, and vice versa, via a RESTful interface. Microsoft lets you query Bing up to 125,000 times per for free, but only if you've acquired a free key at "Creating a Bing Maps Account." (You need only log on with your Microsoft account.) Keys look like P8xPNZEHQTY9fKoDRw0NyOjysoCN3zV-Tbh-AEQBNkRSk8fsPg9916a9gOL7cQ, but I’ll use BingKey in my examples here. You can often conveniently try a RESTful Uniform Resource Identifier (URI) from a browser, so let's start by asking for the city, county, and state associated with ZIP code 12345 with this URI:

http://dev.virtualearth.net/REST/v1/Locations?q=12345,%20USA&key=BingKey

Try it, and you’ll get one very ugly, long line of JSON-formatted text, like this:

Here, locality, adminDistrict2, adminDistrict, and countryRegion clearly mean city, county, state, and country, respectively. Try replacing 12345 in that URI with any other five-digit value, and it'll either return only countryRegion (which means Bing couldn't find the answer—ZIP code lookups are apparently not straightforward), or values for that ZIP code's city, state, and so on. 

That long line of JSON is intended to be a text-based way of representing data objects in much the same way that we’ve seen XML do before. Although you needn’t learn to parse or create XML or JSON, at least a small familiarity with their formats can be useful, so let's take a look at a simple data object and then see it represented in XML and JSON. Consider this one:

I've called the object Creatures. Inside Creatures are three "animal" objects. Each has an attribute called type, and I've used fish, amphibians, and mammals for my objects. Note that type means nothing to XML or JSON; it's just a word I chose. Class might technically be a better choice from a Linnaean point of view. Inside each animal object, there are two objects: blood type (warm or cold) and the number of chambers in the animal’s heart. You could represent this collection of data in XML like this:

Compare that to how we’d represent our Creatures hierarchy in JSON:

They both get the job done, but in different ways. A short look will probably show that JSON defines objects using pairs of curly braces (“{}”), arrays with square brackets (“[]”), and an array's elements (our three animals, in this case) as separated by commas.

Unfortunately, however, real-world Web services actually deliver JSON as one long line of text, not something like the “prettyprinted” version you saw in my Creatures example. That can make finding your desired data a bit tough unless you’ve got some help, and I strongly recommend help in the form of a free Chrome plug-in called JSON Formatter, or JSONView for Firefox users. (Internet Explorer unfortunately seems to lack a prettyprinter, so I don't use IE with JSON much.)

The prettyprinting is important because it displays the hierarchy, and that's essential to extracting just the data we want. (PowerShell's JSON support doesn't have an Xpath-like thing.) Here's an excerpt of the prettyprinted version of the ZIP=12345 output:

Viewed here, it's simple to see that an object named resourceSets contains an object named resources, which contains an object named address, and that contains our desired adminDistrict, adminDistrict2, and locality objects. Were this a PowerShell object, we'd be able to grab locality with a statement like this:

$somevariable.resourceSets.resources.address.locality

But we've got a JSON object, not a PowerShell object, and that's where Invoke-RestMethod comes in. It's basically a clone of Invoke-WebRequest, except it understands and expects incoming JSON objects, and it automatically delivers them as a PowerShell object, like so:

$URI = " http://dev.virtualearth.net/REST/v1/Locations?q=12345,%20USA&key=BingKey"

$Result = Invoke-RestMethod $URI

Now the JSON objects live in $Result as a PowerShell object. To see the Locality, just type

$Result.resourceSets.resources.address.locality

In sum, then, when you find that your Web service delivers results in JSON, look at the prettyprinted JSON results to determine the object's hierarchy and where your desired data lives in that hierarchy, use Invoke-RestMethod to get the JSON result converted to a PowerShell object, and finally employ PowerShell's dot notation to extract the desired item or items. 

Next month, more REST! See you then.