Twitter: raymondcamden


Address: Lafayette, LA, USA

Quick and Dirty JSON/Query Example

09-20-2007 31,175 views JavaScript, ColdFusion

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 plain print about
1<cfquery name="getstuff" datasource="blogdev" maxrows="5">
2select    id, title, posted
3from    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 plain print about
1<script>
2mydata = 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 plain print about
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 plain print about
1function 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 plain print about
1mydata.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 plain print about
1<cfquery name="getstuff" datasource="blogdev" maxrows="5">
2select    id, title, posted
3from    tblblogentries
4</cfquery>
5
6<cfset jsondata = serializeJSON(getstuff)>
7
8<script>
9mydata = eval(<cfoutput>#jsondata#</cfoutput>)
10
11function 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" />