ColdFusion's query object is great for working with complex, multi-column data. When you only need to operate on one column, however, it sometimes makes more sense to convert it to an array.

Although sure someone had already written code to accomplish this (they had - Peter Farrell's queryColumnToArray on CfLib for example), I decided it would be just as quick to knock out my own.

Note: all of the code below assumes CF9.0.1+

array function arrayFromQueryColumn( required query q,required string columnName )
{
	var queryCount = arguments.q.recordcount;
	var result = [];
	for( var i=1; i LTE queryCount; i++ )
	{
		ArrayAppend( result,arguments.q[ arguments.columnName ][ i ] );
	}
	return result;
}

But after reading an answer on StackOverflow by Adam Cameron today I've realised there's a simpler approach using built-in functions.

Query columns are arrays already (sort of)

As Adam explains, CF query columns can often be treated as arrays, allowing you to apply functions such as ArrayLen() directly, e.g. rows = ArrayLen( q[ "columnname" ] );

So could I reduce my function body to just one line?

array function arrayFromQueryColumn( required query q,required string columnName )
{
	return arguments.q[ arguments.columnName ];
}

Sadly no. CF will return the first row of the query only (my hunch is that this is a kind of safety valve in case you output a query column as a simple variable without wrapping it in <cfoutput query="q"> or <cfloop query="q">, but I might be wrong).

However, the full array can be extracted using a combination of two built-in functions: ValueList() and ListToArray().

listOfColumnValues = ValueList( q.myColumn );
fullArray = ListToArray( listOfColumnValues );

Potential problems

There are a couple of problems with this though.

1. If any of the column values contain commas, the column will be split into multiple elements in the array.
2. If any of the column values are empty then those columns won't appear in the array.

To illustrate, let's create a query with a comma in its first column value, and an empty third column:

q = QueryNew( "" );
names = [ "a,a","b","" ];
QueryAddColumn( q,"name","VarChar",names );

Now let's convert that to an array using the two functions:

listOfColumnValues = ValueList( q.name );
fullArray = ListToArray( listOfColumnValues );

The first column has been split into two, and the third empty value is missing.

Addressing these two issues is fairly easy.

1. Specify a list delimiter that won't appear in your data: the pipe character | is often a good choice.
2. Tell the ListToArray() function to includeEmptyFields, its third parameter.

listOfColumnValues = ValueList( q.name,"|" );
fullArray = ListToArray( listOfColumnValues,"|",true );

The array now matches the original query. Job done.

Dynamic column names

Well, not quite in my case. This works fine where you know the name of the query column, but my conversion function won't have that information: the column name needs to be passed in as an argument to allow it to work with any query. Unfortunately ValueList() does not work with dynamic column names.

columnName = "name";
listOfColumnValues = ValueList( q[ columnName ],"|" );

Complex constructs are not supported with [the] function ValueList.
Use [a] simple variable instead.

The solution

We can get round this by replacing ValueList() with ArrayToList() which will happily operate on query columns expressed using array notation.

array function arrayFromQueryColumn( required query q,required string columnName )
{
	var listOfColumnValues = ArrayToList( arguments.q[ columnName ],"|" );
	return ListToArray( listOfColumnValues,"|",true );
}

fullArray = arrayFromQueryColumn( q,"name" );

Which means I can get my one-liner after all.

array function arrayFromQueryColumn( required query q,required string columnName )
{
	return ListToArray( ArrayToList( arguments.q[ columnName ],"|" ),"|",true );
}

As a bonus, testing the old looping function against the new version suggests the latter is significantly faster for large (500+) recordsets on initial runs at least (tested on CF9.0.1, 32-bit Windows). Once caching kicks in though, the speed difference seems to be negligible.