CFQUERY to JSON

Rolling Your Own: CFQUERY to JSON

Definitions:
CFML - Adobe ColdFusion Mark-Up Language
JSON - JavaScript Object Notation
dual (in query) - Oracle's generic way of returning data without an actual table
cfdump - outputting the contents of a variable in CF
cfScript - ColdFusion's version of scripting for CFML

When I first started diving into JavaScript and getting data from the Coldfusion server, I was completely confused.

serialize, deserialize, structs, arrays, JSON, objects....ahhhhhhh

Generally, I approach new programming areas with very little finesse and a lot of brute force. So my initial attempt to transform the cfQuery results to JSON by using serializeJSON in CFML. This function takes your CF variable, whether it be an string, array, structure, etc., and converts it to something JavaScript can understand. Going the other way is deserializeJSON, which takes your JSON and converts it to something CF can understand. It's a pretty nifty system but not without it's inconveniences.

Consider the following very simple query:

select  'Terry' as first_name,
        'Beard' as last_name,
        'Low Key Blogger' as title
from    dual

If we cfdump the query results, we get something like this:

RESULTSET	query   FIRST_NAME LAST_NAME TITLE 1 Terry Beard Low Key Blogger
CACHED	false
EXECUTIONTIME	0
SQL	select 'Terry' as first_name,'Beard' as last_name, 'Low Key Blogger' as title from dual

Suppose we have a function that was called from our JavaScript (or other language) application and we need to process the information from the query. What we can do is take the cfquery results and serialize them but doing the following:

serializeJSON(myQuery)

{"COLUMNS":["FIRST_NAME","LAST_NAME","TITLE"],"DATA":[["Terry","Beard","Low Key Blogger"]]}

Yikes! This formatting is not very practical when trying to display data. When first working with this and not knowing any better, I wrote a little JavaScript function that transformed the JSON results to something more useable. In a later version of CF, a parameter was added where you could get the query data in a more amendable format using the keyword struct:

serializeJSON(myQuery, 'struct')

[{"FIRST_NAME":"Terry","LAST_NAME":"Beard","TITLE":"Low Key Blogger"}]

So now we are getting closer to a much more useable format with an array of objects. In fact, you could use this format if you didn't need anything else. But I wanted a bit more control of the column names and field formatting so that the HTML template looked cleaner. Rather than seeing something in the template that says {{myData.FIRST_NAME}}, I wanted the code to be more visually friendly and inline with what you would normally see in JavaScript...some thing like {{myData.firstName}}. In the instance of the example query, we could change the field name to firstName, but CF is quite happy with converting all your column names to CAPS.

[CF] Function Declaration

What I've done is gotten into the habit of rolling my own struct or array (which ever is necessary) from query data in my CF functions. While this seems a little unnecessary for just formatting sake, often times I like to do extra processing on the query results. This maybe formatting dates, or getting additional data based on some attribute in the query.

The first thing to note is that rather than doing a serializeJSON on the return step of your function, you can specify the format of your data return in the function declaration that's getting the data. For example, suppose I have a function called getEmployees designed to return employee data back to my JavaScript page. The function declaration (in cfScript) would look something like:

remote array function getEmployees() returnformat = 'JSON' {
    ... do some stuff ...
    return myData;
}

Forming the Array

Most of the time I am returning arrays of data. The basic idea is to execute a query, loop through the query's result, create a struct to hold a row of data, and then append that struct to the array.

remote array function getEmployees() returnformat = 'JSON' {
    var _sql = 'select * from employees order by last_name, first_name';
    var _qEmployees = new Query();

    _qEmployees.setDatasource(myDS);

    var _rEmployees = _qEmployees.execute( sql = _sql );

    var _employees = ArrayNew(1)

    for (_e in _rEmployees.getResult()){
        var _employee = structNew();

        _employee['firstName'] = _e.first_name;
        _employee['lastName'] = _e.last_name;
        _employee['title'] = _e.title;
        _employee['hireDate'] = dateformat(_e.hire_date,'short');

        arrayAppend(_employees,_employee);
    }

    return _employees;
}

The first two lines in the above example function is to define the query:

var _sql = 'select * from employees order by last_name, first_name';
var _qEmployees = new Query();

Next we set the query's datasource, i.e. where the data is coming from and then .execute the query and put the results of the query into the variable _rEmployees. An array called _employees is created to hold our processed data. We loop through the data using a for loop where _e holds each row as we go through the .getResult, effectively the result set.

Inside the loop, we define the _employee structure to hold the row of data. Then we assign each column of data (that we want) to the corresponding property of the _employee structure. It's important to note the syntax of the _employee[] specification. CF allows you to use dot notation to make these assignments as well. For example you can do employee.firstName = _e.first_name. However, by default, CF will return the property as all CAPS. When using bracket notation for the structure, the CF returns the property name as is.

The next step in the loop is to add the _employee to the _employees collection which is done with arrayAppend.

After the loop has run it's course, the final step is to return the data back to the calling process.

The result is very similar to just returning the query using the serializeJSON(_qEmployees,'struct') but with lower case formatting:

[{"lastName":"Stark","hireDate":"12/1/18","firstName":"Tony","title":"Iron Man"},
{"lastName":"Parker","hireDate":"12/1/18","firstName":"Peter","title":"Spider Man"},
{"lastName":"Odinson","hireDate":"12/1/18","firstName":"Loki","title":"Prince of Asgard"}]

Benefits

While it's not necessary to roll your own JSON, there are some benefits in doing so. First is readability on the JavaScript and template side of the application. Having the both the middle and presentation tiers match on their data elements goes a long way toward maintenance and debugging. The second benefit is being able to easily do some post processing on the query's result set. A small example in the above process is where I formatted the hire date. But sometimes you need to pull data from somewhere else that, while it might be possible inside the query, it may not be wise. The biggest benefit is that it is a pattern which can be easily followed and understood a year or two or five later.

Hope you found this helpful. Feel free to leave a comment below and let me know what you think.