Is there a way to easily convert cfquery output to DataTable JSON format in coldfusion?

Question

I am trying to create a chart using the Google Charts API, but I cannot get the data in the required format.

serializeJSON(data)in coldfusion gives me JSON data, but it looks like this is not what the diagrams API expects. Is there any component that I can use to convert it to the required format?

Query

<cfquery name="getAllUsers" datasource="cccac_swipe" result="UserDetailsResult">
Select login_mode,count(login_mode) as total from login_activity,ccac_registered_users where login_activity.student_id=ccac_registered_users.student_id GROUP BY login_mode
</cfquery>

ColdFusion JSON

{"COLUMNS":["LOGIN_MODE","TOTAL"],"DATA":[["manual",123],["swipe",20]]}

Expected JSON

{ "cols": [ {"id":"","label":"SignIn Method","pattern":"","type":"string"}, {"id":"","label":"Count","pattern":"","type":"number"} ], "rows": [ {"c":[{"v":"manual","f":null},{"v":123,"f":null}]}, {"c":[{"v":"swipe","f":null},{"v":20,"f":null}]} ] }

Answer 1

This is basically what you need. Just plug in the request data.

<cfset chartsData = structNew()>
<cfset chartsData["cols"] = arrayNew(1)>

<!--- use a query loop to copy query data to this struct --->
<cfloop query="UserDetailsResult">
   <cfset chartsRow = structNew()>
   <cfset chartsRow["id"] = ""> 
   <cfset chartsRow["label"] = "SignIn Method">
   <cfset chartsRow["pattern"] = "">
   <cfset chartsRow["type"] = "string">

   <cfset arrayAppend(chartsData["cols"], chartsRow)>
</cfloop>

<cfset chartsDataJSON = serializeJSON(chartsData)>