Pulling JSON data from a public data API

“It is a capital mistake to theorize before one has data.” ― Arthur Conan Doyle

The Go Code Colorado crew, photo by Allison Daniell of Stellar Propeller Studio
Go Code Colorado photos by Allison Daniell of Stellar Propeller Studio

I had the opportunity recently to participate in Go Code Colorado, an “apps challenge” (think weekend hackathon + startup pitch competition) meant to make public data more accessible. It was an awesome experience and it got me thinking about government/public data, and how to use it and combine it in ways that can help people.

Here’s a quick example of how to pull data from one such public data source,  the Colorado Business Entity Database.   Basically, anyone starting a new business or forming a business entity in Colorado, whether that be a sole proprietership, partnership, LLC, corporation, etc, has to file documents and register their business with the Secretary of State’s office, and that information becomes public data. What we’re going to do in this example is to use state-provided API to pull a list of current businesses in a particular ZIP code, and list out their names, street addresses, and cities.

Getting to the data

So first of all, how do we get our hands on all this sweet, sweet, public data? Well, it depends on what data you are looking for and at what level of government, for example there’s a central clearinghouse of US federal government open data available at data.gov, or you can access US Census data, or weather data from the National Weather Service, or even space science data from NASA.  In this case we are looking for state of Colorado data, so we are going to look at the Colorado Information Marketplace at data.colorado.gov, which is Colorado’s official public data portal.

NOTE: I tried to find a list of the central data hubs for all 50 US states but was unsuccessful. If this doesn’t exist someone should create it!

Before we dive into the code we first need to get the URL that we’re going to pull the data from, also known as the API endpoint. You can find the Colorado Business Entities dataset on the Colorado Information Marketplace website here: data.colorado.gov/Business/Colorado-Business-Entities/4ykn-tg5h. Once there, you can click the “Export” button near the top right to see options for getting the data (see screenshot), then choose the “SODA API” menu the find the endpoint URL (see screenshot). Here’s the URL: http://data.colorado.gov/resource/4ykn-tg5h.json, and you can see the data is being sent to us as JSON.

A bit about JSON

JSON stands for JavaScript Object Notation, and is used by many web APIs these days as a simple, human-readable, easily parsable data interchange format. From json.org: “JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language … These properties make JSON an ideal data-interchange language.” Besides all of that it’s a data format that’s especially well-suited for use in the browser, completely client-side with no server-side processing needed, like what we’re doing in this example. For more about JSON see this MDN article.

Check out the finished demo

See a working example right here. Not every single ZIP code returns results, so if you happen to choose one that doesn’t contain any registered businesses just go ahead and try a different ZIP. We’ll go through the relevant bits of code below, but you can see the full demo code on github.

The HTML

Let’s take a look at the HTML first. It’s pretty straightforward, a <select> element, containing a number of <option>s, each of which contains a ZIP code, the name of a city or town in Colorado, and the county each city is in. Then a <button> that we’ll use to fire off the API access javascript.

<div class="content">
    <h1>Pulling JSON data from an open data API</h1>
    Choose: 
    <select>
        <option>80001 Arvada Jefferson</option>
        <option>80044 Aurora Arapahoe</option>
        <option>80252 Denver Denver</option>
        <option>80727 Eckley Yuma</option>
        <option>80728 Fleming Logan</option>
        <option>80729 Grover Weld</option>
        <option>80938 Colorado Springs El Paso</option>
        <option>81658 Vail Eagle</option>
    </select> 
    <em>(ZIP City County)</em> 
    <button>Let's do this thing!</button>
</div>

The JavaScript

The other key part here is the JavaScript. We’re using jQuery here to make a few things simpler, but because jQuery is just JavaScript there’s no reason you couldn’t write this all in vanilla JS instead. Anyway here’s the full code, check the comments:

(function ($) {
    $('button').on('click', function () {
        // remove resultset if this has already been run
        $('.content ul').remove();
        // add spinner to indicate something is happening
        $('<i class="fa fa-refresh fa-spin"/>').appendTo('body');

        // get selected zip code from selectbox
        var zip = $('select option:selected').text().substring(1, 6);

        // make the AJAX request
        $.getJSON('http://data.colorado.gov/resource/4ykn-tg5h.json?entitystatus=Good%20Standing&principalzipcode=' + zip, function (data) {

            // do all this on success       
            var items = [],
                $ul;

            $.each(data, function (key, val) {
                //iterate through the returned data and build a list
                items.push('<li id="' + key + '"><span class="name">' + val.entityname + '</span><br><span class="addr">' + val.principaladdress1 + '</span> <span class="city">' + val.principalcity + '</span></li>');
            });
            // if no items were returned then add a message to that effect
            if (items.length < 1) {
                items.push('<li>No results for this ZIP code, try again!</li>');
            }

            // remove spinner
            $('.fa-spin').remove();

            // append list to page
            $ul = $('<ul />').appendTo('.content');

            //append list items to list
            $ul.append(items);
        });
    });
}(jQuery));

First the whole thing is wrapped in an IIFE, inside of that everything is wrapped in the callback function of an event handler attached to the button, so that all the code is triggered when the button is clicked. (see jQuery’s .on() for more details)

$('button').on('click', function () {
// everything happens inside of here, when the button is clicked
});

After the button is clicked, there are a couple of things that happen as setup, before we actually get to pulling the data from the API. First we clear the area where the results will live, in case there’s something already there, using jQuery’s .remove(). Then we’ll throw a spinner up there using .appendTo(), so the user knows that something is happening. The reason it looks funny, like an <i> tag, is that I’m using the totally awesome Font Awesome to provide the spinner graphic as an icon font. After that we’ll get the ZIP code of whatever option was chosen in the selectbox by using substring() to pull off the first 5 characters and store them in a variable, aptly named “zip.”

