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 a ColdFusion ORM Array

This component also works when using ColdFusion ORM. When you query a database using ColdFusion ORM using the map keyword in the query, the object returned is typically an object in an array of structures. 

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"
   }
]

I will cover the other less common use cases in future blog posts as needed when I discuss the Kendo UI widgets.

Further Reading