Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with:

Looping Over ColdFusion JSON Queries In jQuery

Posted by Ben Nadel

I've talked to people many times about how to loop over ColdFusion queries that have been returned from an AJAX call in JSON format. The serialized format of ColdFusion queries are a bit round-about since they are designed for memory optimization, not necessarily usability. As such, I often wind up telling people that I prefer to convert my queries to arrays of structs before returning them as that format is quite intuitive to work with on the Javascript side.

After discussing this topic with Andy Matthews last week, I wanted to see if could use a jQuery plugin to abstract out the differences between all the (popular) query serialization formats. From what I can reason, there are three main query serialization formats:

SerializeJSON( query )

This method serializes a query using SerializeJSON() with no WDDX compatibility. It returns the query in the following format:

  • {
  • "COLUMNS":["ID","NAME"],
  • "DATA":[
  • [1.0,"Kate"],
  • [2.0,"Sarah"],
  • [3.0,"Tricia"],
  • [4.0,"Kit"],
  • [5.0,"Jill"]
  • ]
  • }

As you can see, this breaks the column names and data values into two separate arrays.

SerializeJSON( query, true )

This method serializes a query using SerializeJSON() with the WDDX-compatibility. It returns the query in the following format:

  • {
  • "ROWCOUNT":5,
  • "COLUMNS":["ID","NAME"],
  • "DATA":{
  • "id":[1.0,2.0,3.0,4.0,5.0],
  • "name":["Kate","Sarah","Tricia","Kit","Jill"]
  • }
  • }

As you can see, this looks a bit more like a standard ColdFusion query in which we have a RecordCount as well as individual column arrays.

Array of Structs

This method serializes a query using a manual conversion of rows to structs. It returns the query in the following format:

  • [
  • {"ID":1.0,"NAME":"Kate"},
  • {"ID":2.0,"NAME":"Sarah"},
  • {"ID":3.0,"NAME":"Tricia"},
  • {"ID":4.0,"NAME":"Kit"},
  • {"ID":5.0,"NAME":"Jill"}
  • ]

As you can see, this is nothing more than an array of structs in which each struct is a collection of name-value pairs in which each name is the name of a column in the row. Here, we lose memory optimization since each column is repeated for each row; but, we have an objectively easier data structure to work with.

I figured the easiest way to abstract out the query iteration would be to create an each-style method in which you pass it the query and a callback function and the callback gets executed in the context of each row. The row context would then be a column-key-indexed struct that would be super user friendly. To see what I am intending, take a look at this:

  • eachRow(
  • query,
  • function( rowIndex, row ){
  • console.log( this.ID, row.ID );
  • console.log( this.NAME, row.NAME );
  • }
  • );

Here, the callback takes two arguments - rowIndex (the index of the row being iterated) and row (a struct of name-value pairs in which the name is the column name). While the row is passed as an argument to the callback, the callback is also executed in the context of the row such that you can access the column values using either the row argument or the THIS-based keys.

I took that concept and I built it as a jQuery plugin. To add a better level of organization to this plugin, I created it within a "coldfusion" namespace:

  • ;(function( $ ){
  •  
  • // Create a name space for ColdFusion related functionality.
  • jQuery.coldfusion = {};
  •  
  •  
  • // Create a function that will iterate over each row of the
  • // serialized JSON query. This iteration can handle three
  • // types of query structure:
  • //
  • // - Default from SerializeJSON()
  • // - WDDX Compatible from SerializeJSON()
  • // - Array of structs.
  • jQuery.coldfusion.eachRow = function( query, callback ){
  • // Check to see which type of iterator we are going to
  • // use when looping over this query.
  • if ($.isArray( query )){
  •  
  • // This is an array of structs.
  • jQuery.coldfusion.eachRow.arrayIterator(
  • query,
  • callback
  • );
  •  
  • } else if ("ROWCOUNT" in query){
  •  
  • // This is the WDDX-compatible format.
  • jQuery.coldfusion.eachRow.wddxIterator(
  • query,
  • callback
  • );
  •  
  • } else {
  •  
  • // This is the default format.
  • jQuery.coldfusion.eachRow.defaultIterator(
  • query,
  • callback
  • );
  •  
  • }
  •  
  • // Return the jQuery library.
  • return( this );
  • };
  •  
  •  
  • // Define a cfquery loop iteration method that can handle the
  • // default SerializeJSON() method.
  • jQuery.coldfusion.eachRow.defaultIterator = function( query, callback ){
  • var i = 0;
  •  
  • // Loop over the data array.
  • for (var i = 0 ; i < query.DATA.length ; i++){
  •  
  • (function( rowIndex ){
  • var row = {};
  •  
  • // Loop over the column names to create the data
  • // collection as column-value pairs.
  • $.each(
  • query.COLUMNS,
  • function( index, column ){
  • row[ column ] = query.DATA[ rowIndex ][ index ];
  • }
  • );
  •  
  • // Execute the callback method in the context of
  • // the row data.
  • callback.call( row, rowIndex, row );
  •  
  • })( i );
  •  
  • }
  • };
  •  
  •  
  • // Define a cfquery loop iteration method that can handle the
  • // SerializeJSON() method that returns WDDX-compatible data.
  • jQuery.coldfusion.eachRow.wddxIterator = function( query, callback ){
  • var i = 0;
  •  
  • // Loop over the records.
  • for (var i = 0 ; i < query.ROWCOUNT ; i++){
  •  
  • (function( rowIndex ){
  • var row = {};
  •  
  • // Loop over the column names to create the data
  • // collection as column-value pairs.
  • $.each(
  • query.DATA,
  • function( column, values ){
  • row[ column.toUpperCase() ] = values[ rowIndex ];
  • }
  • );
  •  
  • // Execute the callback method in the context of
  • // the row data.
  • callback.call( row, rowIndex, row );
  •  
  • })( i );
  •  
  • }
  • };
  •  
  •  
  • // Define a cfquery loop iteration method that can handle the
  • // query as an array of structs.
  • jQuery.coldfusion.eachRow.arrayIterator = function( query, callback ){
  • var i = 0;
  •  
  • // Loop over the records.
  • for (var i = 0 ; i < query.length ; i++){
  •  
  • // Execute the callback method in the context of
  • // the row data.
  • callback.call( query[ i ], i, query[ i ] );
  •  
  • }
  • };
  •  
  • })( jQuery );

Because Javascript treats functions as first class citizens, I was able to store the three iteration methods as properties of the eachRow() method. To see this in action, let's take a look at an example:

  • <!DOCTYPE HTML>
  • <html>
  • <head>
  • <title>Looping Over ColdFusion JSON Queries In jQuery</title>
  • <script type="text/javascript" src="jquery-1.3.2.min.js"></script>
  • <script type="text/javascript" src="jquery.query-loop.js"></script>
  • <script type="text/javascript">
  •  
  • // When the DOM loads, gather the query data and output
  • // it as lists.
  • jQuery(function( $ ){
  •  
  • // Get the results dive for population.
  • var results = $( "#results" );
  •  
  •  
  • // This method will handle writing the query to the
  • // DOM as an ordered list.
  • var render = function( type, query ){
  • var items = [];
  •  
  • // Add the type header.
  • results.append( "<h2>" + type + "</h2>" );
  •  
  • // Loop over the query to add each row to the
  • // output string buffer. The eachRow() method is
  • // executed in the context of the row collection.
  • $.coldfusion.eachRow(
  • query,
  • function( rowIndex ){
  • items.push(
  • "<li>" +
  • this.NAME +
  • " ( ID: " + this.ID + " )" +
  • "</li>"
  • );
  • }
  • );
  •  
  • // Append the rows as an ordered list.
  • results.append(
  • "<ol>" +
  • items.join( "" ) +
  • "</ol>"
  • );
  • };
  •  
  •  
  • // Gather the query using the default method.
  • $.getJSON(
  • "./query.cfm",
  • function( query ){
  • render( "Default Method", query );
  • }
  • );
  •  
  • // Gather the query using the wddx-compatible method.
  • $.getJSON(
  • "./query.cfm?serializeMethod=wddxCompatible",
  • function( query ){
  • render( "WDDX-Compatible Method", query );
  • }
  • );
  •  
  • // Gather the query using the array of structs method.
  • $.getJSON(
  • "./query.cfm?serializeMethod=arrayOfStructs",
  • function( query ){
  • render( "Array-of-Structs Method", query );
  • }
  • );
  •  
  • });
  •  
  • </script>
  • </head>
  • <body>
  •  
  • <h1>
  • Looping Over ColdFusion JSON Queries In jQuery
  • </h1>
  •  
  • <div id="results">
  • <!--- This will be populated dynamically. --->
  • </div>
  •  
  • </body>
  • </html>

