In my previous blog, I had shown with examples, how you’d use map, filter, and reduce on arrays to manipulate values in an array. To reiterate, map transforms the values in an array (or struct or list), filter filters a value that satisfies a condition, and reduce reduces the values to a single value.

In this blog, we shall see how to apply map, filter, and reduce functions on query objects.

Map

The map function iterates over each row of the query object and calls a closure function to work on each row of the query. The result is a query object with transformed values, with each value set at the same index.

Example

<cfscript>
// create query object emp
emp = queryNew("id,name,salary","integer,varchar,integer", [{"id": 1,"name": "Jon Snow","salary": 100000}, {"id": 2,"name": "John Adams","salary": 90000}, {"id": 3,"name": "Mona Lisa","salary": 110000}]);
// original array
writeDump(emp);
// define closure function mapQuery
function mapQuery(item) {
item.name=item.name & " gets a salary of " & item.salary;
return item;
}
// apply map function
newQuery = emp.map(mapQuery);
// transformed query
writedump(newQuery);
</cfscript>

Output

Before using map:

After using map:

Filter

The filter function iterates over each row of the query object and only displays the rows for which the closure function returns TRUE. The output depends on the filter you specify.

Example

<cfscript>
myResult=QueryExecute(("SELECT * FROM EMPLOYEES WHERE EMP_ID BETWEEN :low AND :high"),{low=4,high=14},
{datasource="cfdocexamples"});
//writedump(myresult);
filteredResults=myResult.filter(function(obj){
return obj.EMP_ID>4; // apply filter
});
writedump(filteredResults);
</cfscript>

Output

Reduce

The Reduce function iterates over each row of a query and calls the closure function to work on each row of the query. This function reduces the query to a single value that is returned.

Example

<cfscript>
myResult=QueryExecute("SELECT * FROM EMPLOYEES",[],{datasource="cfdocexamples"});
myArray=ArrayNew(1);
// Populate myArrray with random values between 25-45
for (i=1;i<=myResult.recordcount;i++){
myArray[i]=randrange(25,45);
}
// Add column Ages and populate with values of myArray
QueryAddColumn(myResult,"Ages","integer",myArray);
writedump(myResult);
// Use reduce function to calculate the average age of all persons in the recordset
// Initialize age in closure
findAverage=myResult.reduce(function(age,item){
return age+item.Ages/myResult.recordcount;
},0);
WriteOutput("Average age is: " & findAverage);
</cfscript>

Right off the bat, you can see a lot going on in the code. So, let us deconstruct the code.

1. We run a query on the table EMPLOYEES in the datasource cfdocexamples. We store the results of the query in myResult.
2. We create a one-dimensional array, myArray.
3. We then populate the array with random values from 25 to 45. The length of the array is the same as that of the query object.
4. Next, we add a column Ages of type integer to myResult. We also populate Ages with values of myArray.
5. We introduce the reduce function on myResult that takes two parameters age and item. In the function, we initialize the value of age as zero and use item to iterate over each record in the Ages column.
6. The function returns the average age of all persons in the query object.
7. We then display the result of the reduce function.