Beyond running SQL, ColdFusion has functions for building a query object entirely in code — useful for tests, mock data, or reshaping data from a non-database source into something you can loop through, sort, or query with Query of Queries.
It also has functional-style operations for queries, following the same pattern as the array and struct versions covered earlier — a closure runs against every row instead of a manual loop.
Learning Objectives
After completing this lesson, you'll be able to:
- Build a query object from scratch with queryNew().
- Add rows and set cell values with queryAddRow and querySetCell.
- Run a function for every row with queryEach.
- Transform and filter query rows with queryMap and queryFilter.
- Combine a column's values into a single result with queryReduce.
- Extract a column's values as a delimited string or array with valueList and valueArray.
Creating a Query: queryNew
The most direct way — pass a column list, a matching type list, and the row data as an array of structs, all at once.
Building a Query Row by Row: queryAddRow and querySetCell
For when the data isn't all available up front — build the structure first, then fill it in one cell at a time.
queryAddRow can also take a struct or array of structs directly — queryAddRow(news, {id: 1, title: "..."}) — skipping the need for separate querySetCell calls.
queryEach — Run Something for Every Row
The callback receives (row, currentRow, query) — row is a struct with all of that row's columns.
queryMap — Transform Every Row
The callback must return the (possibly modified) row — queryMap builds a new query from whatever each call returns.
queryFilter — Keep Only Matching Rows
queryReduce — Combine Into a Single Value
Same pattern as arrayReduce and structReduce — the accumulated result comes first in the callback, and an explicit starting value (0 here) is always worth passing.
Extracting a Column: valueList and valueArray
valueList returns a delimited string (comma by default); valueArray returns an actual array — pick whichever the rest of your code needs.
Real-World Example: Building a Report Row by Row
Common Beginner Mistakes
Mismatching queryNew's column list and type list
queryNew("id,title", "integer") has two columns but only one type — always keep both lists the same length and in the same order.
Forgetting queryMap's callback must return the row
Modifying row inside the closure without returning it leaves the resulting query's data unset for that row — always return row (or a replacement) at the end of the callback.
Forgetting queryReduce's starting value
Without the third argument, the very first row becomes the starting point instead of running through the callback — usually not what you want for a numeric total starting from 0.
Best Practices
- Use queryNew() with the full column/type/data form when you have all the data up front, and queryAddRow/querySetCell only when building incrementally.
- Use valueList when you need a delimited string (e.g., for a cfqueryparam list), valueArray when you need to keep working with the data as an array.
- Use the functional query operations (queryEach/Map/Filter/Reduce) the same way you would with arrays — for building a new query, prefer queryMap/queryFilter over a manual loop.
Interview Questions
What are the three arguments to queryNew()?
A column list, a matching column type list, and optionally the row data (as a struct, array of structs, or array of arrays).
What does queryFilter's callback need to return?
A Boolean — true to keep that row in the result, false to exclude it.
What's the difference between valueList and valueArray?
valueList returns a delimited string of a column's values. valueArray returns the same values as an actual array.
What does queryMap's callback need to return?
The (possibly modified) row — queryMap builds its resulting query from whatever each call returns.
Summary
In this lesson, you learned to build query objects with queryNew, queryAddRow, and querySetCell, and to transform them with the functional queryEach, queryMap, queryFilter, and queryReduce — plus valueList and valueArray for pulling out a single column's values.
This wraps up Queries for this module.
What's Next?
The next lesson covers JSON — converting between ColdFusion data structures and JSON, for APIs and data interchange.