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:

view plain print about
1<cffunction name="books" access="remote" output="false" returntype="string">
2    <cfargument name="term" type="string" required="true" />
3    <cfset var rs = {} />
4    <cfquery name="rs.q" datasource="cfbookclub">
5        SELECT DISTINCT
6            bookid,
7            title,
8            genre
9        FROM
10            books
11        WHERE
12            title LIKE <cfqueryparam value="%#arguments.term#%" cfsqltype="cf_sql_varchar" />
13        ORDER BY
14            genre, title
15    </cfquery>
16    <cfreturn serializeJSON( rs.q ) />
17</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:

view plain print about
1<cfdump var="#books('Man')# />

My JSON will look like this:

view plain print about
1{
2    "COLUMNS":["BOOKID","TITLE","GENRE"],
3    "DATA":[
4        [8,"Apparition Man","Fiction"],
5        [2,"Shopping Mart Mania","Non-fiction"]
6    ]
7}

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

view plain print about
1{
2    "ROWCOUNT":2,
3    "COLUMNS":["BOOKID","TITLE","GENRE"],
4    "DATA":{
5        "BOOKID":[8,2],
6        "TITLE":["Apparition Man","Shopping Mart Mania"],
7        "GENRE":["Fiction","Non-fiction"]
8    }
9}

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.

view plain print about
1{    
2    "data":[
3        {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
4        {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
5    ]
6}

Client-side Conversion

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

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

Server-side Conversion

The day after Ray put up his first Handlebars demo, I got to work using Handlebars in an internal application I'm building. Handlebars is a drop-dead simple JavaScript template engine which I'll talk more about it later.

A few days ago, Julian Halliwell put up a post on this same approach, but his returns both the original CF JSON and the converted JSON.

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

ArrayCollection.cfc

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

view plain print about
1<cfcomponent>
2
3    <cffunction name="init" access="public" output="false" returntype="ArrayCollection">
4        <cfset setContentType("json") />
5        <cfset setDataHandle(true) />
6        <cfset setDataHandleName("data") />
7        <cfreturn this />
8    </cffunction>
9
10    <cffunction name="$renderdata" access="public" output="false" returntype="string" hint="convert a query to an array of structs">
11        <cfset var rs = {} />
12        <cfset var rs.q = variables.data />
13        <cfset rs.results = [] />
14        <cfset rs.columnList = lcase(listSort(rs.q.columnlist, "text" )) />
15        <cfloop query="rs.q">
16            <cfset rs.temp = {} />
17            <cfloop list="#rs.columnList#" index="rs.col">
18                <cfset rs.temp[rs.col] = rs.q[rs.col][rs.q.currentrow] />
19            </cfloop>
20            <cfset arrayAppend( rs.results, rs.temp ) />
21        </cfloop>
22        <cfset rs.data = {} />
23        <cfif hasDataHandle()>
24            <cfset rs.data[getDataHandleName()] = rs.results />
25        <cfelse>
26            <cfset rs.data = rs.results />
27        </cfif>
28        <cfreturn serializeJSON(rs.data) />
29    </cffunction>
30
31    <cffunction name="setData" access="public" output="false" returntype="void">
32        <cfargument name="data" type="query" required="true">
33        <cfset variables.data = arguments.data />
34    </cffunction>
35
36    <cffunction name="setContentType" access="private" output="false" returntype="void">
37        <cfargument name="contenttype" type="string" required="true" />
38        <cfset variables.contentType = arguments.contentType />
39    </cffunction>
40    <cffunction name="getContentType" access="public" output="false" returntype="string">
41        <cfreturn variables.contentType />
42    </cffunction>
43
44    <cffunction name="setDataHandle" access="public" output="false" returntype="void">
45        <cfargument name="datahandle" type="boolean" required="true" />
46        <cfset variables.dataHandle = arguments.datahandle />
47
48    </cffunction>
49    <cffunction name="hasDataHandle" access="public" output="false" returntype="boolean">
50        <cfreturn variables.dataHandle />
51    </cffunction>
52
53    <cffunction name="setDataHandleName" access="public" output="false" returntype="void">
54        <cfargument name="dataHandleName" type="string" required="true" />
55        <cfset variables.dataHandleName = arguments.dataHandleName />
56    </cffunction>
57    <cffunction name="getDataHandleName" access="public" output="false" returntype="string">
58        <cfreturn variables.dataHandleName />
59    </cffunction>
60
61</cfcomponent>

Converting a Query

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

view plain print about
1<cfset rs.ac = createObject("component", "ArrayCollection").init() />
2<cfset rs.ac.setData( rs.q ) />
3<cfreturn rs.ac.$renderdata() />

You've already seen the results:

view plain print about
1{    
2    "data":[
3        {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
4        {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
5    ]
6}

So let's change the data handle:

view plain print about
1<cfset rs.ac = createObject("component", "ArrayCollection").init() />
2<cfset rs.ac.setData( rs.q ) />
3<cfset rs.ac.setDataHandleName( "foo" ) />
4<cfreturn rs.ac.$renderdata() />

which returns

view plain print about
1{    
2    "foo":[
3        {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
4        {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
5    ]
6}

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

view plain print about
1<cfset rs.ac = createObject("component", "ArrayCollection").init() />
2<cfset rs.ac.setData( rs.q ) />
3<cfset rs.ac.setDataHandle(false) />
4<cfreturn rs.ac.$renderdata() />

will give us just the array

view plain print about
1[
2    {"bookid":8,"genre":"Fiction","title":"Apparition Man"},
3    {"bookid":2,"genre":"Non-fiction","title":"Shopping Mart Mania"}
4]

Teaser

I'll post this CFC to github later on, but soon I'll post an example of using this with Handlebars AND I'll tell you why I chose the name $renderdata() instead of renderdata().