Looping Over ColdFusion JSON Queries In jQuery

Posted November 16, 2009 at 9:45 AM

Tags: ColdFusion, Javascript / DHTML

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

Britta
Nov 16, 2009 at 11:43 AM // reply »
1 Comments
10 Points

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.


Brian Lang
Nov 16, 2009 at 11:45 AM // reply »
35 Comments
370 Points

Looks useful. Going to try this on my next AJAX project.


Ben Nadel
Nov 16, 2009 at 11:51 AM // reply »
8,777 Comments
49,491 Points

@Britta, @Brian,

Cool.


Sami Hoda
Nov 16, 2009 at 2:15 PM // reply »
3 Comments
24 Points

Very nice Ben. This is extremely useful.


Ben Nadel
Nov 16, 2009 at 2:17 PM // reply »
8,777 Comments
49,491 Points

@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.


Mark Morrison
Nov 16, 2009 at 5:34 PM // reply »
4 Comments
41 Points

Ben,

Your tutorials are always so well written. Great info. I learned more than I expected.


Ben Nadel
Nov 16, 2009 at 5:47 PM // reply »
8,777 Comments
49,491 Points

@Mark,

Wooohooo! Thanks, that's awesome.


Michael Mosler
Nov 17, 2009 at 8:53 AM // reply »
1 Comments
10 Points

Thanks! Works like a charm


Ben Nadel
Nov 17, 2009 at 8:54 AM // reply »
8,777 Comments
49,491 Points

@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.


Pritesh Patel
Nov 17, 2009 at 1:47 PM // reply »
1 Comments
12 Points

Thanks for such pretty useful code. I really love this.


Hakan
Nov 18, 2009 at 6:41 AM // reply »
1 Comments
12 Points

Thank you for your very useful code


Daniel Lancelot
Mar 17, 2010 at 8:39 PM // reply »
1 Comments
10 Points

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


Ben Nadel
Mar 19, 2010 at 8:50 AM // reply »
8,777 Comments
49,491 Points

@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.



Post A Comment

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.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:
Formatting: <strong>bold</strong> <em>italic<em>







  • Help Wanted - Find Your Next ColdFusion Job
Recent Blog Comments
Philip Bedi
Sep 3, 2010 at 5:48 AM
Scope Behavior When Using CFThread Inside Of ColdFusion Components
Thanks Ben, Excellent article and very precise explanation. Cheers Philip A question on invoking asynchronous save or some task and returning response back to the calling page. Using cfThread is ... read »
Alex
Sep 3, 2010 at 3:04 AM
Long Polling Experiment With jQuery And ColdFusion
@Ben, Thank you for your answer. If you are interested in - I solved the problem. It was, as you said, a buffer issue. Now when I'm getting a new request, the first thing I do is I'm sending some fa ... read »
Shahab
Sep 3, 2010 at 1:29 AM
Using jQuery's SlideUp() and SlideDown() Methods With Bottom-Positioned Elements
Hey Ben, Thanks for clearing this up! Also, is there a way for the container to be open when you first load the page, so that when u click on the link it will slideUp? ... read »
Pat Barbano
Sep 3, 2010 at 12:29 AM
Bidirectional Data Exchange With ColdFusion's CFThread Tag
Thanks for posting this example, Ben. I plan to put something like this to use. I want to spawn up a thread to insert several records (possibly 1000s) into a database incrementing a counter upon ea ... read »
Mardy
Sep 2, 2010 at 11:23 PM
Experimenting With HTML5's Cache Manifest For Offline Web Applications
Hi Ben, having checked all articles on Html5's appCache, is there a solution to just update newer files, using the manifest file? I am looking into using application cache to actually have an offline ... read »
Ben Nadel
Sep 2, 2010 at 3:11 PM
Long Polling Experiment With jQuery And ColdFusion
@Alex, It looks like some of the browsers implement some sort of buffering on the data request. I was definitely finding different behavior across browsers. I want to come back and figure this code ... read »
Ben Nadel
Sep 2, 2010 at 3:09 PM
Creating Base64-Encoded Data URLs For Images In ColdFusion
@Randall, At the very least, I think Chrome won't be able to close windows unless it opens them up, right? I am not sure. ... read »
Andy
Sep 2, 2010 at 2:17 PM
ColdFusion NumberFormat() Exploration
Ben - I have same question as Jim and I think maybe you misread it? I want numbers with non-zero decimal places to display the decimal, but those that have no decimal to display w/o the decimal poin ... read »