Yesterday in the ColdFusion chat room someone asked a question (a technical question at that - guess how rare that is?) about how they could use a JSON-encoded query. I whipped up a quick example that I thought others might like as well. This example does not use Ajax to load the JSON data - which is also pretty rare - but I wanted something that I could run all in one file. So here we go... The first thing I did was get a query of data and serialize it using SerializeJSON:
   view plainprintabout
1  <cfquery name="getstuff" datasource="blogdev" maxrows="5">
2  select    id, title, posted
3  from    tblblogentries
4  </cfquery>
5  
6  <cfset jsondata = serializeJSON(getstuff)>
At this point, jsondata is a string. Here is what it looked like:
{"COLUMNS":["ID","TITLE","POSTED"],"DATA":[["905D9689-0130-1A16-62272F586A771C0C","mmm","May, 15 2007 10:31:00"],["905DAAED-E300-3B9D-7E25E43985CA9507","nn","May, 15 2007 10:31:00"],["905DE931-B8DB-B1AD-F77FC505851C2E9A","j","May, 15 2007 10:31:00"],["905E0C6A-99B8-97EC-4862D9064B9EC659","mmmmm","May, 15 2007 10:31:00"],["905E5910-0E2D-C566-5DCD39E6FD48ED06","NUMBER 11","May, 15 2007 10:32:00"]]}
I wanted to work with this on the client side (the whole point of this entry), so I needed to set this data to a JavaScript variable. The cool thing about JSON is that it can be evaled (think of this like ColdFusion's evaluate function) directly to a variable, so I used this code to assign it:
   view plainprintabout
1  <script>
2  mydata = eval(<cfoutput>#jsondata#</cfoutput>)
For my demo, I built a quick form with a button. The idea is that you would hit the button, and I'd then run code that would loop over the query and display the contents. Here is the form I used and the 'area' I would use for output:
   view plainprintabout
1  <input type="button" value="Show Data" onClick="showData()">
2  <div id="content" />
Ok, nothing complex yet. Now let's take a look at showData():
   view plainprintabout
1  function showData() {
2      var output = document.getElementById('content');
3      var colMap = new Object();
4      
5      //first - find my columns
6      for(var i = 0; i < mydata.COLUMNS.length; i++) {
7          colMap[mydata.COLUMNS[i]] = i;        
8      }
9      
10      for(var i = 0; i < mydata.DATA.length; i++) {
11          var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
12          output.innerHTML += str;
13      }
14  }
The first line of code simply creates a pointer to my div. I'll be writing out to that later. Now I need to loop over my query. The question is - how? If you look at the JSON string I output earlier, you will see that it is an object with two main properties - columns and data. The columns property is a list of columns. The data property contains my rows of data. Note though that it isn't indexed by column names. Instead - the first item in the first row of data matches the first column name. So what I need to do is figure out what my columns are. To do this I create a column map - i.e., a mapping of columns to indexes. The first FOR loop does this. Once I have that - then it is a trivial matter to loop over my rows of data and pick the values I need. So for example, to get the title for row i, I use:
   view plainprintabout
1  mydata.DATA[i][colMap["TITLE"]]
Make sense? I'll include the full source of the code below, but before I do, a few notes:
  1. The SerializeJSON function takes an optional second argument that is only used for queries. If set to true, the structure is pretty different than what I described above. I'll blog about that later today. (Someone will probably need to remind me.)
  2. While SerializeJSON is new to ColdFusion 8, ColdFusion 7 introduced the toScript function, which is another way to go from a ColdFusion variable to a JavaScript variable. (It is actually simpler than what I used above.)
Now for the complete template:
   view plainprintabout
1  <cfquery name="getstuff" datasource="blogdev" maxrows="5">
2  select    id, title, posted
3  from    tblblogentries
4  </cfquery>
5  
6  <cfset jsondata = serializeJSON(getstuff)>
7  
8  <script>
9  mydata = eval(<cfoutput>#jsondata#</cfoutput>)
10  
11  function showData() {
12      var output = document.getElementById('content');
13      var colMap = new Object();
14      
15      //first - find my columnsco
16      for(var i = 0; i < mydata.COLUMNS.length; i++) {
17          colMap[mydata.COLUMNS[i]] = i;        
18      }
19      
20      for(var i = 0; i < mydata.DATA.length; i++) {
21          var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
22          output.innerHTML += str;
23      }
24  }
25  </script>
26  
27  <input type="button" value="Show Data" onClick="showData()">
28  <div id="content" />