Skip to main content
Ben Nadel at the New York ColdFusion User Group (Nov. 2009) with: Pete Freitag
Ben Nadel at the New York ColdFusion User Group (Nov. 2009) with: Pete Freitag ( @pfreitag )

Ask Ben: Converting a Query to an Array

By on

A new ColdFusion programmer emailed me recently asking:

How can I convert a ColdFusion query into an array?

When it comes to this type of conversion, there are a few things that you have to take into account. For starters, is it worth it? The ColdFusion query object is a very powerful, very flexible array-like object to begin with. It can already be accessed directly without having to loop over the record set. Is this conversion something you really need to do? Second, if you do want to make the conversion, what structure do you want? You can either mimic the existing query notation by creating a "Structure of Arrays" or you can do what I feel is a more natural feeling conversion to create an "Array of Structures".

To begin with, let's cover how you can access a query object as a structure. Once you see this, you might realize that no conversion is needed at all. The ColdFusion query object can be accessed directly like a structure of arrays:

<cfset strValue = qRecords[ COLUMN_NAME ][ ROW_NUMBER ] />

The first index is the key value of the column you want to access, for example, "id" or "name". The second index value is the row for which you want to access that column. If you want to see that in english, think of it as "I want the value in the COLUMN_NAME column of the ROW_NUMBER row. After seeing this, you might not want to go through the processing overhead of converting the query object into any other structure as it is pretty awesome to start with.

After this, if you still want to convert the query object in to another structure, I suggest going to an array of structures. This adds memory overhead (over the structure of arrays method) since each row must have a copy of the column names (as look-up keys), but I think it is a much more natural way of thinking about the query set (especially if you are a traditional programmer).

In order to convert a query into an array, you basically have to create an array, loop over every row in the query record set, create a structure of the values in that row, and then append that structure to the array:

<cffunction name="QueryToArray" access="public" returntype="array" output="false"
	hint="This turns a query into an array of structures.">

	<!--- Define arguments. --->
	<cfargument name="Data" type="query" required="yes" />

	<cfscript>

		// Define the local scope.
		var LOCAL = StructNew();

		// Get the column names as an array.
		LOCAL.Columns = ListToArray( ARGUMENTS.Data.ColumnList );

		// Create an array that will hold the query equivalent.
		LOCAL.QueryArray = ArrayNew( 1 );

		// Loop over the query.
		for (LOCAL.RowIndex = 1 ; LOCAL.RowIndex LTE ARGUMENTS.Data.RecordCount ; LOCAL.RowIndex = (LOCAL.RowIndex + 1)){

			// Create a row structure.
			LOCAL.Row = StructNew();

			// Loop over the columns in this row.
			for (LOCAL.ColumnIndex = 1 ; LOCAL.ColumnIndex LTE ArrayLen( LOCAL.Columns ) ; LOCAL.ColumnIndex = (LOCAL.ColumnIndex + 1)){

				// Get a reference to the query column.
				LOCAL.ColumnName = LOCAL.Columns[ LOCAL.ColumnIndex ];

				// Store the query cell value into the struct by key.
				LOCAL.Row[ LOCAL.ColumnName ] = ARGUMENTS.Data[ LOCAL.ColumnName ][ LOCAL.RowIndex ];

			}

			// Add the structure to the query array.
			ArrayAppend( LOCAL.QueryArray, LOCAL.Row );

		}

		// Return the array equivalent.
		return( LOCAL.QueryArray );

	</cfscript>
</cffunction>

To test, let's set up a simple query using an ID column and a NAME column:

<!--- Set up the query for testing. --->
<cfset qTest = QueryNew( "id, name" ) />

<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "1" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "molly" />

<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "2" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "Sophia" />

<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "3" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "Stefie" />

<cfset QueryAddRow( qTest ) />
<cfset qTest[ "id" ][ qTest.RecordCount ] = "4" />
<cfset qTest[ "name" ][ qTest.RecordCount ] = "Maud" />

Now, to convert that query to an array of structures, we simple call the QueryToArray() method and pass in the query as an argument.

<!--- Convert the query to an array. --->
<cfset arrTest = QueryToArray( qTest ) />

Dumping out the array, you can clearly see that it is an array or structures. Each structure at each array index has keys correlating to the query column.

Want to use code from this post? Check out the license.

Reader Comments

9 Comments

Thank you for this.

