JSON and CFQuery: Seriously guys!?! Posted by Mischa S.

Back

Date: September 18, 2012

Super handy tool Coldfusion has called SerializeJSON() should make talking with frameworks/toolsets like MooTools and JQuery easier.  Passing data with just saying, 'here's my query and magically make it readable/parseable in the JS world'.  Great idea, but wtf on the execution.

Here is what a JS recordset should look like, imho:

data = [
	{"animal":"monkey","food":"banana"},
	{"animal":"human","food":"Taco Bell"},
	{"animal":"Web Developer","food":"Hot Pockets"}
];

That way, as I'm doing a $data.each() in JQuery, or iterating otherwise, I can reference my columns by name.

However, this is how Coldfusion decided, in its infinite lack of wisdom, to do it:

data = {
	"COLUMNS":["ANIMAL","FOOD"],
	"DATA": [
			["monkey","banana"],
			["human","Taco Bell"],
			["Web Developer","Hot Pockets"]	
		]
};

So, as I'm iterating through this absurdity, but using $data.data.each (NOICE!), I now have to reference the columns by array position instead of name!?

I'm sure it's just beyond me, but that seems dumbtastic.  So, entre mi amigo Dave and he whipped this little nugget of JSON dataset lovin'.  It even fixes the uppercasing that CF loves to do.

public function qryToStruct (query q) {
		
       var cols = listToArray(q.columnList);
       var result = arrayNew(1);
       for (local.r = 1; r LTE q.recordcount; r++) {
              local.fld = structNew();
              for (local.c = 1; c LTE arrayLen(cols); c++) {
                     local.fld[lcase(cols[c])] = q[cols[c]][r];
              }
              arrayAppend(result,fld);
       }
       return result;
}

Pass your query into that, serializeJSON() that puppy and you can rightfully reference your monkey as 'animal' (instead of $data.data[0][0]).