The JSON response is perfectly suited to pass directly in to the queryNew method

Work With Remote API JSON Data in ColdFusion 10 Queries

Yesterday I blogged about the language enhancements in ColdFusion 10 and the ColdFusion 9.0.2 update that allow you populate a new query during initialization.

This got me to thinking about how much easier this can make querying and filtering data from a remote API request.

In Action

In this simple example we’ll make a request to the Twitter search API to return the last ten results for our search criteria, ‘ColdFusion’.

As we’re receiving JSON from the response, we can easily deserialize it into a format that we can then pass directly into the queryNew() method.

The JSON response is perfectly suited to pass directly in to the queryNew method

We instantly have an array of structures to send in to our query.

Saving The Data As A Query

Let’s jump straight to the code:

Assuming that we have records returned in the API request, we should now have a query object populated with the results directly from the remote call:

ColdFusion 10 populating a new query with data directly from a remote API request

A Small But Important Caveat

We need to provide the column names for the queryNew() method. As you can see in the code sample above, we took the first result in the response and set the structure key names in to a list to serve this purpose.

For the majority of instances, depending on what API you call, this would probably suffice as the structure of the results wouldn’t change.

The Twitter API, however, will amend the result structure if a particular tweet is a reply by adding in some additional parameters. If these are encountered, the values of the query will not match up with the column names. Something to consider.

Filtering

Now that we have this data, we can easily run a query of queries to further filter the results, or to manipulate them however we wish to:

Filtering the populated query to refine results

ColdFusion already makes working with a JSON response easy. Having the ability to now convert data like this into a query is incredibly cool and helps to make it even easier!

3 Comments

Leave a Comment
      1. Sorry, I was in hurry. I was working on some code from your site. I am new to cold fusion & surfing through the Internet tutorials & examples. I really like this one but this particular example doesn’t work. Update would be nice.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>