I have used various HTML5 widgets extensively for the last decade and will share the functions that I use to convert ColdFusion objects returned from a database into JSON. In this article, I will highlight some of the logic in the code and show you how to use this for a variety of use cases.

Table of Contents

What Does This Component Do?

These functions are a critical component of all of my HTML5 applications. These functions will convert both a ColdFusion query as well as a ColdFusion ORM array object into JSON that is passed back to the calling Ajax function. Unlike ColdFusion's native JSON functions, this will return the column names in the proper case rather than returning the column names in uppercase.

The function was originally created by Adrian Moreno. I modified this function nearly a decade ago and has been in use in several production environments over the last 10 years.  It is also used extensively in my open-source Galaxie Blog which is an HTML5 application. These two functions have been tested thoroughly and have handled server-side data operations on nearly every jQuery-based HTML5 widget that I have used. 

There may be something in the CF world that is a little more modern than this, however, I have written these functions to handle all of the common use cases that I have found when preparing JSON data. For example, some widgets want a data handle in the JSON, while others don't. These functions have handled all use cases that I have thrown at them when using Kendo UI and have handled other HTML5 libraries, such as jsGrid. I have tested a dozen different similar functions and this approach offered the best performance.

I am also using this component extensively in my how-to ColdFusion and Kendo blog series. If you are working with Kendo UI while reading this series, please download this component.

Download the CFJson Component from GitHub

This component can be found on GitHub at https://github.com/GregoryAlexander77/CfJson. It has been tested on ColdFusion 10 all the way through the most modern ColdFusion version, CF2021.

Working with ColdFusion Queries

When dealing with a native ColdFusion query object, use the convertCfQuery2JsonStruct function to convert it into JSON. This function takes a native ColdFusion query object and converts it into JSON.

There are several arguments, the queryObj is required, and the rest of the arguments are optional. 

Function Arguments

  • queryObj (required)
    Pass in the name of the ColdFusion query. This must be a ColdFusion query object.

  • contentType 
    Default value: json
    This argument, for now, is always set to json. This argument was put into the function as eventually, I hope to add JSONP support. 

  • includeDataHandleName
    Default value: false
    This inserts a data handle in front of the JSON. It is used for special use cases when using the Kendo widgets, and also is used for other HTML libraries, such as jsGrid.

  • dataHandleName
    Default value: false
    Specify your data handle name when setting the includeDataHandle argument to true.

  • includeTotal 
    Default value: false
    Used for the Kendo Grids and for Pagination.

  • overRideTotal
    Default value: false
    Used for pagination when filtering records in a Kendo Grid. 
  • newTotal
    Default value: false
    Used to indicate the total number of records after applying filters to a Kendo Grid. 

  • removeStringHtmlFormatting
    Default value: false
    Removes HTML and special characters in the JSON. This is used to create a sanitized string that is displayed in a grid (works with both Kendo and jsGrid grids)

  • columnThatContainsHtmlStrings
    default value: empty string
    When removeStringHtmlFormatting is set to true, specify the column that you want to be sanitized.

  • convertColumnNamesToLowerCase
    Default value: empty string
    This argument is rarely used. In certain situations, you may want to force all of the JSON element names to a lower case to avoid any case sensitivity issues.

Example ColdFusion Query

Here is a simple query that grabs the Galaxie Blog built-in roles. Here we are getting the role Id, name, and description and output this to JSON to populate our HTML5 widgets.

After obtaining the data, we will pass the name of this query, Data, to the convertCfQuery2JsonStruct method to convert this ColdFusion query into a JSON string. This query will be used for all of the examples below using the convertCfQuery2JsonStruct method that converts a ColdFusion query object into a JSON object.

Note: I am only getting the top two records to make it easier to view the JSON output below.

<!--- Make the query --->
<cfquery name="Data" datasource="#dsn#">
	SELECT TOP 2 RoleId
			,RoleName
			,Description
		FROM Role
</cfquery>

Common Usage Using Default Settings

This takes the ColdFusion query object that we just made, in this case, "Data", and it converts it into JSON without a total, data handle or any special formatting.

This particular usage supports most of the Kendo Widgets, other than the Kendo Grid or other specialized use cases. All of the other arguments are set at default and are not used. 

<!--- Convert the query object into JSON using the default parameters of convertCfQuery2JsonStruct method --->
<cfinvoke component="#application.cfJsonComponentPath#" method="convertCfQuery2JsonStruct" returnvariable="jsonString" >
	<cfinvokeargument name="queryObj" value="#Data#">
</cfinvoke>

This is what the JSON that the function returns when using the default arguments:

[
   {
      "Description":"All functionality.",
      "RoleId":1,
      "RoleName":"Administrator"
   },
   {
      "Description":"Can create and edit their own posts.",
      "RoleId":2,
      "RoleName":"Author"
   }
]

Getting the JSON with a Data Handle

If you need to use a data handle, use the following arguments:

<!--- Convert the query object into JSON --->
<cfinvoke component="cfJson" method="convertCfQuery2JsonStruct" returnvariable="jsonString" >
	<cfinvokeargument name="queryObj" value="#Data#">
	<cfinvokeargument name="contentType" value="json">
	<cfinvokeargument name="includeTotal" value="false">
	<!--- Don't include the data handle for Kendo grids ---> 
	<cfinvokeargument name="includeDataHandle" value="true">
	<cfinvokeargument name="dataHandleName" value="myData">
	<!--- Set to true to force the column names into lower case. --->
	<cfinvokeargument name="convertColumnNamesToLowerCase" value="false">
</cfinvoke>

Using this Function with ColdFusion ORM HQL Query

This component also works when using ColdFusion ORM using the convertHqlQuery2JsonStruct method. This method is identical to the convertCfQuery2JsonStruct method, the only difference is that it takes an HQL query object instead of using a native ColdFusion query.

Example ColdFusion HQL Query

I won't go into the details of using HQL, but here we are using the map keyword in the HQL to return an array of Java HashMaps. This query should be understandable to ColdFusion developers it looks nearly the same as using cfquery. 

<cfquery name="Data" dbtype="hql">
	SELECT new Map (
		RoleId as RoleId,
		RoleName as RoleName,
		Description as Description
	)
	FROM 
		Role as Role
</cfquery>

Function Output

Here is what the function returns:

[ 
   {
      "blogurl":"https://www.gregoryalexander.com/blog/",
      "blogdescription":"A technical blog powered by Galaxie Blog - the most beautiful and functional open source ColdFusion/Kendo UI based blog in the world.",
      "blog":"Gregory's Blog"
   }
]

Using this Function to Populate Kendo Virtual Grids

The convertCfQuery2JsonStructForVirtualGrid method is used when populating Kendo Virtual Grids. This particular function is similar to our other functions. However, the default usage will include the data-handle and override the total if a new query was made. You can force the columns to be returned in lower-case, however, the default setting returns the column names from the ColdFusion query object as they exist in the database.

The following arguments are typically used to invoke this component for virtual grids:

<!--- Using my jsonArray.cfc --->
<cfinvoke component="#application.cfJsonComponentPath#" method="convertCfQuery2JsonStructForVirtualGrid" returnvariable="jsonString" >
	<cfinvokeargument name="queryObj" value="#data#">
	<cfinvokeargument name="includeTotal" value="true">
	<!--- When we use server side paging, we need to override the total and specify a new total which is the sum of the entire query. --->
	<cfinvokeargument name="overRideTotal" value="true">
	<!--- We set the totalNumRecords near the top of the function, however, if the filters were applied, the new total will be the number of records in the data query object. ---> 
	<cfif searchFilter>
		<cfset totalRecords = data.recordcount>
	<cfelse>
		<cfset totalRecords = totalNumRecords>
	</cfif>
	<cfinvokeargument name="newTotal" value="#totalRecords#">
	<!--- The includeDataHandle is used when the format is json (or jsonp), however, the data handle is not included when you want to make a javascript object embedded in the page. ---> 
	<cfinvokeargument name="includeDataHandle" value="true">
	<!--- If the data handle is not used, this can be left blank. If you are going to use a service on the cfc, typically, the value would be 'data'--->
	<cfinvokeargument name="dataHandleName" value="data">
	<!--- Keep the case of the columns the same as the database --->
	<cfinvokeargument name="convertColumnNamesToLowerCase" value="false">
</cfinvoke>

<cfreturn jsonString>

Function Output

The following is an example output for our World Cities Virtual Grid when the initial grid is rendered. Notice that the total handle is included and the data handle is present:

{"total":129943,"data":[{"Currency":"AFN","CityFlag":1,"CityId":50,"CountryId":1,"Longitude":65.00000000,"CityLongitude":65.123760,"CurrencyName":"Afghan afghani","State":"Faryab","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":1,"StateFlag":1,"City":"Andkhoy","Country":"Afghanistan","StateId":3889,"CityLatitude":36.952930,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":51,"CountryId":1,"Longitude":65.00000000,"CityLongitude":71.146970,"CurrencyName":"Afghan afghani","State":"Kunar","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":2,"StateFlag":1,"City":"Asadabad","Country":"Afghanistan","StateId":3876,"CityLatitude":34.873110,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":52,"CountryId":1,"Longitude":65.00000000,"CityLongitude":71.533330,"CurrencyName":"Afghan afghani","State":"Badakhshan","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":3,"StateFlag":1,"City":"Ashk?sham","Country":"Afghanistan","StateId":3901,"CityLatitude":36.683330,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":53,"CountryId":1,"Longitude":65.00000000,"CityLongitude":68.015510,"CurrencyName":"Afghan afghani","State":"Samangan","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":4,"StateFlag":1,"City":"A?bak","Country":"Afghanistan","StateId":3883,"CityLatitude":36.264680,"Latitude":33.00000000,"Type":"","Capital":"Kabul"},{"Currency":"AFN","CityFlag":1,"CityId":54,"CountryId":1,"Longitude":65.00000000,"CityLongitude":68.708290,"CurrencyName":"Afghan afghani","State":"Baghlan","Flag":1,"ISO3":"AFG","ISO2":"AF","RowNumber":5,"StateFlag":1,"City":"Baghl?n","Country":"Afghanistan","StateId":3875,"CityLatitude":36.130680,"Latitude":33.00000000,"Type":"","Capital":"Kabul"}]}

For more information regarding Kendo virtual grids, please see Implementing a Kendo Virtualized Grid with ColdFusion - Part 2, Server-Side Logic

Further Reading

Updated 6/14/2023