Steve "Cutter" Blades has come up with a clever way of dealing with a problem you'll be familiar with if you've ever tried to get query data from ColdFusion remotely via AJAX.

CF makes it very easy to serialise server-side data as JSON for consumption by Javascript in the browser, but the format it uses for query objects is not ideal. Take the following simple query object:

By default, CF will serialise it as follows:

{
	"COLUMNS":
		[
			"ID"
			,"COLOUR"
		]
	,"DATA":
		[
			[1,"red"]
			,[2,"green"]
			,[3,"blue"]
		]
}

A supposedly "friendlier" format is available by using the serializeQueryByColumns flag on SerializeJson(), or the queryFormat="column" parameter when calling a remote CFC method:

{
	"ROWCOUNT":3
	,"COLUMNS":
			["ID","COLOUR"]
	,"DATA":
		{
			"ID":[1,2,3]
			,"COLOUR":["red","green","blue"]
		}
}

On the positive side, both these formats are extremely efficient in terms of the size of the string returned. Rather than repeating them for each row, the column names are stated only once in the first type, and twice in the second.

But neither format is particularly useful when trying to pass data to jQuery plugins and other client side code, which typically expect name/value pairs.

Array conversion

Like many other developers, I generally work around this by doing a little extra work on the server side to convert the query object to an array. CF will then serialise that as name/value pairs. This also allows you to circumvent another notorious CF quirk in which it forces all keys to upper case (a royal pain with case-sensitive Javascript). Using bracket-notation as you convert the query prevents this.

(Note: all CF code that follows assumes version 9.0.1.)

remote.cfc

remote array function getDataAsArray() returnFormat="json"
{
	// Simulate a query	
	var q	=	QueryNew( "" );
	var ids	=	[ 1,2,3 ];
	var colours	=	[ "red","green","blue" ];
	QueryAddColumn( q,"ID","Integer",ids );
	QueryAddColumn( q,"colour","VarChar",colours );
	var queryConvertedToArray	=	[];
	// Loop over the query and add each row as a struct to the array
	for( var i=1; i LTE q.recordcount; i++ )
	{
		queryConvertedToArray[ i ]	=	{}; // new row as struct
		// populating the struct using bracket notation allows us to preserve the case of the keys
		queryConvertedToArray[ i ][ "id" ] = q.id[ i ]; 
		queryConvertedToArray[ i ][ "colour" ]	=	q.colour[ i ];
	}
	return queryConvertedToArray;
}

This will return an array of javascript name/value pair objects—much easier to deal with on the client:

[
	{"colour":"red","id":1}
	,{"colour":"green","id":2}
	,{"colour":"blue","id":3}
]

With this tiny sample array, the JSON size is actually smaller than that of either of the query formats, but because the column names are repeated for each row, you can see how with larger data sets a lot of redundant data would be transferred from server to client.

What if we could get the best of both worlds: the efficiency of CF's JSON format as it's transferred, but the straightforwardness of the name/value format as it's consumed?

serializeCFJSON

Enter Steve's serializeCFJSON jQuery plugin which will take the CF query JSON as delivered over the network and massage it into the name/value format ready to be used.

At the time of writing though, Steve's code only appears to work with queries nested inside larger JSON blocks, so I created a simpler version for use exclusively on single, non-nested queries:

(function( $ ){
	$.standardiseCfQueryJSON=function( cfQueryJSON )
	{
		var result	=	[];
		$.each( cfQueryJSON.DATA,function( rowNum,rowValue ){
			var row	=	new Object();
			$.each( cfQueryJSON.COLUMNS,function( position,columnName ){
				row[ columnName.toLowerCase() ] = rowValue[ position ];
			});
			result.push( row );
		});
		return result;
	};
})( jQuery );

(Note that the function forces the upper case keys to be lower case. A downside of this approach is that we don't get the opportunity to preserve the original case, but lower case will normally be preferable to upper.)

UPDATE 8 May 2012: Steve has updated his plugin to work with non-nested queries.

Putting it all together, we can now simplify our remote cfc method which no longer needs the array conversion and can return the query directly as JSON:

remote.cfc

remote query function getData() returnFormat="json"
{
	var q	=	QueryNew( "" );
	var ids	=	[ 1,2,3 ];
	var colours	=	[ "red","green","blue" ];
	QueryAddColumn( q,"ID","Integer",ids );
	QueryAddColumn( q,"colour","VarChar",colours );
	return q;
}

As we call this method on the client-side, our function converts the data to name/value pairs:

$.ajax({
		url:"remote.cfc?method=getData"
		,dataType:"json"
		,success: function( cfdata ){
			data	=	$.standardiseCfQueryJSON( cfdata );
			// do something with the data
			console.log( data );
		}
});

In your console you should be able to see both the original JSON as sent over the network, and the converted name/value pairs ready for consumption.

Making the appropriate choice

Although it's more work on the server-side, the array conversion technique does give you more control over the data, especially when it comes to preserving case. But client-side conversion of the original CF query format is worth considering if you expect large data sets and minimising network overhead is important.