Search code examples
jsonjoinyql

Is it possible to get YQL to return multiple JSON results from a site in a single request?

I'm currently using this YQL (Yahoo Query Language) query to convert the JSON from a site to JSONP:

select * from json where url="http://airportcode.riobard.com/airport/ABC?fmt=JSON"

ABC is the parameter.

So I know YQL is designed to be similar to SQL and I think has some feature that lets you do joins, but I'm not sure if it would be possible to use these features to combine several queries to this airportcode site into a single request. (The site doesn't support this and the site's owner hasn't replied to my email query.)

Basically I want a YQL query that will get not just results for ABC but for ABC, XYZ, FOO, in one call. Is it possible?

Solution

  • You could also make good use of the uritemplate table to convert the airport codes into a group of URLs which can then be fed to the json table.

    Airport Codes to URLs

    The uritemplate table takes a "template" and any number of values to populate that template with. (See also, URI Template draft).

    In this case the needs are simple, just inject the airport code into the path at the appropriate place.

    select * from uritemplate where
template="http://airportcode.riobard.com/airport/{airport}?fmt=JSON"
and airport in ("SFO", "LAX", "LHR")

    (Try this query in the YQL console)

    This gives the following result (diagnostics information removed for simplicity).

    result of query

    Getting JSON results from these URLs

    Now that we have a list of URLs, it is simple enough to use the json table to retrieve the JSON for each of them by using a sub-select. In fact we already used a sub-select above, recognisable by the in (…) syntax.

    select * from json where url in (
    select url from uritemplate 
    where template="http://airportcode.riobard.com/airport/{airport}?fmt=JSON" 
    and airport in ("SFO", "LAX", "LHR")
)

    (Try this query in the YQL console)

    The result this time looks like.

    query result

    Easy querying

    I am a big fan of YQL's query aliases (and the associated variable substitution like @var) which can provide neat endpoints for you to access without the need to embed the YQL query into the URL. The link above details how to create them, and the end result is that you could have a YQL query such as the one below.

    select * from json where url in (
    select url from uritemplate where 
    template="http://airportcode.riobard.com/airport/{airport}?fmt=JSON" 
    and airport in (@ap)
)

    With a query alias (I've called it airports) the query can be accessible via a URL like the one below, and have the ability to easily change or add more/fewer airport codes easily.

    http://query.yahooapis.com/v1/public/yql/peter/airports?ap=SFO&ap=LHR&ap=BOB

    (Go there. Note: I might delete this alias without notice.)

    Reading: