Coldfusion CFQUERY serializeJSON with key and value

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:

{“COLUMNS”:[“FNAME”,”AGE”],”DATA”:[[“Bharat”,28],[“Pritesh”,33],[“Dushyant”,34]]}

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.

[{“FNAME”:”Bharat”,”AGE”:28},{“FNAME”:”Pritesh”,”AGE”:33},{“FNAME”:”Dushyant”,”AGE”:34}]

Adobe Coldfusion 11 and above.

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).

Lucee 5 and above.

Learn more about ColdFusion
Development

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.

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.

Update on 29 Sept 2017

Finally, An additional parameter STRUCT is added in SerializeJSON() function by Lucee team. You
have to update Lucee software to Version 5.2.3.7.

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’ );

Learn More about ColdFusion Development