Tag Archives: data formatting

A little bit about JSON

A little bit about JSON, or Javascript Object Notation. As you start using the Javascript Ajax libraries, you will certainly run into JSON so it is extremely important that you get a handle on the basics of JSON.

The basic format of a properly formatted JSON string is:

{ name:'value',name:'value',name:[{sub_value:'value',sub_value:'value'},
{sub_value:'value',sub_value:'value'}]}

The values in the example above are in quotes due to the fact that they strings, if you had numeric values the quotes would be optional, as with boolean values and null. Let’s take a look at how this translates in the real world. Suppose we had the following table in a database that contained name, age, and gender.

Name Age Sex
Barney Rubble 32 Male
Fred Flintstone 33 Male
Betty Rubble 32 Female
Pebbles 1 Female
Bamm Bamm 2 Male




The JSON representation of this table would then be:

{results:5,data:[{name:'Barney Rubble',age:32,sex:'Male'},
{name:'Fred Flinstone',age:33,sex:'Male'},
{name:'Betty Rubble',age:32,sex:'Female'},
{name:'Pebbles',age:1,sex:'Female'},
{name:'Bamm Bamm',age:2,sex:'Male'}]}

You probably will not want to write the JSON by hand as in the example above, so in real life you would write a function that would convert the data from a table into the JSON format. A sample function might look like :

<cffunction name="getFlinstones" hint="get all the flinstones" returntype="string" >

<cfquery name="getData" datasource="flinstoneDB" >

        Select name, age, sex from flinstones

</cfquery>
<cfset json = "{results:#getData.recordcount#,data:[" >
<cfloop query="getData">
     <cfset json = json + "{name:'#getData.name#',age:'#getData.age#',sex:'#getData.sex#'}," >
</cfloop>
<cfset json = Trim(json)>
<cfset json = left(json, len(json)-1)>
<cfset json = json + "]}">
<cfreturn json>
</cffunction>

Of course most languages come with Json support like php: http://php.net/manual/en/book.json.php or cold fusion: http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=JavaScriptFcns_07a.html.

Look for the editable Json grid next.