Quick and Dirty JSON/Query Example

This post is more than 2 years old.

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

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 senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

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

Archived Comments

Comment 1 by Mike Benner posted on 9/25/2007 at 7:35 AM

I am so glad to see Coldfusion embrace JSON. It is my preferred format of choice for data in JS. Can't wait to start using some of these features more regularly.

Comment 2 by Andrew Scott posted on 9/26/2007 at 10:17 AM

I would also like to add that this might not be that benefical to CF8, but for those wanting to have some JSON in there previous CF instances.

CFJsonService on RiaForge is worth a look at too.

Comment 3 by Don Li posted on 3/20/2008 at 12:07 AM

I like the data rendering speed aspect of the SerializeJSON function, however, I'm concerned about business logic implementation within the data output stream with this method, the js code would be a mess to read vs. cf code for the data output part.

Comment 4 by Raymond Camden posted on 3/20/2008 at 12:11 AM

Don Li - this example was a mash up so I could get everything on one page. Normally you would have a page request the JSON via an Ajax request.

Comment 5 by Michael Damian posted on 6/9/2008 at 10:58 AM

How do you reformat the date to something any normal person would want to see, e.g. mm/dd/yyy?

Comment 6 by Raymond Camden posted on 6/9/2008 at 3:16 PM

You can do it in JS, but it can be a pain. I'd just do it server side with dateFormat.

Comment 7 by Michael Damian posted on 6/9/2008 at 3:59 PM

Thanks. Could we get a quick snippet of that in action please?

Comment 8 by Raymond Camden posted on 6/9/2008 at 4:02 PM

dateformat? Please see the CF reference. It's pretty easy.

Comment 9 by Michael Damian posted on 6/9/2008 at 4:09 PM

Yes, we all know how easy dateformat is to use. But how do you weld it into this JSON pallava. Just a quick example would do wonders and gain my utmost gratitude. Come on, it won't kill you:-)

Comment 10 by Raymond Camden posted on 6/9/2008 at 4:12 PM

But that's the thing - it doesn't need a quick example. If your query is being serialized into json, you would just use dateFormat on the query -before- your serialize it. Really - dateFormat is _extremely_ simple.

Comment 11 by Michael Damian posted on 6/12/2008 at 3:04 PM

Yes, Raymond, dateformat is easy for simple date outputs in a different format sans JSON. Anyway, I've figured out how to weld it into your JSON example. Will post some code shortly for others needing an example.

Comment 12 by Raymond Camden posted on 6/12/2008 at 3:17 PM

Be sure to post a url here. I think I may be missing something as I don't see the need - so once you blog it will probably make more sense to me. :)

Comment 13 by David Levin posted on 8/18/2009 at 11:52 PM

Is it possible to serialize a query to JSON and then deserialize it back to a query object again?

Comment 14 by Raymond Camden posted on 8/18/2009 at 11:56 PM

Yes, it is possible. When you deserialize it though, ensure strictMapping (second optional arg) is set to false.

Comment 15 by David Levin posted on 8/19/2009 at 12:36 AM

Ah... nice.

Here is an example that worked:

<cfset myQuery = QueryNew("ID,Name","INTEGER,VARCHAR")>
<cfset temp = QueryAddRow(myQuery)>
<cfset temp = QuerySetCell(myQuery,"ID","1")>
<cfset temp = QuerySetCell(myQuery,"Name","Dave")>

<cfset myJSON = SerializeJSON(myquery)>
<cfset myNewResult = DeSerializeJSON(myJSON,false)>

<cfdump var="#myNewResult#">

Comment 16 by Larry Lee posted on 9/28/2009 at 9:19 PM

Ray,
You mention at the top of this blog entry that this is a non-ajax example.
This helped open up a world of options for writing custom ajax renderers and validators and I just want to share one 'gotcha' I ran into after wresting with the AJAX version of this for a while.
When returning the JSON object from an AJAX call to a cfc, <cfreturn SerializeJSON(...) did not return the data in a format I needed for a column. The answer was to use returnformat="JSON".
After that subtle difference, I could parse the JSON string with a column map.
Here's a snippet of sample code from the javascript side:

