Bharat Sep,07 2017
Development techniques are varied depend on developer, somewhat many techniques remains undocumented until developer shares it. Let us share something about CFQUERY in Lucee CFML. Modern web apps are moving on Ajax based development includes AngularJS, ReactJS, VueJS and a few more.
API solution primarily requires JSON based response (It is not about XML), JSON based response to convert CFQUERY object into JSON object. While converting into JSON using SerializeJSON() function, There are two different keys COLUMNS AND DATA and those are not helping much and not useful.
The default output for query serialization to JSON looks like as below:
This out put is not what is expected. An own custom methods to apply in order to convert query output into an array. Later on here you will find more about custom solution for this.
Let's talk first from the latest version of CFML ColdFusion 11/Lucee 5 and above. How you can get exact JSON output from the query. The JSON should look like more familiar as below.
In Coldfusion 11 and above, there is a second parameter, called "Struct" in SerializeJSON(). For example SerializeJSON(custStruct, "Struct") produces a JSON as above. If you are calling CFC via URL then you need to pass queryformat=struct. You can get more details here (TLD;R).
There is no new argument parameter in Lucee. In order to get similar JSON, you need to add an attribute in CFQUERY tag called returnType="array" (FYI, this attribute is not valid on Abode Coldfusion) that will returns array of struct instead of query object and then serialize it.
The returnType="Array" is undocumented as on date. It is a faster than query object. Especially in hibernate. As per discussion on LUCEE ISSUE SYSTEM. I haven't tested but it should be.
[Update on 2017-12-01]: Make sure, if you are writting query inside the CFFUCTION then you need to set returnType="array" or returnType="any" to prevent error.
Often, a client isn't upgrading Adobe ColdFusion Server because of Paid license or to continue support for older products or may be some other reasons whereas Lucee is open source so you can keep server up to date(But need to do proper test before applying patch as some of things might be deprecated or removed or might change behaviour). So what to do. Nothing folks... Apply our own custom function that converts query object into an array. Let me confirm here, I haven't tested below custom function in Abode Coldfusion as my clients are using Lucee but It might be work on as well. Here I came with two different solutions that are "One better than another".
A very old row level method that might work on all versions of CFML.
As I am using Lucee version 4.3, I couldn't add returnType="Array" but yes I can use above solution that returns an array of structure from query object. After playing around some inbuilt Lucee function, I came with another solution that also returns an array of a structure having a huge performance improvement. As I mentioned earlier that I used Lucee version 4.3 so I haven't tested on Adobe Coldfusion but hopefullt it may work with Coldfusion 10 and above.
Above examples for CFQUERY serializeJSON with key and value will be helpful to you.
Finally, An additional parameter STRUCT is added in SerializeJSON() function by Lucee team. You have to update Lucee software to Version 126.96.36.199.
Now, no more custom function need to convert QUERY OBJECT into Array of Struct. Also, don't need to add RETURNTYPE attributes in CFQUERY.
SerializeJSON( myQry, 'struct' );