ArrayCollection.cfc, a custom JSON renderer for ColdFusion queries

Posted on May 11, 2012

There’s been a few recent blogs posts covering how to convert the JSON returned by ColdFusion’s serializeJSON function to a more standard pattern in order to use data with various grids or JavaScript templates. Those posts covered client-side conversions, here’s how to generate the JSON on the server.

ColdFusion and JSON

Let’s use this function, with a simple query:

<cffunction name="books" access="remote" output="false" returntype="string">
  <cfargument name="term" type="string" required="true" />
  <cfset var rs = {} />
  <cfquery name="rs.q" datasource="cfbookclub">
    SELECT DISTINCT
      bookid,
      title,
      genre
    FROM
      books
    WHERE
      title LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar" />
    ORDER BY
      genre, title
  </cfquery>
  <cfreturn serializeJSON( rs.q ) />
</cffunction>

This is using the (case sensitive) Embedded Apache Derby database “cfbookclub” that comes with the base install of ColdFusion. If I make this call:

<cfdump var="#books('Man')# />

My JSON will look like this:

{
  "COLUMNS":["BOOKID","TITLE","GENRE"],
  "DATA":[
    [8,"Apparition Man","Fiction"],
    [2,"Shopping Mart Mania","Non-fiction"]
  ]
}

If I change the function’s return to <cfreturn serializeJSON( rs.q, true )>:

{
  "ROWCOUNT":2,
  "COLUMNS":["BOOKID","TITLE","GENRE"],
  "DATA":{
    "BOOKID":[8,2],
    "TITLE":["Apparition Man","Shopping Mart Mania"],
    "GENRE":["Fiction","Non-fiction"]
  }
}

But what we really want is an array of structs. Each struct is an object representation of a row in the query. This is what I’ve known from Flex development as an ArrayCollection.

{
  "data":[
    {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
    {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
  ]
}

Client-side Conversion

Here’s a few blog posts that cover how to convert ColdFusion’s standard JSON to this more standard JSON format:

Steve Cutter created the jQuery plugin serializeCFJSON
Ray Camden shows the function cfQueryNormalize to do the same without needing jQuery.

But we want to serve up our data is this JSON format directly from the server.

ArrayCollection.cfc

The ArrayCollection.cfc only returns the converted JSON and allows you to convert any query on the fly.

Here’s the CFC, which should always be created as a singleton. Props to Ben Nadel for giving me a place to start.

<cfcomponent>

  <cffunction name="init" access="public" output="false" returntype="ArrayCollection">
    <cfset setContentType("json") />
    <cfset setDataHandle(true) />
    <cfset setDataHandleName("data") />
    <cfreturn this />
  </cffunction>

  <cffunction name="$renderdata" access="public" output="false" returntype="string" hint="convert a query to an array of structs">
    <cfset var rs = {} />
    <cfset var rs.q = variables.data />
    <cfset rs.results = [] />
    <cfset rs.columnList = lcase(listSort(rs.q.columnlist, "text" )) />
    <cfloop query="rs.q">
      <cfset rs.temp = {} />
      <cfloop list="#rs.columnList#" index="rs.col">
        <cfset rs.temp[rs.col] = rs.q[rs.col][rs.q.currentrow] />
      </cfloop>
      <cfset arrayAppend( rs.results, rs.temp ) />
    </cfloop>
    <cfset rs.data = {} />
    <cfif hasDataHandle()>
      <cfset rs.data[getDataHandleName()] = rs.results />
    <cfelse>
      <cfset rs.data = rs.results />
    </cfif>
    <cfreturn serializeJSON(rs.data) />
  </cffunction>

  <cffunction name="setData" access="public" output="false" returntype="void">
    <cfargument name="data" type="query" required="true">
    <cfset variables.data = arguments.data />
  </cffunction>

  <cffunction name="setContentType" access="private" output="false" returntype="void">
    <cfargument name="contenttype" type="string" required="true" />
    <cfset variables.contentType = arguments.contentType />
  </cffunction>
  <cffunction name="getContentType" access="public" output="false" returntype="string">
    <cfreturn variables.contentType />
  </cffunction>

  <cffunction name="setDataHandle" access="public" output="false" returntype="void">
    <cfargument name="datahandle" type="boolean" required="true" />
    <cfset variables.dataHandle = arguments.datahandle />

  </cffunction>
  <cffunction name="hasDataHandle" access="public" output="false" returntype="boolean">
    <cfreturn variables.dataHandle />
  </cffunction>

  <cffunction name="setDataHandleName" access="public" output="false" returntype="void">
    <cfargument name="dataHandleName" type="string" required="true" />
    <cfset variables.dataHandleName = arguments.dataHandleName />
  </cffunction>
  <cffunction name="getDataHandleName" access="public" output="false" returntype="string">
    <cfreturn variables.dataHandleName />
  </cffunction>

</cfcomponent>

Converting a Query

Let’s go back to my original function and change it to return an ArrayCollection.

<cfset rs.ac = createObject("component", "ArrayCollection").init() />
<cfset rs.ac.setData( rs.q ) />
<cfreturn rs.ac.$renderdata() />

You’ve already seen the results:

{
  "data":[
    {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
    {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
  ]
}

So let’s change the data handle:

<cfset rs.ac = createObject("component", "ArrayCollection").init() />
<cfset rs.ac.setData( rs.q ) />
<cfset rs.ac.setDataHandleName( "foo" ) />
<cfreturn rs.ac.$renderdata() />

which returns

{
  "foo":[
    {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
    {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
  ]
}

And if we just want the array without a data handle:

<cfset rs.ac = createObject("component", "ArrayCollection").init() />
<cfset rs.ac.setData( rs.q ) />
<cfset rs.ac.setDataHandle(false) />
<cfreturn rs.ac.$renderdata() />

will give us just the array

[
  {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
  {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
]

Repo

The latest version of the ArrayCollection is part of a collection of CFQuery to JSON conversion objects. Let me know if you have any questions.

About the Author

Adrian J. Moreno

Adrian is a CTO and solution architect specializing in software modernization. More information