var grid = ColdFusion.Grid.getGridObject("busforecast");
var gridFoot = grid.getView().getFooterPanel(true);
var gridFoot = grid.getView().getFooterPanel().setVisible(true); //false to remove paging TMS 9626 LLee 12/24/08
var ds = grid.getDataSource();
var forecastObj = new CFCs.ajaxForecastDAO;
<cfoutput>
var getBusmgrSummaryForecastAJAX = forecastObj.getBusmgrSummaryForecastAJAX('#dsn#','#curFY#',#S_OID#, '#qryBusmgrInitialForecast.forecast_level#',1,1);
</cfoutput>
var mydata = getBusmgrSummaryForecastAJAX;
var colMap = new Object();

//find my columns
for(var i = 0; i < getBusmgrSummaryForecastAJAX.COLUMNS.length; i++) {
colMap[getBusmgrSummaryForecastAJAX.COLUMNS[i]] = i;
}
for(var i = 0; i < getBusmgrSummaryForecastAJAX.DATA.length; i++) {
var prioractual = getBusmgrSummaryForecastAJAX.DATA[i][colMap["PRIORACTUAL"]];
var priorcert = getBusmgrSummaryForecastAJAX.DATA[i][colMap["PRIORCERT"]];
var estimatedusage = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ESTIMATEDUSAGE"]];
var actualusage = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ACTUALUSAGE"]];
var fullyearprojection = getBusmgrSummaryForecastAJAX.DATA[i][colMap["FULLYEARPROJECTION"]];
var acctmgrforecast = getBusmgrSummaryForecastAJAX.DATA[i][colMap["ACCTMGRFORECAST"]];
var forecast = getBusmgrSummaryForecastAJAX.DATA[i][colMap["FORECAST"]];

//later in the script I add the var values to a 2nd footer of the grid using Ext.DomHelper.append..

//On the cfc side just use the usual access="remote" returntype="any" but be sure to add returnformat="JSON".

Comment 17 by viky posted on 12/20/2009 at 12:56 AM

Hi,

I am using a dynamic java script table which accepts data in json format....I was able to loop through the query object in cfm code and then arrange my data in json like format.......I didnt loop through the object in javavscript nor did I used serialisejson.But my problem is for pagination I am unable to fetch the correct data.Since I am using inline JavaScript and then on click for pagination I am calling the same page but this JavaScript table is not loaded at all........I cannot write this code in external js as I need to use the cold fusion variable in the javascript function....Please advise

Comment 18 by Raymond Camden posted on 12/20/2009 at 9:25 PM

That doesn't really make much sense to me. I guess if I could see it it would.

Comment 19 by viky posted on 12/20/2009 at 10:28 PM

Hi ,
I ahve modigied my code.
Please have a look at the below code.

Initially I am showing 30 records.

I used serializejson in an inline javascript.
<cfparam name="mxrow" default="30">
<cfquery name="myquery" datasource="testdsn" maxrows=#mxrow#>
select * from emp
</cfquery>
<cfoutput>
<script>
var getQuery=#serializejson(myquery) #;
alert(getQuery);
//I plan to write a java script table here below
dynamicTableLoad();
</script>
</cfoutput>
Alert is giving me the correct set of values for the query.

Now if wish to seethe next 30 records i.e from 31 to 60,
I intend to write a JavaScript function which again calls the same page through ajax.

But on ajax call the javascript is not loaded at all.

Comment 20 by Raymond Camden posted on 12/22/2009 at 1:09 AM

I'm sorry - I just don't quite get what you are doing here. Your front end code should be making a request to load data to the server. The server returns the data serialized in JSON. It should include information about the total # of results and the current page of results. Your code there isn't enough, nor should it be running any actual JS. It should just return JSON.

Comment 21 by John Manoah posted on 5/11/2010 at 7:10 PM

Another quick way is to run the response through an eval function and then parse it according to the column names.

For e.g.

var objdata = eval('('+responseFromServer+')');
(where responseFromServer is the JSON serialized query object in the cfc file)

Now, objdata.DATA.[column_name] would return the respective data.

For e.g. objdata.DATA.INTUSERID, objdata.DATA.STRFIRSTNAME etc (CAPS are essential since JSON in ColdFusion converts the names to all caps!)

Please note: This works only when you set the 'serializeQueryByColumns' in the SerializeJSON to "TRUE".

Comment 22 by vasu polepalle posted on 9/18/2010 at 1:19 AM

would you plz explain what the following line is doing (inside the first for look). To me it looks like its assigning int to an object.

colMap[myData.COLUMNS[i]]=i;

Comment 23 by Raymond Camden posted on 9/18/2010 at 1:24 AM

The variable myData.COLUMNS[i] is one column. It is a string, like Name. i is the index.

What this does is create a mapping by where the _name_ of the column has a value of the index.

I can then use that to get data by name and not index.

So imagine that AGE was the 3rd column. Instead of doing

data[3]

I can do

data[colMap.AGE]

Make sense?

Comment 24 by Cindi posted on 8/8/2013 at 10:40 PM

We wrote a wrapper service to handle date formatting when converting query results to JSON string. Instead of using serializeJSON() function, we have a custom encode()function that will fix that dates is isDate(value) is true. Loop over the values and format them with a private formatting function - something like this:

private any function format(val) {
var value = arguments.val;
// escape certain characters in strings...
value = replace(value,'\','\\','all');
value = replace(value,'"','\"',"all");
value = replace(value,'/','\/',"all");
value = replace(value,"#chr(13)#","\r","all");
value = replace(value,"#chr(10)#","\n","all");
value = replace(value,"#chr(9)#","\t","all");
var newvalue = '"#value#"';
if (isNumeric(value)) {
if (variables.quotedNumbers == false) {
newvalue = value;
}
}
//if (value == 0 || value == 1) {
// newvalue = value;
// }
if (value == 'yes' || value == 'no' || value == 'true' || value == 'false' ) {
if (variables.convertBoolean == true) {
if (value == 'yes') { newvalue = true;}
if (value == 'no') { newalue = false; }
}
else {
if (value != 1 && value != 0 ) {
newvalue = '"#value#"';
}
}
}
if (isDate(value) && isNumeric(value) == 'NO' && variables.fixDates == true) {
newvalue = '"#dateFormat(value,"mmmm dd, yyyy ") & timeFormat(value,"hh:mm:ss")#"';
}
return newvalue;
}

Comment 25 by Zy Danielson posted on 3/17/2016 at 6:06 PM

Hi Raymond. I know you wrote this article 9 years ago, but it is still doing good work to this day. I wanted to thank you. For months, literally, I've been trying to figure out how to use AJAX to get data from a ColdFusion server and process it in the browser in JavaScript. Sounds like it should be so easy, and yet, for some reason it isn't. Or wasn't, until I read your article.

The one stumbling block for me, after getting rid of whitespace problems first, is that my AJAX call returned a string that looked exactly like what you started with in your article (in terms of structure). The problem, of course, was that what I got back wasn't a complex JavaScript object, it was a simple JSON string:

{"COLUMNS":["JID","NAME"],"DATA":[[13,"B2I"],[14,"B2II"],[15,"B2III"]]}

To get it from string to object was simply the use of JSON.parse().

Here is how I adapted your article to my AJAX call:


$.ajax({
type: "POST",
url: "ajax/getJobs.cfm?jcid="+jobClassID,
timeout: 100000,
success: function(data) {
var mydata = JSON.parse(data);
var colMap = new Object();
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 = "Job ID " + mydata.DATA[i][colMap["JID"]] + " Job Name " + mydata.DATA[i][colMap["NAME"]];
document.write(str + "<br>");
}
}
})

I just thought I'd post in case there is someone else out there who, like me, has been having trouble figuring out how to actually make that AJAX call and then do something with it.

If anyone is interested, I can leave another comment showing the server side of it too, since getting rid of that excess whitespace can be tricky.

In any case, thanks Raymond. Your words of 9 years ago are still alive and teaching today!

Comment 26 (In reply to #25) by Raymond Camden posted on 3/17/2016 at 6:30 PM

I'm happy this is still useful for you. :)

Comment 27 (In reply to #0) by Raymond Camden posted on 8/14/2017 at 9:43 PM

Glad this helped - I see it is a bit ugly formatting wise - going to fix now.

Comment 28 by Eric J. Peterson posted on 8/22/2018 at 8:57 PM

Almost 11 years later, and I too have found this article very helpful. A nice, simple explanation. Thank you.

Comment 29 (In reply to #28) by Raymond Camden posted on 8/23/2018 at 1:39 PM

Happy to help!