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

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