The annoying thing about YQL’s JSON output and the reason for it

A few days ago, Simon Willison vented on Twitter about one of the things that annoyed me about YQL, too:

The interesting thing about this is that it is not a YQL bug – instead it is simply a problem of generic code and data conversion.

The problem with YQL JSON results

Let’s have an example. If you use YQL to get for example the geo information about “London” you get several results in XML:

select * from geo.places where text=”london”:

If you use JSON as the output format this becomes an array:

Cool, so you can loop over query.results.place, right? No, cause when there is only one result, we have a different situation. Instead of being an array, place becomes an object:

select * from geo.places where text=”london,uk”:

This is what ailed Simon as it means you need to write a function that works around that issue. For example in the Geoplanet Explorer, I use a render() function to show each place result and I use the following to work around the JSON output issue:

function renderlist($p){
if(sizeof($p)>1){
foreach($p as $pp){
$o .= render($pp);
}
} else{
$o = render($p);
}
return $o;
}

In JavaScript you’d have to check the length and either show the results directly or loop over the results.

Generic conversion vs. creating a single API

This is a problem when you convert XML to JSON - as the resulting XML from the GeoPlanet API doesn’t have a places element with place in it but instead repeats the place element for every result the JSON parser must make a decision: if it is only one element, this becomes a property of the results object. If there are more than one place element it becomes a property that is an array (as you cannot repeat the same property name).

If you build your own API and you know the format of the different results you can force this to be an array even when there is only one result. If you do not know the XML schema there is no way of assuming what should be an array and what should not be an array. So the generic solution for XML to JSON conversion of an unknown XML with 1 to n results would be to always create an array. That way you couldn’t get to place.admin1.code for example but you’d always have to do place[0].admin1[0].code[0] – not really a sensible thing to do.

As YQL is an open system and the XML results are provided by anyone in their open table definition the only way around I could think of is to tell YQL in the table that some elements should always be forced to become arrays.

Do you have a solution?

Tags: conversion, JSON, xml, yql