All geek questions in one place

Format ColdFusion JSON from SQL Query for Google Charts

Full disclosure - I'm very new to any form of web coding, so TIA for your patience!

I am trying to query the data and use it to create dynamic google charts. In Coldfusion, Ive queried SQL Server for data, then output it as JSON (I confirmed this JSON at http://jsonlint.com/ and it says it is valid). If I try to directly use this JSON in a Google chart, I get an error: "Uncaught Error: Invalid JSON string" because the JSON format does not meet Google requirements.

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

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

Here is the format Google graphics expect:

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

JSON , Google, . : cfquery JTON DataTable coldfusion?

, , , :

<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>

:

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

/: (1) "id", "" "c, v, f"? ? (2) , , . , , . - ?

UPDATE: @WhiteHat @Leigh, :

<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>

JSON Google. EXCEPT. ( , , , ). :

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

( ) "serializeJSON", . "-1" , , :

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

. :

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

(: - "]" , ):

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

, , , , , , , :

<cfoutput>#chartsDataJSON#,</cfoutput>

JSON Google, " JSON":

<!---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>

JSON , .

- JSON ! , ColdFusion ( ). :

 <!---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>
+4
1

"rows" , "cols"

"c"

...

<!--- 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>

-, @Leigh

i 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
    }]
  }]
}

...
( )

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>
Hide result

, type: 'string'
, JSON ColdFusion

+1

Source: https://habr.com/ru/post/1650961/

More articles:


All Articles