Here, we are calling a ColdFusion page to return a query object in the three different serialization formats. Each type of serialized query is then iterated over using the eachRow() plugin and its contents are written to the DOM. Running the above code, we get the following output:

Default Method

1. Kate ( ID: 1 )
2. Sarah ( ID: 2 )
3. Tricia ( ID: 3 )
4. Kit ( ID: 4 )
5. Jill ( ID: 5 )

WDDX-Compatible Method

1. Kate ( ID: 1 )
2. Sarah ( ID: 2 )
3. Tricia ( ID: 3 )
4. Kit ( ID: 4 )
5. Jill ( ID: 5 )

Array-of-Structs Method

1. Kate ( ID: 1 )
2. Sarah ( ID: 2 )
3. Tricia ( ID: 3 )
4. Kit ( ID: 4 )
5. Jill ( ID: 5 )

As you can see, each style of query serialization was iterated over usingg the exact samemethodologyy - the eachRow() jQuery plugin.

If you care to see what the ColdFusion side of things is doing in order to create the various serialization formats, here you go:

  • <!---
  • Param the type of return value that we are going to be using
  • when we serialize the query object. There are three different
  • ways to return the query data:
  •  
  • default: serializeJSON( query )
  • wddxCompatible: serializeJSON( query, true )
  • arrayOfStructs: Create an array of structs.
  • --->
  • <cfparam
  • name="url.serializeMethod"
  • type="string"
  • default="default"
  • />
  •  
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  • <!--- Build the query object. --->
  • <cfset girls = queryNew( "" ) />
  •  
  • <!--- Add the ID column. --->
  • <cfset queryAddColumn(
  • girls,
  • "id",
  • "cf_sql_integer",
  • listToArray( "1,2,3,4,5" )
  • ) />
  •  
  • <!--- Add the name column. --->
  • <cfset queryAddColumn(
  • girls,
  • "name",
  • "cf_sql_varchar",
  • listToArray( "Kate,Sarah,Tricia,Kit,Jill" )
  • ) />
  •  
  • <!--- ----------------------------------------------------- --->
  • <!--- ----------------------------------------------------- --->
  •  
  •  
  • <!---
  • Now that we have created our query object, let's serialize it.
  • But first, we need to see which method of serialization we are
  • going to use.
  • --->
  • <cfswitch expression="#url.serializeMethod#">
  •  
  • <!---
  • arrayOfStructs. This will convert each row of the query
  • to a struct and return the aggregate of row-stucts in an
  • array.
  • --->
  • <cfcase value="arrayOfStructs">
  •  
  • <!---
  • Before we can serialize the query, we need to convert
  • it to an array of structs.
  • --->
  • <cfset rows = [] />
  •  
  • <!--- Loop over the query to convert it. --->
  • <cfloop query="girls">
  •  
  • <!--- Create a row struct. --->
  • <cfset row = {} />
  •  
  • <!--- Add each column to our struct. --->
  • <cfloop
  • index="column"
  • list="#girls.columnList#"
  • delimiters=",">
  •  
  • <cfset row[ column ] = girls[ column ][ girls.currentRow ] />
  •  
  • </cfloop>
  •  
  • <!--- Append the row struct to the row array. --->
  • <cfset arrayAppend( rows, row ) />
  •  
  • </cfloop>
  •  
  • <!---
  • Now that we have converted our query to an
  • array of structs, we can serialize it using the
  • serializeJSON() method.
  • --->
  • <cfset serializedQuery = serializeJSON( rows ) />
  •  
  • </cfcase>
  •  
  • <!---
  • wddxCompatible. This will use the SerializeJSON() method,
  • but it will pass in TRUE as the optional second parameter
  • for a WDDX compatible format.
  • --->
  • <cfcase value="wddxCompatible">
  •  
  • <!--- Seraialize the query. --->
  • <cfset serializedQuery = serializeJSON( girls, true ) />
  •  
  • </cfcase>
  •  
  • <!--- The default is just to use SerailizeJSON(). --->
  • <cfdefaultcase>
  •  
  • <!--- Seraialize the query. --->
  • <cfset serializedQuery = serializeJSON( girls ) />
  •  
  • </cfdefaultcase>
  •  
  • </cfswitch>
  •  
  •  
  • <!---
  • Now that we have our serialized data, let's convert it
  • into binary so we can have a bit more control over the
  • streaming.
  • --->
  • <cfset binaryResponse = toBinary( toBase64( serializedQuery ) ) />
  •  
  •  
  • <!--- Set the header. --->
  • <cfheader
  • name="content-length"
  • value="#arrayLen( binaryResponse )#"
  • />
  •  
  • <!--- Stream the content back as JOSN. --->
  • <cfcontent
  • type="application/x-json"
  • variable="#binaryResponse#"
  • />

