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 4,560 views JavaScript, ColdFusion 15 Comments

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.

15 Comments

  • Commented on 05-08-2014 at 6:05 PM
    Couldn't you use the CFLIB function queryToArrayOfStructures(), convert the query using that, and then serialize that to achieve the same result? That's how I've been doing it for years.
  • Scott Stroz #
    Commented on 05-08-2014 at 6:19 PM
    I am NOT 53, yet.
  • Todd Sharp #
    Commented on 05-08-2014 at 6:43 PM
    43?!
  • Henry Ho #
    Commented on 05-08-2014 at 7:09 PM
    Couldn't you have just use serializeJSON(someQuery, true).data for pre-CF11?
  • Commented on 05-08-2014 at 7:21 PM
    @Brian: I did not mean to imply my workaround was new - sorry if it came off that way. It was more just an FYI to remind folks.

    @Todd/Scott: Yeah, yeah, yeah.

    @Henry: No, pre-CF11 there were two forms of query serialization, column and row. I always forget the name of the default.
  • Henry Ho #
    Commented on 05-08-2014 at 8:08 PM
    @Raymond oh my mistake. ya, neither true nor false as 2nd param yields the struct format.
  • Commented on 05-09-2014 at 5:08 AM
    Credit where credit is due, that looks like a copy of queryToArray out of Taffy, which is itself copied and slightly modified from Ben Nadel's blog. And it preserves query column casing: if your sql is "select FoO from bar" then the key name in the resulting json will be FoO - something I'm almost certain CF continues to do poorly.

    So thank you Ben!
  • Commented on 05-09-2014 at 6:57 AM
    I wrote that from scratch for the blog post, but I'm sure all of these solutions follow the same pattern. I explicitly did not want to match case and forced lcase as I thought that would be easier to use.

    As to your comment about case - CF11 has one more setting related to JSON - case preservation. But it only applies to structs, not queries.
  • Commented on 05-09-2014 at 7:29 AM
    Now that I'm not browsing on my phone I can see the entire code sample, and I see what's different. My bad.

    Just goes to show how absurd software patents are, given that such similar solutions can be found independently when solving the same problem. :)
  • Commented on 05-09-2014 at 8:25 AM
    So a query is finally able to be JSONified into a schema that makes sense for JSON (ie: an array of objects).

    Better late than never, but it took a bit too long to get there though.

    --
    Adam
  • Commented on 05-10-2014 at 10:59 AM
    If you're using jQuery, you can also serialize to the struct format client side using my plugin:

    https://github.com/cutterbl/serializeCFJSON
  • anugrah #
    Commented on 07-16-2014 at 5:08 AM
    could you please tell me how to set the bar width of cfchartseries in cf11
  • Anugrah #
    Commented on 07-16-2014 at 5:24 AM
    Could you please tell me how to set the bar width of cfchartseries in cf11?In cf11 json is used for giving backgroudstyle to cfchartseries
  • Commented on 07-16-2014 at 10:05 AM
    Um, I don't know. Check the docs? Remember you can go to ZingCharts for specifics.
  • Dawesi #
    Commented on 07-25-2014 at 4:21 AM
    Looks like a copy out of 'everyone's framework... I have this too in a framework I created over 10 years ago, long before taffy

    There's plenty of clever devs out there ;-)

    Software patents aren't defendable anyway (unless you have 10's of millions) so they aren't worth the effort, also due to a recent case in the USA there is now a precident for people to use patented patterns and not violate patents.

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty