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!

  • KS

    The Twitter REST API v1 is no longer active. Please migrate to API v1.1. https://dev.twitter.com/docs/api/1.1/overview. – Code doesn’t work. Can you update it?

    • coldfumonkeh

      Hi. Update what? The example code in this blog post?

      • KS

        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.