Optimized vs Unoptimized JSON Return in CFML

I was writing a test script for returning some user-data to a UI client. The client expected a return in two formats in different places: 1. A jqGrid to display a datagrid of users and 2. for a config object that expected data in name value pairs.

Using cfml as the middle tier makes life so easy. I banged out a simple cfml script.

SysUsers.tst.cfm file:

   1: <cfsetting enablecfoutputonly="true" showdebugoutput="false" />

   2: <cfparam name="url.page" default="1" />

   3: <cfparam name="url.optimized" default="false" />

   4:  

   5: <cfparam name="form.username" default="" />

   6: <cfparam name="form.password" default="" />

   7:  

   8:  

   9: <!-- Fixtures Query --->

  10: <cfset QryData = QueryNew("user_id, username, first_name, last_name, password, created_date, updated_date, is_active, role") />

  11: <cfset QueryAddRow(QryData,5) />

  12:  

  13: <cfset operator_list = "usr1, usr2, usr3, usr4, usr5" />

  14: <cfset passwd_list = "usr1, usr2, usr3, usr4, usr5" />

  15: <cfset role_list = "admin, normal, perm1, perm1, normal" />

  16:  

  17: <cfloop from="1" to="5" index="i">

  18:     <cfset QuerySetCell(QryData,"user_id",i*4,i)>

  19:     <cfset QuerySetCell(QryData,"username",listGetAt(operator_list, i),i)>

  20:     <cfset QuerySetCell(QryData,"first_name","Optr-#i#",i)>

  21:     <cfset QuerySetCell(QryData,"last_name", "operator#i#",i)>

  22:     <cfset QuerySetCell(QryData,"password",listGetAt(passwd_list, i),i)>

  23:     <cfset QuerySetCell(QryData,"created_date",dateFormat(now(), 'mm/dd/yyyy'),i)>

  24:     <cfset QuerySetCell(QryData,"updated_date",dateFormat('#randRange(1,12)#/#randRange(1,30)#/#randRange(2001,2010)#', 'mm/dd/yyyy'),i) />

  25:     <cfset QuerySetCell(QryData,"is_active",randRange(0,1),i)>

  26:     <cfset QuerySetCell(QryData,"role",listGetAt(role_list,i),i)>

  27: </cfloop>

  28:  

  29:  

  30: <!--- StructDef --->

  31: <cfset DataStr = StructNew() />

  32: <cfset DataStr["total"] = 1 /><!--- total pages --->

  33: <cfset DataStr["page"] = url.page /><!--- current page --->

  34: <cfset DataStr["records"] = QryData.recordCount /><!--- total records --->

  35: <cfset DataStr["columns"] = deserializeJSON(serializeJSON(QryData))['columns'] />

  36:  

  37: <cfif NOT url.optimized>

  38:     <!--- Unoptimized return for small datasets for use in smaller structs to save JS programming --->

  39:     <cfset DataStr["rows"] = arrayNew(1) />

  40:     <cfset array_idx = 1 />

  41:     <cfloop query="QryData">

  42:         <cfloop from="1" to="#listLen(columnList)#" index="i">

  43:             <cfset DataStr["rows"][array_idx][listgetAt(columnList,i)] = QryData[listgetAt(columnList,i)][array_idx] />

  44:         </cfloop>

  45:         <cfset array_idx = array_idx + 1 />

  46:     </cfloop>

  47: <cfelse>

  48:     <!--- optimized return for large datasets for used in grids --->

  49:     <cfset DataStr = StructNew() />

  50:     <cfset DataStr["total"] = 1 /><!--- total pages --->

  51:     <cfset DataStr["page"] = url.page /><!--- current page --->

  52:     <cfset DataStr["records"] = QryData.recordCount /><!--- total records --->

  53:     <cfset DataStr["rows"] = deserializeJSON(serializeJSON(QryData))['data'] />

  54:     <cfset DataStr["columns"] = deserializeJSON(serializeJSON(QryData))['columns'] />

  55: </cfif>

  56:  

  57: <!--- Output JSON --->

  58: <cfoutput>#serializeJSON(DataStr)#</cfoutput>

When calling this script just pass in the url parameter value for the variable “optimized” (Line 3 above).

Passing in a value of true will yield (array of arrays in “rows”):

   1: {

   2:     "records": 5,

   3:     "columns": [

   4:         "USER_ID",

   5:         "USERNAME",

   6:         "FIRST_NAME",

   7:         "LAST_NAME",

   8:         "PASSWORD",

   9:         "CREATED_DATE",

  10:         "UPDATED_DATE",

  11:         "IS_ACTIVE",

  12:         "ROLE"

  13:     ],

  14:     "total": 1,

  15:     "rows": [

  16:         [

  17:             4,

  18:             "usr1",

  19:             "Optr-1",

  20:             "operator1",

  21:             "usr1",

  22:             "10\/12\/2010",

  23:             "01\/05\/2005",

  24:             1,

  25:             "admin"

  26:         ],

  27:         [

  28:             8,

  29:             " usr2",

  30:             "Optr-2",

  31:             "operator2",

  32:             " usr2",

  33:             "10\/12\/2010",

  34:             "04\/21\/2007",

  35:             1,

  36:             " normal"

  37:         ],

  38:         [

  39:             12,

  40:             " usr3",

  41:             "Optr-3",

  42:             "operator3",

  43:             " usr3",

  44:             "10\/12\/2010",

  45:             "10\/18\/2009",

  46:             1,

  47:             " perm1"

  48:         ],

  49:         [

  50:             16,

  51:             " usr4",

  52:             "Optr-4",

  53:             "operator4",

  54:             " usr4",

  55:             "10\/12\/2010",

  56:             "08\/06\/2001",

  57:             1,

  58:             " perm1"

  59:         ],

  60:         [

  61:             20,

  62:             " usr5",

  63:             "Optr-5",

  64:             "operator5",

  65:             " usr5",

  66:             "10\/12\/2010",

  67:             "09\/18\/2005",

  68:             0,

  69:             " normal"

  70:         ]

  71:     ],

  72:     "page": "1"

  73: }

…while a value of false will yield (array of objects in “rows”):

Hope it will help someone (or me in the future…I tend to forget :) )