Generating JSON from a query

Question

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.

• Phrancis: That does seem quite clunky. I'm sure someone will have ideas on making this more readable and practical.
• Adam Cameron: Which version of ColdFusion?
• Adam Cameron: 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.
• James A Mohler: I am on ColdFusion 11
• James A Mohler: The data comes from the result of a CFquery

Answer 1

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); 
}