Quick and Dirty JSON/Query Example

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:
<cfquery name="getstuff" datasource="blogdev" maxrows="5">
select	id, title, posted
from	tblblogentries
</cfquery>

<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:
<script>
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:
<input type="button" value="Show Data" onClick="showData()">
<div id="content" />
Ok, nothing complex yet. Now let's take a look at showData():
function showData() {
	var output = document.getElementById('content');
	var colMap = new Object();
	
	//first - find my columns
	for(var i = 0; i < mydata.COLUMNS.length; i++) {
		colMap[mydata.COLUMNS[i]] = i;		
	}
	
	for(var i = 0; i < mydata.DATA.length; i++) {
		var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
		output.innerHTML += str;
	}
}
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:
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.) </ol> Now for the complete template:
    <cfquery name="getstuff" datasource="blogdev" maxrows="5">
    select	id, title, posted
    from	tblblogentries
    </cfquery>
    
    <cfset jsondata = serializeJSON(getstuff)>
    
    <script>
    mydata = eval(<cfoutput>#jsondata#</cfoutput>)
    
    function showData() {
    	var output = document.getElementById('content');
    	var colMap = new Object();
    	
    	//first - find my columnsco
    	for(var i = 0; i < mydata.COLUMNS.length; i++) {
    		colMap[mydata.COLUMNS[i]] = i;		
    	}
    	
    	for(var i = 0; i < mydata.DATA.length; i++) {
    		var str = mydata.DATA[i][colMap["TITLE"]] + " was posted at " + mydata.DATA[i][colMap["POSTED"]] + "<br />";
    		output.innerHTML += str;
    	}
    }
    </script>
    
    <input type="button" value="Show Data" onClick="showData()">
    <div id="content" />
    
Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate for Extend by Auth0. He focuses on serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

Lafayette, LA https://www.raymondcamden.com

Comments