Hopefully this will help you abstract away the confusingg ColdFusion query serialization format. Once again, jQuery for the win!



Reader Comments

Hey,
Thanks for the ColdFusion Tutorial.
It was very helpfull. Ive learnd a lot of them.
This style and your code-template helps me for my other programming ideas.

Thx Britta.

Reply to this Comment

@Sami,

Thanks my man; glad you like it. I think query serialization is definitely one of the trickiest things that people come up against when crossing the ColdFusion-Javascript chasm.

Reply to this Comment

@Michael,

Sweet. I particularly like that the callback function is called in the context of the row itself such that the column values can be accessed off of the THIS reference.

Reply to this Comment

Thanks Ben - this is just what I needed - just getting up to speed with jQuery - and your posts are accelerating that process substantially :)

Reply to this Comment

@Daniel,

Awesome my man. If there is anything you want to see in particular, just let me know and I'll try to write up something worthwhile.

Reply to this Comment

Ben, I'm hoping you can help me make sense of this.

I have an update form where a client can update some information about a project and then below that another form where the client can update information about the components. The components could be 1 item to 101 items (the first form is not looped so I know these form field names; the second form is populated by looping over a query so the form field names are dynamic).

I'm using jQuery to serialize the data submitted to an action page where I'd like to update the database. Could I used what you posted here to solve this or is this a solution for another problem or would you have another suggestion to review for my problem? I know how to submit the page and update the database but I'd prefer to not have the use leave the page just for an update (thus why I'm using ajax).

TIA,
Tom

Reply to this Comment

@Tom,

It sounds like you are already OK at generating the form with the N-number of items? Is that correct? You are asking about posting the various data items back to the server (via AJAX) and then how to loop over them on the server side?

Reply to this Comment

@Ben,

I figured out what was wrong... syntax. Here is the solution to came up with and it is working great. I was making this way harder than it really was...

// I'm looping over my ids
<cfloop index="i" list="#attributes.id#" delimiters=",">
// then I set my db_names to the appropriate form value (this is the syntax issue I managed to resolve
<cfset LiveDate = form["LiveDate_" & i] />
<cfset PSA = form["PSA_" & i] />
<cfset iPSA = form["iPSA_" & i] />
...
// then I just perform the update query
<cfquery name="rsUpdateSystemDetails" datasource="#dsn#">
update table
set LiveDate = <cfqueryparam cfsqltype="CF_SQL_DATE" value="#LiveDate#">,
PSA = <cfqueryparam cfsqltype="cf_sql_varchar" value="#PSA#" maxlength="50">,
iPSA = <cfqueryparam cfsqltype="cf_sql_varchar" value="#iPSA#" maxlength="50">,

...
where id = <cfqueryparam cfsqltype="cf_sql_integer" value="#i#">
</cfquery>

Reply to this Comment

Thank you! I've been cursing at CF for the way it formats queries into JSON a lot. This just made my life simple and clean again. Love your blogs. You should write a book with tips and tricks.

Reply to this Comment

Just in case anyone else came across this, I did not want my previous post to throw anyone off. Your blog post is referring to how to handle data coming back from a ajax call where I was asking how to deal with the data being sent via an ajax/json post. Posting data to be stored in a database is the solution I posted above...

Reply to this Comment

Is there a benefit to doing this in Jquery as opposed to CF within the CFC? Pros and cons?

Reply to this Comment

Ben, thank you much for this AWESOME post. I've learned so much and can't wait to apply these techniques. Thank again and keep up the great work!

Reply to this Comment

Is there an issue using this on a query returning a single row? I seem to get a JS error when I attempt to do that.

TypeError: invalid 'in' operand b
...0;b<c.DATA.length;b++){(function(f){var e={};a.each(c.COLUMNS,function(g,h){e[h]...

Reply to this Comment

Post A Comment

?
You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.