How Do I Parse JSON Return From ColdFusion CFC?

Question

I've simplified this code for the ease of explanation.

I have a cfm page where the user clicks on a table row and gets an ID. I want to send that ID to a CFC, run a query there, and return the results back to the cfm page.

Here's what the JQuery looks like.

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

and here's what the 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 - ALTERNATE CFC 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 on top shows, div1 will be populated with the JSON response, which looks like this.

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

EDIT - ALTERNATE RESPONSE

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

Next I want to be able to use and output the data from that JSON response somewhere on my page. How can I do that? I'm having a hard time understanding parsing this data. My main concern is to get this data out of the array format so I can output it into form fields in my page.

Answer 1

(Too long for comments)

A few improvements to simplify and solidify the code

1. No need for the cfoutput or cfsetting and inside the function. Just return the raw structure "as is". To automatically convert the response to JSON, simply append the URL parameter ?returnformat=json to your ajax call

2. Add dataType: "json" to your ajax call and jQuery will parse the response into a JSON object automatically

3. Do not forget to localize all function local variables with VAR or LOCAL

4. Typically, you want to avoid accessing the URL scope directly from within a function. Instead, define the ID as an argument. Then reference ARGUMENTS.ID in the query 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>

Answer 2

I figured out the best way to do this.

1. Have the cfc return the query results with braces only and not an array 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. The JQuery looks like this

      $.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 3

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

CF10 or below: return queryToArray(testQuery), don't forget to var-scope your 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;
}

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

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