"For starters, is it worth it?"
Yes, if you need to dynamically access the columns.
MyQuery[variables.col] unfortunately only works with Structs. :(

2 Comments

Keeping up with 'Converting a Query to an Array' is tough nowadays...As part of the relaunch of their site, the folks at depressedpress.com (http://www.depressedpress.com) are presenting a series of CFML challenges. The first is converting a Query to an Array using only CFML as fast as you can. Merry Christmas of Poland!

1 Comments

With regard to the comment:
MyQuery[variables.col] unfortunately only works with Structs. :(

Yes you can, but you need to suffix it with the row that you want, so it'd be MyQuery[variables.col][row]

If you're looping through the rows then just use the loop variable, or if you're inside a cfoutput or cfloop then just use currentRow.

7 Comments

I was pulling my hair out trying to access a query column dynamically and saw this post. Thanks much for the tip!
It's funny how many years you can program CF and still find something new.

1 Comments

THAT is exactly what i´m looking for! cool you are my daysaver, thank you very much for sharing its a great tutorial. merry xmas.

25 Comments

Yes, if you need to dynamically access the columns.

Couldn't one also use the evaluate() function to dynamically access the columns? E.g.,

<cfset temp = evaluate("MyQuery.#variables.col#[#index#]")>

IIRC, inside a CFLOOP or CFOUTPUT of MyQuery one would only need to do the following:

<cfset temp = evaluate("#variables.col#")>

I actually prefer building queries (using queryNew(), queryAddRow(), etc.) to using arrays of structures or structures of arrays because queries are so powerful.

15,663 Comments

@Stju,

Did you actually test this? I ask because there is a fatal flaw in it - you are using the same Row struct for every row. Since Structs are passed by reference, every subsequent update you make to it will change EVERY row already added to the array.

I just copy-pasted your code to confirm this. When I CFDump, every row is the same as the last one.

I am going to delete your comment so as not to mis-inform people. I appreciate the effort though.

7 Comments

What about using the built in listToArray and ValueList functions?

myArray = ListToArray( valueList(myQuery.columnName) )

If there is any chance your values could contain a comma, then use a different delimiter:

myArray = ListToArray( valueList(myQuery.columnName,Chr(31) ),Chr(31) )

15,663 Comments

@Ryan,

You could do that; but, that organizes the arrays column-wise, not row-wise. Not saying that's a problem - just pointing out the difference.

1 Comments

Not sure if anyone knows but to access dynamic column names you can do something similar to the following (quote and pound the dynamic column name as first array type element of query):

<cfset var cnt = 0>
<cfloop query="qry">
<cfset cnt++>
<cfloop from="1" to="#listLen(myDynamicFieldList)#" index="i">
	cfset myDynamicColumn = qry["#listGetAt(myDynamicFieldList, i)]#"][cnt]>
</cfloop>
</cfloop>
1 Comments

I recently encountered an issue where I needed to supply a case sensitive field name (DT_RowId) to jQuery DataTables plugin. The field names I got from using the QueryToArray function were all uppercase because ColdFusion internally stores struct keys in uppercase. To get around, change the following:

LOCAL.Columns = ListToArray(ARGUMENTS.Data.ColumnList );

to

LOCAL.Columns = data.getMetaData().getColumnLabels();

Hope this helps anyone who run into this issue.

**References: **

http://blog.pengoworks.com/index.cfm/2011/3/3/Easy-AJAX-using-ColdFusion-jQuery-and-CFCs

http://existdissolve.com/2010/11/quick-coldfusion-goodness/

http://datatables.net/release-datatables/examples/server_side/ids.html

2 Comments

Thanks for sharing this. I would change
This line from

LOCAL.Row[ QtA.ColumnName ] = LOCAL.Qry[ LOCAL.ColumnName ][ LOCAL.RowIndex ];

To

StructInsert( LOCAL.Row, LOCAL.ColumnName, LOCAL.Qry[ LOCAL.ColumnName ][ LOCAL.RowIndex ] );

To allow better control of the case values when working with JSON/Javascript.

1 Comments

Hey Ben,

I know this is a very old post but because it describes a common issue you should consider updating this post to incorporate Kamil Saiyed's suggestion which preserves the case of property names which is obviously very useful for web development.

All the best,

I believe in love. I believe in compassion. I believe in human rights. I believe that we can afford to give more of these gifts to the world around us because it costs us nothing to be decent and kind and understanding. And, I want you to know that when you land on this site, you are accepted for who you are, no matter how you identify, what truths you live, or whatever kind of goofy shit makes you feel alive! Rock on with your bad self!
Ben Nadel