Formatting ColdFusion JSON from SQL query for Google Charts

Question

Full disclosure – I am very new to any kind of web coding, so TIA for your patience!

What I am trying to do is query for data and use it to build dynamic Google charts. In Coldfusion I’ve queried SQL Server for data, then output as JSON (I validated this JSON on http://jsonlint.com/ and it says it is valid). If I try to directly use this JSON in a Google chart I get the error: “Uncaught Error: Invalid JSON string” because the format of the JSON is not what Google charts wants.

Here is a snippet of the JSON that ColdFusion creates from the SQL query:

{"COLUMNS":["DATE","TEMP_C"],"DATA":[["08\/09\/2016",27.04],["08\/09\/2016",26.98],["08\/09\/2016",27.02],      …etc

Here’s the format that Google charts is expecting:

{ 
"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}]} 
   ] 
}

I’ve been trying to restructure the JSON to the format needed by Google charts and I can’t figure it out. I’ve been trying to use the info in this question: Is there a way to easily convert cfquery output to DataTable JSON format in coldfusion?

So the section I have sortof working is this:

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

<cfset chartsRow = structNew()>
   <cfset chartsRow["id"] = ""> 
   <cfset chartsRow["label"] = "Date">
   <cfset chartsRow["pattern"] = "">
   <cfset chartsRow["type"] = "date">

   <cfset chartsRow2["id"] = ""> 
   <cfset chartsRow2["label"] = "Temp_C">
   <cfset chartsRow2["pattern"] = "">
   <cfset chartsRow2["type"] = "number">  

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

   <cfset chartsDataJSON = serializeJSON(chartsData)>

  <cfset chartsData2 = structNew()>
  <cfset chartsData2["rows"] = arrayNew(1)>

  <!--- use a query loop to copy query data to this struct --->
   <cfloop query="qAEBdata">
   <cfset chartsRow3 = structNew()>
   <cfset chartsRow3["c"] = []>
   <cfset chartsRow3["v"] = "#Date#">
   <cfset chartsRow3["f"] = "null">
   <cfset chartsRow3["type"] = "date">

   <cfset arrayAppend(chartsData2["rows"], chartsRow3)>
   <cfset chartsDataJSON2 = serializeJSON(chartsData2)>
</cfloop>

   <cfoutput>#chartsDataJSON#,</cfoutput>
   <cfoutput>#chartsDataJSON2#</cfoutput>

Which results in this output:

{
"cols":[
{"pattern":"","label":"Date","id":"","type":"date"},
{"pattern":"","label":"Temp_C","id":"","type":"number"}
]
}, 
{    <!---note that google charts does not want this structure --->
"rows":[
{"f":"null","v":"08\/11\/2016","c":[],"type":"date"},
{"f":"null","v":"08\/11\/2016","c":[],"type":"date"},
{"f":"null","v":"08\/11\/2016","c":[],"type":"date"}   …etc

My questions/issues are: (1) Why is it reordering the ‘id’, ‘label’, ‘pattern’, ‘type’ and the ‘c’,’v’,’f’,’type’? How do I correct this? and (2) Clearly I’m not structuring the format correctly, esp. for the rows, and I can’t figure out how to correct it. Can anyone please help nudge me in the right direction?

UPDATE: I updated my code with the help of @WhiteHat and @Leigh, as such:

<cfquery name="qAEBdata" datasource="mydb">
SELECT convert(nvarchar, date, 101) AS Date, temp AS Temp_C
FROM mytable
WHERE threeletter = 'AEB'
and date > '8/11/2016' 
</cfquery> 

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

   <cfset chartsRow = structNew()>
   <cfset chartsRow["id"] = ""> 
   <cfset chartsRow["label"] = "Date">
   <cfset chartsRow["pattern"] = "">
   <cfset chartsRow["type"] = "string">

   <cfset chartsRow2["id"] = ""> 
   <cfset chartsRow2["label"] = "Temp_C">
   <cfset chartsRow2["pattern"] = "">
   <cfset chartsRow2["type"] = "number">

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

<cfloop query="qAEBdata">
  <cfset chartsRow3 = structNew()>
  <cfset chartsRow3["c"] = []>
  <cfset chartsRow3Value0 = structNew()>
  <cfset chartsRow3Value0["v"] = "#Date#">
  <cfset chartsRow3Value1 = structNew()>
  <cfset chartsRow3Value1["v"] = "#Temp_C#">
  <cfset arrayAppend(chartsRow3["c"], chartsRow3Value0)>
  <cfset arrayAppend(chartsRow3["c"], chartsRow3Value1)>
  <cfset arrayAppend(chartsData["rows"], chartsRow3)>
</cfloop>

<cfset chartsDataJSON = serializeJSON(chartsData)>
<cfoutput>#chartsDataJSON#,</cfoutput>

This puts the data into the correct JSON structure for Google charts EXCEPT my output has a a trailing comma at the very end (it's also putting rows before columns, but that doesn't seem to matter). Here's my output:

{"rows":[
{"c":[{"v":"08\/12\/2016"},{"v":26.93}]},
{"c":[{"v":"08\/12\/2016"},{"v":26.94}]},
"cols":[
{"pattern":"","label":"Date","id":"","type":"string"},
{"pattern":"","label":"Temp_C","id":"","type":"number"}]},

I've tried adding each these methods (one at a time) after the 'serializeJSON' and they don't remove the comma. Increasing the "-1" to a higher number results in removing the brackets and eventually text from the data:

<cfset chartsDataJSON = reReplace(chartsDataJSON, ",$", "", "all")>
<cfset chartsDataJSON = chartsDataJSON.substring(0, len(chartsDataJSON)- 1)>
<cfset chartsDataJSON = left(chartsDataJSON, len(chartsDataJSON)-1)>

E.g. using this:

 <cfset chartsDataJSON = left(chartsDataJSON, len(chartsDataJSON)-2)>

Results in the end of the output looking like the following (note: result is a "]" bracket is missing from before the comma but comma is still there):

"cols":[{"pattern":"","label":"Date","id":"","type":"string"},{"pattern":"","label":"Temp_C","id":"","type":"number"}, 

SECOND UPDATE I found where my stupid comma was and removed it, it was after where I'd output it, which makes sense why I couldn't remove it where I tried to:

<cfoutput>#chartsDataJSON#,</cfoutput>

I am trying to pull the JSON data into the Google chart and I'm getting "invalid JSON string":

<!---build chart--->  
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
google.charts.load('current', {
  callback: function () {
    var fusionObject = 'chartDataJSON';

    var chartsDataJSON = new google.visualization.DataTable(fusionObject);

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(chartsDataJSON);
  },
  packages: ['corechart']
});
  </script>

But if I paste in the JSON directly the chart draws just fine.

FINAL UPDATE - JSON formatted correctly and chart draws! As Leigh pointed out, I needed to call the variable correctly for ColdFusion (a duh moment for me). Corrected code below:

 <!---build chart--->  
<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
google.charts.load('current', {
  callback: function () {
    var fusionObject = ('<cfoutput>#chartsDataJSON#</cfoutput>');

    var chartsDataJSON = new google.visualization.DataTable(fusionObject);

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(chartsDataJSON);
  },
  packages: ['corechart']
});
  </script>

</head>
<body>
  <div id="chart_div"></div>
</body>

Answer 1

"rows" needs to be added to the same object as "cols"

each "c" also needed some changes

using the following snippet...

<!--- simulate data query --->
<cfset qAEBdata = queryNew("")>
<cfset queryAddColumn(qAEBdata, "Date", "varchar", ["08/09/2016","08/10/2016","08/11/2016"])>
<cfset queryAddColumn(qAEBdata, "Temp_C", "decimal", [27.04,26.98,27.02])>

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

<cfset chartsRow = structNew()>
<cfset chartsRow["id"] = "">
<cfset chartsRow["label"] = "Date">
<cfset chartsRow["pattern"] = "">
<cfset chartsRow["type"] = "string">

<cfset chartsRow2["id"] = "">
<cfset chartsRow2["label"] = "Temp_C">
<cfset chartsRow2["pattern"] = "">
<cfset chartsRow2["type"] = "number">

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

<cfloop query="qAEBdata">
  <cfset chartsRow3 = structNew()>
  <cfset chartsRow3["c"] = []>
  <cfset chartsRow3Value0 = structNew()>
  <cfset chartsRow3Value0["v"] = "#Date#">
  <cfset chartsRow3Value1 = structNew()>
  <cfset chartsRow3Value1["v"] = "#Temp_C#">
  <cfset arrayAppend(chartsRow3["c"], chartsRow3Value0)>
  <cfset arrayAppend(chartsRow3["c"], chartsRow3Value1)>
  <cfset arrayAppend(chartsData["rows"], chartsRow3)>
</cfloop>

<cfset chartsDataJSON = serializeJSON(chartsData)>
<cfoutput>#chartsDataJSON#</cfoutput>

at the try cold fusion gist runner provided by @Leigh

i was able to create the following JSON

{
  "cols": [{
    "pattern": "",
    "label": "Date",
    "id": "",
    "type": "string"
  }, {
    "pattern": "",
    "label": "Temp_C",
    "id": "",
    "type": "number"
  }],
  "rows": [{
    "c": [{
      "v": "08/09/2016"
    }, {
      "v": 27.04
    }]
  }, {
    "c": [{
      "v": "08/10/2016"
    }, {
      "v": 26.98
    }]
  }, {
    "c": [{
      "v": "08/11/2016"
    }, {
      "v": 27.02
    }]
  }]
}

which produces the following chart...
(run following snippet)

google.charts.load('current', {
  callback: function () {
    var fusionObject = {"cols":[{"pattern":"","label":"Date","id":"","type":"string"},{"pattern":"","label":"Temp_C","id":"","type":"number"}],"rows":[{"c":[{"v":"08/09/2016"},{"v":27.04}]},{"c":[{"v":"08/10/2016"},{"v":26.98}]},{"c":[{"v":"08/11/2016"},{"v":27.02}]}]};

    var chartsDataJSON = new google.visualization.DataTable(fusionObject);

    var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
    chart.draw(chartsDataJSON);
  },
  packages: ['corechart']
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

BUT, had to change the first column to type: 'string'
not sure how to get an actual date to JSON from ColdFusion