LINQ to General Election Part 1 – LINQ to JSON

I discovered this week that the Guardian newspaper is making the UK General Election results available in JSON format. Their politics API gives access to all kinds of results, party and candidate information.

What I wanted to do was to be able to quickly and easily perform some of my own queries on the election results. Having not worked with JSON, I needed something that could parse it and allow me to run LINQ queries on it. My search led me to Json.NET, which is a very easy to use library that allows you to parse JSON into an object that you can run LINQ queries against.

Visualising JSON

When working with JSON, you need to have some kind of tool that will let you explore the structure of the JSON data. I found a handy utility called JsonViewer that parses it into a tree format. Alternatively, you can use this online JSON formatter.

Parsing JSON

The first step is to download the JSON and parse it into a JObject:

string url = "http://www.guardian.co.uk/politics/api/general-election/2010/results/json";
WebClient wc = new WebClient();
string json = wc.DownloadString(url); 
JObject o = JObject.Parse(json);

LINQ Queries

Now it is straightfoward to write queries against the object. Notice that you can use Children() to select all the children under a specific node (useful for arrays).

var unpopularWinners = from winningMp in o["results"]["called-constituencies"].Children()["result"]["winning-mp"]
    where (decimal)winningMp["votes-as-percentage"] < (100 / 3M)
    orderby (decimal)winningMp["votes-as-percentage"] ascending
    select winningMp;

To get the values out of the instances of JToken returned, you have to remember to cast them to the appropriate type (e.g. int, string, decimal):

Console.WriteLine("Unpopular Winners:");
foreach (var winner in unpopularWinners)
{
    Console.WriteLine("{0:F1}% {1} ({2})", (decimal)winner["votes-as-percentage"], (string)winner["name"], (string)winner["party"]["name"]);
}

LINQ to JSON seems to support the full range of LINQ operations, including grouping:

var seatsPerParty = from winningMp in o["results"]["called-constituencies"].Children()["result"]["winning-mp"]
    group winningMp by (string)winningMp["party"]["name"] into g
    orderby g.Count() descending
    select new { Party = g.Key, Seats = g.Count(), TotalVotes = g.Sum(c => (int)c["votes-as-quantity"]) 
};

Unfortunately, the Guardian do not provide one JSON file that contains the votes for all candidates, although it looks like you can get that information if you follow another link for each one. However, they did make the full results available in a Google spreadsheet, so I plan to follow up shortly with another post on how to perform LINQ queries against Excel data.