Twitter: raymondcamden


Address: Lafayette, LA, USA

ColdFusion 11's new Struct format for JSON (and how to use it in ColdFusion 10)

05-08-2014 5,538 views JavaScript, ColdFusion

One of the nicer new features in ColdFusion 11 is a new way to serialize queries to JSON. If you've ever returned a query over the wire to JavaScript, then you are probably aware that ColdFusion has a ... unique way of serializing the query. The default serialization for queries is based on returning data in a lightweight, efficient format over the network. It isn't difficult to deal with - but it can be awkward if you aren't expecting it. We can argue (and I just did with a fellow ColdFusion community member ;) about whether that choice was wise or not, but instead, I'd rather talk about how ColdFusion 11 addresses this, and how you can mimic the same behavior in earlier versions of ColdFusion.

The default form for query serialization to JSON looks a bit like so:

{"COLUMNS":["NAME","AGE"],"DATA":[["ray",33],["todd",43],["scott",53]]}

When loaded by your JavaScript code, you've got an object with two properties, COLUMNS and DATA. COLUMNS is an array of columns (duh), and DATA is an array of arrays, where the Nth element of each index represents the Nth column. As I said, a bit awkward, but this form saves quite a bit (see the diversion towards the end) of size in the resulting JSON string.

ColdFusion 11 adds a new "struct" form for query serialization. (ColdFusion already had two forms of query serialization - but I'm not going to bother covering the second.) If you serialize your query and pass "struct" as the second argument, serializeJSON(myAwesomeFrakingQuery, "struct"), you'll get something that may be a bit more familiar:

[{"NAME":"ray","AGE":33},{"NAME":"todd","AGE":43},{"NAME":"scott","AGE":53}]

If you are calling a CFC method (the normal way you get JSON), you can ask for this format by adding this to the url: queryformat=struct. So a CFC request in your JavaScript code could look like this: service.cfc?method=getPeople&returnformat=json&queryformat=struct.

As another tip, you can actually set those values as defaults, right in your CFC. On the top of the file (after the component begins of course), outside any methods, you can do: url.returnformat="json" and url.queryformat="struct". Then your JavaScript code can use a simpler URL: service.cfc?method=getPeople.

Lastly, you can set the default serialization for queries directly in Application.cfc. The documentation is missing this (and I have edit rights to it but I've not gotten around to it) but you specify the default like so: this.serialization.serializequeryas = "struct".

Finally (well, before the diversion), how would you do something similar in ColdFusion 10 (or earlier)? Simple - write a utility function to convert the query. Here is an example.

component {

	url.returnformat="json";

	remote function getPeople() {
		//number of test rows
		var rows = 500;
		var result = queryNew("id,propername,age,gender");
		for(var i=1; i<= rows; i++) {
			queryAddRow(result, {id:i, propername:"Name #i#", age:i%25, gender:1});
		}
		return queryToArray(result);
	}

	private function queryToArray(q) {
		var s = [];
		var cols = q.columnList;
		var colsLen = listLen(cols);
		for(var i=1; i<=q.recordCount; i++) {
			var row = {};
			for(var k=1; k<=colsLen; k++) {
				row[lcase(listGetAt(cols, k))] = q[listGetAt(cols, k)][i];
			}
			arrayAppend(s, row);
		}
		return s;
	}
}

In the CFC above, the main method, getPeople, is what would be called from your code. It makes use of the utility method, queryToArray, to return a nice result. getPeople isn't specifying a return type, but if you do add one, be sure to specify array, not query.

Ok, you can stop reading now. Seriously. The rest of this is just numbers BS for fun.

For the heck of it, I did some testing to compare the size of JSON responses between the "old, slim" style and the new sexy style. I used neither ColdFusion 11 nor my utility function; I simply did a hard-coded conversion:

remote function getPeople1() {
	//number of test rows
	var rows = 500;
	var result = queryNew("id,propername,age,gender");
	for(var i=1; i<= rows; i++) {
		queryAddRow(result, {id:i, propername:"Name #i#", age:i%25, gender:1});
	}
	return result;
}

remote function getPeople2() {
	var data = getPeople1();
	var result = [];
	for(var i=1; i<=data.recordCount; i++) {
		arrayAppend(result, {"id":data.id[i], "propername":data.propername[i], "age":data.age[i], "gender":data.gender[i]});
	}
	return result;
}

On every test I did, the slimmer style was slimmer, and got more slimmer when the number of rows got higher and the column names changed. For example, just changing the column "name" to "propername" saw a jump. At 500 rows, the slim style was 11.6KB. The array form was 27.6KB -- more than twice as a big. Of course, 28KB is still relatively slim so - there ya go.

To make things more interesting, Ryan Guill did some testing where he enabled GZIP on the results. Talk about making a difference. You can see the full results here, but in general, he saw approximately 50%, and higher, reductions in size. Nice.