Twitter: raymondcamden


Address: Lafayette, LA, USA

Quick and Dirty JSON/Query Example

09-20-2007 30,088 views JavaScript, ColdFusion 24 Comments

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

24 Comments

  • Commented on 09-24-2007 at 10:35 PM
    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.
  • Andrew Scott #
    Commented on 09-26-2007 at 1: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.
  • Don Li #
    Commented on 03-19-2008 at 3:07 PM
    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.
  • Commented on 03-19-2008 at 3:11 PM
    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.
  • Commented on 06-09-2008 at 1:58 AM
    How do you reformat the date to something any normal person would want to see, e.g. mm/dd/yyy?
  • Commented on 06-09-2008 at 6:16 AM
    You can do it in JS, but it can be a pain. I'd just do it server side with dateFormat.
  • Commented on 06-09-2008 at 6:59 AM
    Thanks. Could we get a quick snippet of that in action please?
  • Commented on 06-09-2008 at 7:02 AM
    dateformat? Please see the CF reference. It's pretty easy.
  • Commented on 06-09-2008 at 7:09 AM
    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:-)
  • Commented on 06-09-2008 at 7:12 AM
    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.
  • Michael Damian #
    Commented on 06-12-2008 at 6:04 AM
    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.
  • Commented on 06-12-2008 at 6:17 AM
    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. :)
  • Commented on 08-18-2009 at 2:52 PM
    Is it possible to serialize a query to JSON and then deserialize it back to a query object again?
  • Commented on 08-18-2009 at 2:56 PM
    Yes, it is possible. When you deserialize it though, ensure strictMapping (second optional arg) is set to false.
  • Commented on 08-18-2009 at 3:36 PM
    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#">
  • Commented on 09-28-2009 at 12: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#',#SOID#, '#qryBusmgrInitialForecast.forecastlevel#',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".
  • viky #
    Commented on 12-19-2009 at 1:56 PM
    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
  • Commented on 12-20-2009 at 10:25 AM
    That doesn't really make much sense to me. I guess if I could see it it would.
  • viky #
    Commented on 12-20-2009 at 11:28 AM
    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.
  • Commented on 12-21-2009 at 2:09 PM
    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.
  • Commented on 05-11-2010 at 10:10 AM
    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".
  • vasu polepalle #
    Commented on 09-17-2010 at 4:19 PM
    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;
  • Commented on 09-17-2010 at 4:24 PM
    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?
  • Cindi #
    Commented on 08-08-2013 at 1: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;
       }

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty