ColdFusion Query Results to JSON

To quickly convert query resutls to JSON in ColdFusion, you can use CF’s serializeJSON function. But, you’ll soon notice that the way ColdFusion organizes your query results is not all that great. Basically, it gives you the following keys:

  • COLUMNS
  • DATA

and you will end up with a JSON string like:

  • {“COLUMNS”:[“R_ID”,”R_FIRSTNAME”,”R_LASTNAME”,”RT_NAME”,”R_URL”],”DATA”:[[164,”mmmmmmm”,”mmmmmmmmm”,”MD”,””],[163,”zzzzzzzzzzz”,”zzzzzzzzzzzzz”,”MD”,””],[162,”dfsdfsd”,”dsfsd”,”MD”,””],[161,”werwe”,”werewr”,”MD”,null],[3,”test”,”sql”,”Ph.D.”,”http:\/\/www.test.com“],[4,”test”,”sql2″,”MPH”,”http:\/\/www.blah.com“],[5,”upd”,”test”,”MD”,null],[6,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[7,”test”,”test”,”MD”,”test”],[8,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[9,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[10,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[11,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[12,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[13,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[14,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“],[15,”test”,”add”,”Ph.D.”,”http:\/\/www.test.com“]]}

That is not exactly flexible to work with. I wanted a more standard, flexible, and clear JSON format to for use in one of my sites, one that allows creation of custom arrays and objects, as well as creation of custom key names. It works well for me, so I’m sharing with you guys.. Note that my example code revolves around a query of researchers that belong to the logged in user. This is for a secret project I hope to release later. 😉

The function – setQueryDataFeed:

<cffunction name="setQueryDataFeed" access="public" output="false" returntype="struct" hint="sets query results to json">
 <cfargument name="argumentsStruct" type="struct" required="true" />
  <cfscript>
   var feedData = structNew();
   var extraKeysLen = 0;
   var outerVar = "";
   var formatForMap = false;
   var extraKeysValues = arrayNew(1);
   var extraKeys = arrayNew(1);
   var rec_ID = "";
   var columnCnt = "";
   feedData['dataFeed'] = "{";
   if(structKeyExists(arguments.argumentsStruct, "extraKeys")){
    extraKeys = arguments.argumentsStruct.extraKeys;
    extraKeysLen = arrayLen(arguments.argumentsStruct.extraKeys);
    extraKeysValues = arguments.argumentsStruct.extraKeysValues;
   }
   if(structKeyExists(arguments.argumentsStruct, "outerVar")){
    outerVar = arguments.argumentsStruct.outerVar;
   }
   if(structKeyExists(arguments.argumentsStruct, "formatForMap")){
    formatForMap = arguments.argumentsStruct.formatForMap;
   }
   if(len(outerVar)){
    if(formatForMap){
     feedData['dataFeed'] = feedData.dataFeed & """" & outerVar & """:[";
   }
   else{
    feedData['dataFeed'] = feedData.dataFeed & """" & outerVar & """:{";
   }
  }
  columnCnt = arrayLen(arguments.argumentsStruct.queryColumns);
  feedData['dataFeed'] = feedData.dataFeed & "";
  if(formatForMap){
   for(i = 1; i lte arguments.argumentsStruct.queryResults.recordCount; i = i + 1){
    rec_ID = arguments.argumentsStruct.queryKey;
    if(i eq 1 or (i gt 1 and arguments.argumentsStruct.queryResults[rec_ID][i] neq arguments.argumentsStruct.queryResults[rec_ID][i - 1])){
     feedData['dataFeed'] = feedData.dataFeed & "{";
     newRec = true;
    }
    for(x = 1; x lte columnCnt; x = x + 1){
     feedData['dataFeed'] = feedData.dataFeed & """" & arguments.argumentsStruct.queryColumns[x] & """:""" & arguments.argumentsStruct.queryResults[arguments.argumentsStruct.queryColumns[x]][i] & """";
     if(x lt columnCnt){
      feedData['dataFeed'] = feedData.dataFeed & ",";
     }
     else if(x eq columnCnt and newRec){
      feedData['dataFeed'] = feedData.dataFeed & "}";
     }
     if((x eq columnCnt and newRec) and i lt arguments.argumentsStruct.queryResults.recordCount){
      feedData['dataFeed'] = feedData.dataFeed & ",";
     }
    }
   }
  }
  else{
   for(x = 1; x lte columnCnt; x = x+1){
    feedData['dataFeed'] = feedData.dataFeed & """" & arguments.argumentsStruct.queryColumns[x] & """:[";
    for(i = 1; i lte arguments.argumentsStruct.queryResults.recordCount; i=i+1){
     feedData['dataFeed'] = feedData.dataFeed & """" & arguments.argumentsStruct.queryResults[arguments.argumentsStruct.queryColumns[x]][i] & """";
     if(i lt arguments.argumentsStruct.queryResults.recordCount){
      feedData['dataFeed'] = feedData.dataFeed & ",";
     }
    }
    feedData['dataFeed'] = feedData.dataFeed & "]";
    if(x lt columnCnt){
     feedData['dataFeed'] = feedData.dataFeed & ",";
    }
   }
  }
  if(len(outerVar) and not formatForMap){
   feedData['dataFeed'] = feedData.dataFeed & "}";
  }
  if(extraKeysLen){
   for(i = 1; i lte extraKeysLen; i=i+1){
    feedData['dataFeed'] = feedData.dataFeed & ",""" & extraKeys[i] & """:";
    feedData['dataFeed'] = feedData.dataFeed & """" & extraKeysValues[i] & """";
   }
  }
  if(formatForMap){
   feedData['dataFeed'] = feedData.dataFeed & "]}";
  }
  else{
  feedData['dataFeed'] = feedData.dataFeed & "}";
  }
 </cfscript>
 <cfreturn feedData />
</cffunction>

Function Arguments:

queryResults: The query object to want to convert to JSON. required.

queryColumns: An array of column names from the query to include in JSON string. required.

formatForMap: If you wish to format for the jQuery map function, set to true. optional. default: false.

outerVar: If you want to create an object holding your query (ie: researchers{“r_ID”:[“value”]}), pass in an object name. optional.

extraKeys: If you want to return some extra keys in addition to the query results, pass in an array of key names. optional.

extraKeysValues: If using extraKeys, pass in an array of key values. optional.

This will result in a JSON string in the following format:

  • {“outerVar”:{“queryColumns[1]”:[“queryResults.queryValue1″],”queryColumns[2]”:[“queryResults.queryValue2″]},”extraKeys[1]”:”extraKeysValues[1]”}

And here’s an example:

  • {“researchers”:{“r_ID”:[“164″,”163″,”162″,”161″,”3″,”4″,”5″,”6″,”7″,”8″,”9″,”10″,”11″,”12″,”13″,”14″,”15″],”r_firstName”:[“mmmmmmm”,”zzzzzzzzzzz”,”dfsdfsd”,”werwe”,”test”,”test”,”upd”,”test”,”test”,”test”,”test”,”test”,”test”,”test”,”test”,”test”,”test”],”r_lastName”:[“mmmmmmmmm”,”zzzzzzzzzzzzz”,”dsfsd”,”werewr”,”sql”,”sql2″,”test”,”add”,”test”,”add”,”add”,”add”,”add”,”add”,”add”,”add”,”add”],”rt_name”:[“MD”,”MD”,”MD”,”MD”,”Ph.D.”,”MPH”,”MD”,”Ph.D.”,”MD”,”Ph.D.”,”Ph.D.”,”Ph.D.”,”Ph.D.”,”Ph.D.”,”Ph.D.”,”Ph.D.”,”Ph.D.”]},”researcherAddLnk”:”Not listed? Add Researcher”,”researcherAddMsg”:””}

Here is the calling code:

researchData['queryResults'] = researchData.researcherResults;
researchData['outerVar'] = “researchers”;
researchData['queryColumns'] = arrayNew(1);
researchData['queryColumns'][1] = “r_ID”;
researchData['queryColumns'][2] = “r_firstName”;
researchData['queryColumns'][3] = “r_lastName”;
researchData['queryColumns'][4] = “rt_name”;
researchData['extraKeys'] = arrayNew(1);
researchData['extraKeys'][1] = “researcherAddLnk”;
researchData['extraKeys'][2] = “researcherAddMsg”;
researchData['extraKeysValues'] = arrayNew(1);
researchData['extraKeysValues'][1] = researchData.researcherAddLnk;
researchData['extraKeysValues'][2] = researchData.researcherAddMsg;
structAppend(researchData, request.utils.setQueryDataFeed(researchData));

More code examples, including an example using the formatForMap option, later. Any input or suggestions, let me know!

Advertisements

2 responses to “ColdFusion Query Results to JSON

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s