// remove resultset if this has already been run
$('.content ul').remove();

// add spinner to indicate something is happening
$('<i class="fa fa-refresh fa-spin"/>').appendTo('body');

// get selected zip code from selectbox
var zip = $('select option:selected').text().substring(1, 6);

After all that setup is complete, we’re ready to actually pull the data from the API using $.getJSON(), which looks like this:

// make the AJAX request
$.getJSON('http://data.colorado.gov/resource/4ykn-tg5h.json?entitystatus=Good%20Standing&principalzipcode=' + zip, function (data) {

// everything inside here happens once the data is successfully returned        

});

The first thing passed into $.getJSON() is our data URL from earlier, with 2 parameters tacked onto the end of the URL: entityStatus=Good%20Standing, which limits the results to just active businesses in good standing, and principalZipCode=' + zip which tells the API that we want results only from the selected ZIP code. The second argument passed into $.getJSON() is a callback function, to be executed after the data is received.

Now the last big section of code is what’s nested inside of the success callback function of $.getJSON(). This is everything that happens if and when the API has sent us the data we asked for.

// do all this on success       
var items = [],
            $ul;
            
$.each(data, function (key, val) {

    //iterate through the returned data and build a list
    items.push('<li id="' + key + '"><span class="name">' + val.entityname + '</span><br><span class="addr">' + val.principaladdress1 + '</span> <span class="city">' + val.principalcity + '</span></li>');

});
            
// if no items were returned then add a message to that effect
if (items.length < 1) {
    items.push('<li>No results for this ZIP code, try again!</li>');
}
            
// remove spinner
$('.fa-spin').remove();
            
// append list to page
$ul = $('<ul />').appendTo('.content');
            
//append list items to list
$ul.append(items);

After declaring a couple of variables there’s a jQuery $.each() that we will pass the returned data into. $.each() is a jQuery function that can be used to iterate over objects and arrays, so we will use it to iterate through the business data and for each result put the business name and address into an <li>. Then we add that <li> to the items array that was declared earlier. This all happens on line 9 above.

After that there’s a check to see if, by chance, no results were returned (line 14), then we remove the spinner (line 19), add a <ul> to the page (line 22), and finally take the whole list of businesses, now stored in items, and insert them inside of the new <ul>.

Taking it further

That’s a pretty basic example of what you can do with an open data API, performing a very simple search and outputting the list of results to a web page. But once you’ve got the basics down, and can pull the data into your page as an array or object, it opens up a number of other things you can do with the data, like map it, chart it, make a cool visualization, or combine it in interesting ways with other open data.

Resources

Live demo – http://leemark.github.io/co-data-pull/
Full code on github – https://github.com/leemark/co-data-pull
Socrata Open Data API – http://beta.dev.socrata.com/
Introducing JSON – http://www.json.org/
MDN JSON docs – https://developer.mozilla.org/en-US/docs/JSON
Data.gov – http://data.gov/

16 Replies to “Pulling JSON data from a public data API”

  1. I haven’t been able to make the Colorado zip code JSONP example work for a name search based off of zip codes. One concern is of course to make it search not for the exact name sought but for names that are similar to the name searched. Is there an quick way to modify the above code to make this happen?

  2. I haven’t been able to make the Colorado zip code JSONP example work for a name search from a name search that is based off a sought out name instead of based off of zip codes … (sorry for the confusion). One concern ….. (please continue with previous post)

  3. Thank you for the coding. I got it to work for name searches except the problem is that if the name does not match exactly I get a “Noncompliant ” error as a response along with the inputed search string. Is there a way to structure it so that the search string gets close matches without an error?

  4. A useful trick is to turn the data from a JSON feed into a CSV file. You can do this by pasting the URL into https://json-csv.com…CSV is useful because you can open the file up in Excel to work with the data.

  5. I think you have a bug in var zip = $(‘select option:selected’).text().substring(1, 6);

    where it should be .substring(0, 6);

    as I had a problem fetching it out, because it was always no result after I tried to output zip value and it did output zip code as 0001 instead of 80001

    Very helpful tutorial anyway!

  6. I will like to know how this work, I expect the same origin policy to apply here and the resource should not be loaded since there is no callback.

    Can someone explain how this worked?

  7. I am using Parse website (cloud database website) and I want to use JSON to get data from it and use it to in my d3.js code. I am trying to make line graph visualization using d3.js but I just know how to extract data from a CSV file. I really need urgent help. I tried working on it but getting all the pieces to work together is really confusing.

    Any help would be highly appreciated. I need it urgently.

    Thanks a lot.

  8. TASK:
    Make a Javascript Query that can pull out the below data from any skillshare profile teaching page [e.g. https://www.skillshare.com/everytuesday/teaching ] . You can use Javascript / excel macros / VBA Code / any other functions.
    The data that needs to be pulled out are:
    1. No. of followers
    2. Different course titles, number of students in each course

    For example:
    Input: Skillshare profile teaching page [e.g. https://www.skillshare.com/everytuesday ]
    Then, you press a button “EXTRACT DATA”.
    It will scrape the data and give outputs as:
    a) No. of followers
    b) Course title
    c) Number of students in the course

    NOTE: The data should not be static data. If I input a new profile link, all the data should be extracted from the new page and populated in the respective columns.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.