# Generating JSON from a query

I am trying to generate JSON from a query. All this seems to be too much grinding it out.

string function getJSON() output="false"    {

var result = "[";
var myrow = 0;

for(var row in this.qryLocation)  {
myrow++;
result &= "["
& row.LocationID                     & ','
& '"' & row.Address & " " & row.City & '",'
& '"' & row.formatted_CreateDate     & '"'
& ']'
& myRow < this.qryLocation.recordcount ? ',' : '';
} // end for-in query

return result & "]";
}


All this seems very hard to read, especially making sure that the last row does not get an extra comma.

• That does seem quite clunky. I'm sure someone will have ideas on making this more readable and practical. – Phrancis Sep 2 '15 at 2:17
• Which version of ColdFusion? – Adam Cameron Sep 2 '15 at 5:27
• It's difficult to answer well without knowing your version of CF, but as a practice: don't build a JSON string by hand: build a native data structure which represents the format you need, then serialise it to JSON when done. – Adam Cameron Sep 2 '15 at 6:57
• I am on ColdFusion 11 – James A Mohler Sep 2 '15 at 15:33
• The data comes from the result of a CFquery – James A Mohler Sep 2 '15 at 15:34

I agree with Adam's comment. Generally, you should avoid rolling-your-own JSON. Just create native CF objects as usual (ie CF arrays and structures). Then use SerializeJSON to generate the JSON string.

SerializeJSON eliminates the need for manual quoting, which dramatically improves the readability of the code IMO. It also handles any escaping of object values automatically (the current code does not). Granted, Adobe's SerializeJSON function does have a few .. "quirks". So you may prefer to use a different implementation. However, the basic approach remains the same.

string function getJSON() output="false"    {
var result = [];

for(var row in this.qryLocation)  {
arrayAppend(result, [ row.LocationID, row.Address & " " & row.City, row.formatted_CreateDate ] );
}

return serializeJSON(result);
}