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

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.