There is currently no built-in QueryRenameColumns() function in either Lucee or Adobe ColdFusion (ACF).

Which is a pity because query column names can be problematic in some situations, particularly with ACF.

For instance, say you want to create a query object programmatically like this:

newQuery = QueryNew( "1stName,last name", "VarChar,VarChar", [ [ "Frumpo", "McNugget" ] ] );

This works in Lucee, but ACF errors with

The column name 1stName is invalid

In fact neither of the column names are acceptable to ACF because they don't follow the rules for valid variable names (the first starts with a number and the second contains a space). Why this is so I'm not sure since they are literals not variables, but there it is.

How to get round it? Well in the Lucee Spreadsheet Library (which in case you didn't know, also supports ACF2016+) I've been using a method on the underlying query Java object, available in both CFML engines

query.setColumnNames().

This allows you to change all of the column names after the query has been created.

So the workaround is to create the query with safe column names, such as column1, then change them, like so:

newQuery = QueryNew( "column1,column2", "VarChar,VarChar", [ [ "Frumpo", "McNugget" ] ] );
newQuery.setColumnNames( [ "1stName", "last name" ] );
WriteDump( newQuery );

Which works.

But there are a couple of problems with this approach.

1. query.setColumnNames() is not officially supported in either engine. It's highly unlikely to be removed, but it's a risk theoretically.
2. ColdFusion sets the names in UPPERCASE.

The second point may not have much impact in many cases, but in mine these generated queries are often transformed into, or result from, spreadsheets or CSV files. To have the column/header names changed in any way is not ideal.

queryRenameColumns()

Searching for an alternative led me to an ingenious suggestion from Adam Cameron, which is to convert the query to JSON, edit the column(s), then convert it back.

Worked up as a UDF it looks like this:

query function queryRenameColumns( required query query, required array columnNames, required array newColumnNames ){
	// throw an error if the number of old and new names don't match
	if( arguments.columnNames.Len() != arguments.newColumnNames.Len() )
		Throw( message: "Column name mismatch", detail: "The number of column names to change doesn't match the number of new names" );
	// convert the query to JSON
	var queryJson = SerializeJSON( arguments.query );
	// get the current set of columns as an array so we can work on it
	var columns = GetMetaData( arguments.query ).Map( function( item ){
		return item.name;
	});
	// find and rename the specified columns
	var newColumns = columns.Map( function( item ){
		var foundPosition = columnNames.FindNoCase( item );
		return foundPosition? newColumnNames[ foundPosition ]: item;
	});
	// convert the original and changed column arrays to text
	var columnsJson = SerializeJSON( columns );
	var newColumnsJson = SerializeJSON( newColumns );
	// do a simple string replace (no RegExp required)
	var queryJsonColumnRenamed = queryJson.Replace( 'COLUMNS":' & columnsJson, 'COLUMNS":' & newColumnsJson );
	// convert the JSON back to a query object
	return DeserializeJSON( queryJsonColumnRenamed, false );
}

View UDF as a gist

I'll admit this is a bit convoluted, and Adam cautions against doing this with a large amount of data. Performance will certainly depend on the size of the query, but I tested it with a 100,000 row/10 column query on ACF 2016 and it only took around 300 milliseconds. A 10K row/10 column query clocked in at circa 20ms.

Here's how we can deal with the generated query now, specifying the column names we want:

newQuery = QueryNew( "column1,column2", "VarChar,VarChar", [ [ "Frumpo", "McNugget" ] ] );
newQuery = queryRenameColumns( newQuery, [ "column1", "column2" ], [ "1stName", "last name" ] );
WriteDump( newQuery );

Query of Queries

The question Adam was answering presents another scenario where being able to change column names would be handy in ACF: Query of Queries (QoQ).

When querying an existing query in Adobe ColdFusion, it seems you can't currently reference a column name which contains a space. Square brackets are normally used to escape characters in QoQ, but if you were to take the newQuery above and attempt to select the "last name" column you'd get an error.

QoQ = QueryExecute( "SELECT [last name] FROM newQuery", {}, { dbtype: "query" } );
WriteDump( QoQ );

This works fine in Lucee, but in ACF results in:

Query Of Queries syntax error. Encountered "[. Incorrect Select List, Incorrect select column.

Quotes or back-ticks don't work either.

The UDF allows you to work around this by renaming the column with an underscore replacing the space:

newQuery = queryRenameColumns( newQuery, [ "last name" ], [ "last_name" ] );
QoQ = QueryExecute( "SELECT last_name FROM newQuery", {}, { dbtype: "query" } );
WriteDump( QoQ );