How can I parse JSON return from ColdFusion CFC?

Question

I simplified this code for ease of explanation.

I have a cfm page where the user clicks on a table row and gets the id. I want to send this identifier to CFC, run a request there and return the results to the cfm page.

Here is what jQuery looks like.

$.ajax({ url: "test.cfc?method=testFunction", data: {ID:123456}, success: function(response) { $("#div1").html(response); } }); 

and this is what cfc looks like.

 <cfcomponent> <cffunction name="testFunction" access="remote" returnType="query" returnFormat="JSON"> <cfquery name="testQuery" datasource="x"> Select ID, NAME From Table Where ID = '#url.ID#' </cfquery> <cfreturn testQuery> </cffunction> </cfcomponent> 

EDIT - CFC ALTERNATIVE METHOD

 <cffunction name="testFunction" access="remote"> <cfquery name="testQuery" datasource="x"> Select ID, NAME From Table Where ID = '#url.ID#' </cfquery> <cfset response = [] /> <cfoutput query="testQuery"> <cfset obj = { "ID" = ID, "NAME" = NAME } /> <cfset arrayAppend(response, obj) /> </cfoutput> <cfprocessingdirective suppresswhitespace="Yes"> <cfoutput> #serializeJSON(response)# </cfoutput> </cfprocessingdirective> <cfsetting enablecfoutputonly="No" showdebugoutput="No"> </cffunction> 

As the success function in the ajax call from above shows, div1 will be populated with a JSON response that looks like this.

 {"COLUMNS":["ID","NAME"],"DATA":[[123456,"John"]]} 

EDIT - ALTERNATIVE RESPONSE

 [{"ID":123456,"NAME":"John"}] 

Next, I want to be able to use and output data from this JSON response somewhere on my page. How can i do this? I find it difficult to understand the analysis of this data. My main task is to get this data from the array format so that I can output it to the form fields on my page.

Answer 1

I figured out how to do this.

• Ask cfc to return the query results only with curly braces, not with brackets.

   <cfcomponent> <cffunction name="testFunction" access="remote"> <cfquery name="testQuery" datasource="x"> Select ID, NAME From Table Where ID = '#url.ID#' </cfquery> <cfoutput query="testQuery"> <cfset obj = { "ID" = ID, "NAME" = NAME } /> </cfoutput> <cfprocessingdirective suppresswhitespace="Yes"> <cfoutput> #serializeJSON(obj)# </cfoutput> </cfprocessingdirective> <cfsetting enablecfoutputonly="No" showdebugoutput="No"> </cffunction> 

  2. JQuery looks like

      $.ajax ({ //location of the cfc url: "test.cfc", //function name and url variables to send data: {method:'functioname', ID:ID}, //function run on success can the returned json object and split it out each field to a form field. Here I'm just showing one field in the alert. success: function(obj) { var parsedjson = $.parseJSON(obj); alert(parsedjson.ID); } }); 

Answer 2

CF11: supported, see serialization.serializeQueryAs in the doc .

CF10 or lower: return queryToArray(testQuery) , remember to change the scope of testQuery

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

link : http://www.raymondcamden.com/index.cfm/2014/5/8/ColdFusion-11s-new-Struct-format-for-JSON-and-how-to-use-it-in-ColdFusion- ten

OR, alternatively, use: https://github.com/cutterbl/serializeCFJSON to parse the client-side CF request dataset using Javascript.

Answer 3

(too long for comments)

Several enhancements to simplify and simplify code

• No need for cfoutput or cfsetting inside the function. Just return the original structure as is. To automatically convert the response to JSON, just add the URL parameter ?returnformat=json to your ajax call

• Add dataType: "json" to your ajax call, and jQuery will automatically parse the response in the JSON object.

• Remember to localize all local function variables using VAR or LOCAL

• Typically, you want to avoid accessing the URL directly from a function. Instead, define an identifier as an argument. Then specify ARGUMENTS.ID in the request instead of URL.ID

JQuery

  $.ajax({ type: "GET", url: "test.cfc", // return the result as JSON data: {method: "testFunction", ID:someID, returnFormat: "JSON"}, // automatically parse JSON response dataType: "json", success: function (response) { // check RECORDCOUNT to determine if ID was found alert("ID="+ response.ID); }, error: function (request, status, error) { // do something if call fails.. alert(error); } }); 

CFC:

  <cfcomponent> <cffunction name="testFunction" access="remote" returntype="struct"> <cfargument name="ID" type="string" required="true"> <cfquery name="LOCAL.testQuery" datasource="x"> Select ID, NAME From Table Where ID = <cfqueryparam value="#arguments.ID#" cfsqltype="cf_sql_varchar"> </cfquery> <!--- note, this assumes the query only ever returns 0 or 1 record ---> <cfset Local.obj = { recordCount=testQuery.recordCount, ID=testQuery.ID, Name=testQuery.Name }> <cfreturn Local.obj> </cffunction> </cfcomponent>