Formatting JSON Data with ColdFusion for HighCharts
So I'm working on a pie chart using HighSlide and ColdFusion.
To make it simple, it expects a line like this for data:
data: [{name: 'Jane',y: 13}, {name: 'John',y: 23}, {name: 'Joe',y: 19}]
What I did to accomplish this was to view the query results and create a line like this:
<cfloop query="getAreaCounts">
<cfset areaList = listAppend(areaList, "{name: '#name#',y: #y#}")>
</cfloop>
I know I should be simpler / smarter? Since this is JSON data, I figured I could just do this:
<cfoutput>#SerializeJSON(getAreaCounts)#</cfoutput>
But this returns a JSON string like this, which won't be processed to high standards:
{"COLUMNS":["Y","NAME"],"DATA":[[8,"Area1"],[7,"Area2"],[1,"Area3"],[1,"Area4"]]}
Any help pointing me in the right direction would be great. Do I need to dig deeper into the JSON howto?
source to share
You will need to convert the query to an array of structures and then run serializeJSON()
on that array.
Below is a method that I often use when dealing with a lot of requests and JSON. I think I got it from Ben Nadel site and then converted it to cfscript. I'll try to track down the blog post after posting this answer.
public array function queryToArray( required query qry ) {
var columns = arguments.qry.getColumnNames();
var ofTheJedi = [];
for( var i = 1; i LTE qry.recordCount; i++ ) {
var obj = {};
for( var k = 1; k LTE arrayLen( columns ); k++ ) {
structInsert( obj, columns[ k ], arguments.qry[ columns[ k ] ][ i ] );
}
arrayAppend( ofTheJedi, obj );
}
return ofTheJedi;
}
So, in your case, you would do something like this:
<cfset myJSON = queryToArray( getAreaCounts ) />
<cfoutput>#serializeJSON( myJSON )#</cfoutput>
EDIT: Here's Ben's blog post that inspired the method above: http://www.bennadel.com/blog/124-Ask-Ben-Converting-a-Query-to-an-Array.htm
source to share
You need something like this (untested, but you get the idea)
<cfscript>
data = [{name='Jane',y=13],{name='John',y=23},{name='Joe',y=19}];
</cfscript>
<cfoutput>#serializeJson(data)#</cfoutput>
You need to create an array of Coldfusion strucutres (each with "name" and "y" as members), then serialize it. What you are serializing above is the request object. From your code it looks like you wanted to serialize the "arealist" variable, but this var probably won't come out correctly, because it's not an array of structures - it's a list of strings.
source to share