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.
Archived Comments
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.
I am NOT 53, yet.
43?!
Couldn't you have just use serializeJSON(someQuery, true).data for pre-CF11?
@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.
@Raymond oh my mistake. ya, neither true nor false as 2nd param yields the struct format.
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!
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.
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. :)
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
If you're using jQuery, you can also serialize to the struct format client side using my plugin:
https://github.com/cutterbl...
could you please tell me how to set the bar width of cfchartseries in cf11
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
Um, I don't know. Check the docs? Remember you can go to ZingCharts for specifics.
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.
Is there a way to make the field/key being returned lowercase?
eg:
Current output: [{“NAME”:”ray”,”AGE”:33},{“NAME”:”todd”,”AGE”:43},{“NAME”:”scott”,”AGE”:53}]
Desired output: [{“name”:”ray”,”age”:33},{“name”:”todd”,”age”:43},{“name”:”scott”,”age”:53}]
Use this setting, serialization.preservecaseforstructkey, in your app.cfc. Or use bracket notation when creating the struct.
Thanks for the response! The JSON in my case is actually created by calling a cfc using returnformat=json in an ajax call so I don't have control over bracket notation. I also don't want to create my own json object in the cfc method because that defeats the purpose of the built in feature of returnformat... I will try the setting in app.cfc as you suggested.
Hey fellas,
I am running a CFExecute to pull in json data via powershell from AWS CLI. I have successfully been able to display the data in JSON format in my cfml page, but am having a heck of a time formatting or deserializing the data. I'm very new to json, and have no idea what I am doing. Here is what is working so far, but any attempt to deserialize causes a 500 error. Any help is appreciated.
<cfoutput>
<cfexecute name="powershell.exe" variable="result" errorvariable="err" arguments="aws ec2 describe-volumes --query 'Volumes[*].[VolumeId, Attachments[0].InstanceId, AvailabilityZone, Size, VolumeType]'" timeout="10"/>
Error: <cfif err="" neq="" ""="">#err#<cfelse>None</cfif>
</cfoutput>
<cfoutput>#result#</cfoutput>
I'm confused. I don't see where you are deserializing there. I see you calling powershell and outputting the result.
Hi Raymond,
This auto-complete thingy I've inherited requires the JSON to look like this:
{"results":[{"id":544,"text":"Painting & Decorating \u2014 Automotive Painting"},{"id":487,"text":"Painting & Decorating \u2014 Exterior Painting"},{"id":486,"text":"Painting & Decorating \u2014 Interior Painting"},{"id":550,"text":"Painting & Decorating \u2014 Roof Painting"},{"id":488,"text":"Painting & Decorating \u2014 Decorators"},{"id":489,"text":"Painting & Decorating \u2014 Wallpaper Hanging"}]}
I'm nearly there thanks to your post, but how do I get the "results" node (or whatever it's called) in there at the front?
I resolved it in an extremely hacky way as follows:
<cfset vsj="{"results":" &="" serializejson(qget,="" "struct")="" &="" '}'="">
<cfset vsj="replace(vSJ,"\\u2014","\u2014","ALL")">
<cfset vsj="replace(vSJ,"ID","id","ALL")">
<cfset vsj="replace(vSJ,"TEXT","text","ALL")">
<cfcontent type="application/json">
<cfreturn vsj="">
It'll do for now:-)
A bunch of extra markup was added to that last code snippet during posting - i.e. equals signs followed by double quotes.
Why are you building the json by hand versus using CF's built in serialization?
Using serializeJSON(qGet,"struct") on my query, I get:
[{"id":544,"text":"Painting & Decorating \u2014 Automotive Painting"},{"id":487,"text":"Painting & Decorating \u2014 Exterior Painting"},{"id":486,"text":"Painting & Decorating \u2014 Interior Painting"},{"id":550,"text":"Painting & Decorating \u2014 Roof Painting"},{"id":488,"text":"Painting & Decorating \u2014 Decorators"},{"id":489,"text":"Painting & Decorating \u2014 Wallpaper Hanging"}]
However, I need it looking like:
{"results":[{"id":544,"text":"Painting & Decorating \u2014 Automotive Painting"},{"id":487,"text":"Painting & Decorating \u2014 Exterior Painting"},{"id":486,"text":"Painting & Decorating \u2014 Interior Painting"},{"id":550,"text":"Painting & Decorating \u2014 Roof Painting"},{"id":488,"text":"Painting & Decorating \u2014 Decorators"},{"id":489,"text":"Painting & Decorating \u2014 Wallpaper Hanging"}]}
so, I just manufactured the "results" key/node or whatever it's called.
Heh - can I ask why - in your client-side code, you wanted the results wrapped like that? It seems pointless. If you were returning the query _and_ other stuff, then it would make sense to use name/value pairs for each part of data, but you're only returning the query.