JSON format

[Justin Edwards]

I'm sure most are aware of ACF and Railos JSON format that uses column and row to keep overhead down.   Since this format doesn't work well with any javascript without having to do some looping,  I wanted to come up with a way to make this a bit simpler.   I wanted to share in case it was of value to someone else.  


I dropped toJSON.cfc from riaforge in my CFC folder.  I created a CFC, left the returntype out,  set access to remote, output to false (no extra whitespace), and set returnFormat to plain.  I then used my usual cfarguments, and put in my cfquery.  Instead of returning the cfquery directly, it had to be processed through toJSON.  To do this I used the following lines.


               <cfinvoke component="toJSON" method="queryToJSON" data="#QueryName#" rootelement="QueryName" returnvariable="JSONQueryName" />
               <cfreturn JSONQueryName>

Then ended the function.


Of course calling the CFC method via remote opens up a security risk, so I advise you to require arguments to the method that verifies they are authorized to get the data.  

This will give you data that is a valid data store for jQuery or ExtJS.  


I don't have a blog or anything to post this with, so I figured this would be the best place to talk about it.  

I tried to write a few functions to accomplish the same thing (query to struct to json, returnformat json and then reformat, etc), but ended up having to haggle with it a lot.  This method has been the best.

It also seemed a bit simpler than this http://www.coldfusionjedi.com/index.cfm/2009/3/11/Writing-a-JSONP-service-in-ColdFusion

[Paul Kukiel]

I use CF and Railo and jQuery all the time and its fine.

Can you share a bit more code of what's going wrong currently?

Paul

--
Paul Kukiel

[Justin Edwards]

I needed the data like attached. I didn't want it with COLUMN, or
using cfjson where it returns the count and column names at the start
from the query.   There's no problem, I just wanted to share my
solution.   I wanted the code to be formatted in a way that requires
no interpretation by javascript libraries at all (standard json).  Not
like this {"ROWCOUNT":2,
"COLUMNS":["CITY","STATE"],"DATA":{"City":["Newton","San
Jose"],"State":["MA","CA"]}}

My Format. No iteration required.
{"ListLanguages":[{"LANGUAGEID":"111","LANGUAGE":"Aderi","LANGUAGECODE":"AD"},{"LANGUAGEID":"112","LANGUAGE":"Afghani","LANGUAGECODE":"AF"},{"LANGUAGEID":"113","LANGUAGE":"African
Creole/Krahn","LANGUAGECODE":"AC"},{"LANGUAGEID":"114","LANGUAGE":"Afrikaans","LANGUAGECODE":"AK"},{"LANGUAGEID":"115","LANGUAGE":"Akan","LANGUAGECODE":"AQ"},{"LANGUAGEID":"116","LANGUAGE":"Albanian","LANGUAGECODE":"AL"},{"LANGUAGEID":"117","LANGUAGE":"Amharic","LANGUAGECODE":"AM"},{"LANGUAGEID":"118","LANGUAGE":"Arabic","LANGUAGECODE":"AR"},{"LANGUAGEID":"119","LANGUAGE":"arabic
juba","LANGUAGECODE":"AJ"},{"LANGUAGEID":"120","LANGUAGE":"Armenian","LANGUAGECODE":"AN"},{"LANGUAGEID":"121","LANGUAGE":"Ashanh","LANGUAGECODE":"AS"},{"LANGUAGEID":"122","LANGUAGE":"Assyrian","LANGUAGECODE":"AY"},{"LANGUAGEID":"123","LANGUAGE":"Azeri
(Turkish)","LANGUAGECODE":"AZ"},{"LANGUAGEID":"124","LANGUAGE":"Badini","LANGUAGECODE":"BD"},{"LANGUAGEID":"125","LANGUAGE":"Bajuni","LANGUAGECODE":"BJ"},{"LANGUAGEID":"126","LANGUAGE":"Balochi","LANGUAGECODE":"BA"},{"LANGUAGEID":"127","LANGUAGE":"Bambara","LANGUAGECODE":"BB"},{"LANGUAGEID":"128","LANGUAGE":"Bangladeshi","LANGUAGECODE":"BI"},{"LANGUAGEID":"129","LANGUAGE":"Bantu","LANGUAGECODE":"BT"},{"LANGUAGEID":"130","LANGUAGE":"Basque","LANGUAGECODE":"BQ"}]}

[Justin Edwards]

http://api.flickr.com/services/feeds/photos_public.gne?jsoncallback=?tag=cat&tagmode=any&format=json

Another good example.

On Thu, Sep 8, 2011 at 11:30 PM, Justin Edwards

[Paul Kukiel]

Ahh OK,

Sorry I jumped the gun and misunderstood what you were saying.

Paul

--
Paul Kukiel

[MrBuzzy]

Flattening the query to an array of structs is pretty common, I'm not quite following why you needed to create a remote service that returns plain? Am I missing something?

(I think) I do something similar with a private method to convert the query, ie;

<cffunction name='getSomething' access='remote' returnformat='json'>

<!--- get your query from somewhere, then --->

<cfset var result.data = queryToArray(qSomething)>

<cfreturn result>

</cffunction>

[Justin Edwards]

You need to use plain format because wddx is invalid and json is not
returned as the most common format. If you don't specify the format
it will come through in a wddx packet (default). An array of structs
is what you want, but the ACF and Railo json returnformat isn't JSONP
(what popular remote services use).

You can see an example on the coldfusionjedi link I posted originally.

Nothing I tried was as simple as the solution I posted.
toJSON returns the data properly formatted. If you didn't use plain
it would further complicate your data.

Also JSONP doesn't have strict origin policy (sometimes a headache).
http://api.jquery.com/jQuery.ajax/

[Michael Offner]

are you aware of the second parameter for the function json (exists in Railo and ACF)?

SerializeJSON(var[, serializeQueryByColumns])

/micha

2011/9/9 Justin Edwards <justinl...@gmail.com>

[Justin Edwards]

Michael,

Yes I did experiment with that when coming up with a simple solution
for array of structs type formatted JSON.

{"ROWCOUNT":2, "COLUMNS":["CITY","STATE"],"DATA":{"City":["Newton","San
Jose"],"State":["MA","CA"]}}

vs

{"COLUMNS":["CITY","STATE"],"DATA":[["Newton","MA"],["San Jose","CA"]]}

but each row {"COLUMN1":"Value", "COLUMN2":"Value"} is much more
portable, but not a native option

[Michael Offner]

when i understand it right you repeat the column name for every row, if you have for example a resultset with 100 records and one column name "lastName", "lastName" exists a 100 times in your json string, somehow like this.

{"lastName":"Offner"},{"lastName":"Streit"},{"lastName":"Moser"},{"lastName":"Hobbs"}

sorry this is a huge overhead with no benefit,in this case you have the a similar amount of meta data as real data

this can double the size of a big resultset and makes a iteration through it even slower.

you should declare metadata just onces.

where you see the benefit in this structure?

[Justin Edwards]

It is a lot of overhead, but both jQuery and ExtJS make it easier to
handle data in that format. ExtJS uses JSON data stores, and that is
the native format. Most other JSON/JSONP webservices use that format
as well.

Also there is work going into a more secure JSONP standard. http://json-p.org/

You can see here that people have worked around this formatting in
coldfusion since 2007
http://www.sencha.com/forum/showthread.php?20790-Using-Coldfusion-to-provide-json-data

[MrBuzzy]

Thanks for the explanation Justin.
I don't use JSONP. I've been doing Phone development which doesn't suffer cross domain constraints. But I'm following now ;)

Getting a bit OT, isn't JSONP somewhat unecessary now XHR policy is supported by a majority of browsers? I guess it doesn't help much if a consumer dictates you must provide JSONP.

Either way, it'd be nice to have some built in functionality to flatten queries to arrays. Or have them converted by setting an attribute on cffunction.

[Justin Edwards]

Some examples of other people using this format.

http://services.digg.com/stories/top?appkey=http%3A%2F%2Fmashup.com&type=javascript
&callback=?
http://local.yahooapis.com/LocalSearchService/V3/localSearch?appid=YahooDemo&query=pizza&zip=10504&results=2&output=json&callback=?
http://www.geonames.org/postalCodeLookupJSON?postalcode=10504&country=US&callback=?
http://cache.addthiscdn.com/services/v1/sharing.en.jsonp#jsonpcallback=callback_fn_name
http://twitter.com/status/user_timeline/USERNAME.json?count=10
&callback=?

[Justin Edwards]

http://twitter.com/status/user_timeline/micrailo.json?count=10&callback=?

:-)

[Michael Offner]

i get your point, but what i still not understand is why you are using a remote CFC call for this (slow).

FYI: In Railo 3.3 you can do custom json formatting for CFCs, you simply define a function in the CFC with the following pattern:

json():string

Example of a CFC with a Custom Json Converter Function:

component {

public string function _toJson(){

        return '["Susi"]';

    }

}

then this function is executed to convert the CFC to Json instead of the default behavior.

we plan to support this with build in types as well in Appollo (Railo 4.0), you can define a function in your application.cfc for custom serialisation.

Example:

component {

   this.name="myApp";

   function onApplicationStart(...) {...}

   function toJson(any obj,boolean serializeQueryByColumns){

      if(isQuery(obj)) {

         ... // do custom format for query

         return serializedQuery:

      }

      // for all other types nothing (null) is returned, in this case Railo does the default conversion

[Michael Offner]

perhaps we could also extend the current serializeJson function as follows

serializeJson(obj,"formatX") ; // i have no idea for a good names for the 3 different formats atm

[Justin Edwards]

My main reason was it would leverage a lot of code already created and
it could be used in a variety of ways. Through mobile applications
(phonegap http://www.phonegap.com/) and from many browsers. I
haven't seen an issue with speed yet. I am pretty new to CFML
development, and one of my coworkers points out better ways to do
things sometimes.

He will read all of this in the morning and will probably come up with
better solutions from your advice. I've stayed up pretty late
tonight testing things out. (3:15am my time)

[Andrew Scott]

Justin I hear your pain, I came to the conclusion that I would have to make changes to ExtJS.

Now you mentioned both jQuery and ExtJS, and I can't comment on jQuery but I can on ExtJS. As with ExtJS you can create your own readers and proxies and many more to achieve what you need for different situations. Can't comment on jQuery here.

But here is what I do with ExtJS for any data that is returned from ColdFusion, when it comes back as their interpretation of JSon, if you have any questions please don't hesitate to ask.

Ext.ux.AndyScott.ExtReader = function(colModel) {

this.recordType = Ext.data.Record.create(colModel);

}; 

Ext.extend(Ext.ux.AndyScott.ExtReader, Ext.data.DataReader, {

readRecords : function(response) {

var records = [];

var cols = response.QUERY.COLUMNS;

var data = response.QUERY.DATA;

for (var i=0; i<data.length; i++) {

var recordObj = {};

for (var j=0; j<cols.length; j++) {

recordObj[cols[j]] = data[i][j];

}

records.push(new Ext.data.Record(recordObj));

}

return {success:true, records:records, totalRecords:response.TOTALROWCOUNT};

}

});

// CF implementation of the Ext DataProxy, for dynamic grids

Ext.ux.AndyScott.ExtProxy = function(bindHandler, errorHandler) {

Ext.ux.AndyScott.ExtProxy.superclass.constructor.call(this);

this.bindHandler = bindHandler;

this.errorHandler = errorHandler;

};

Ext.extend(Ext.ux.AndyScott.ExtProxy, Ext.data.DataProxy, {

// We need this to maintain state for cases where bindOnLoad=false

_cf_firstLoad : true,

load : function(params, reader, callback, scope, arg) {

if (!this.bindOnLoad) {

// If bindOnLoad is set to false, then the grid needs to be

// loaded up with dummy rows initially

var lrParams = {'_cf_reader':reader, 

'_cf_grid_errorhandler':this.errorHandler, 

'_cf_scope':scope, 

'_cf_gridDataProxy':this, 

'_cf_gridname':this._cf_gridName, 

'_cf_arg':arg, 

'_cf_callback':callback,

'ignoreData':true};

var data = [];

for (i=0; i<params.limit; i++) data.push(new Ext.data.Record({}));

this.loadResponse(data, lrParams);

this.bindOnLoad = true;

} else {

var pageNo = (params.start/params.limit)+1;

// Set sort and dir to '' if not specified

// These should always be passed through

if (!params.sort) params.sort= '';

if (!params.dir) params.dir = '';

this.bindHandler(this, pageNo, params.limit, params.sort, params.dir, this.errorHandler, callback, scope, arg, reader);

}

},

loadResponse : function(data, params) {

var result = null;

if (params.ignoreData) {

result = {success:true, records:data, totalRecords:data.length}

} else {

var errorCode;

if (!data) {

errorCode = "grid.extproxy.loadresponse.emptyresponse";

} else if (!data.TOTALROWCOUNT && data.TOTALROWCOUNT != 0) {

errorCode = "grid.extproxy.loadresponse.totalrowcountmissing";

} else if (!ColdFusion.Util.isInteger(data.TOTALROWCOUNT)) {

errorCode = "grid.extproxy.loadresponse.totalrowcountinvalid";

} else if (!data.QUERY) {

errorCode = "grid.extproxy.loadresponse.querymissing";

} else if (!data.QUERY.COLUMNS || !ColdFusion.Util.isArray(data.QUERY.COLUMNS)

|| !data.QUERY.DATA || !ColdFusion.Util.isArray(data.QUERY.DATA)

|| (data.QUERY.DATA.length > 0 && !ColdFusion.Util.isArray(data.QUERY.DATA[0]))) {

errorCode = "grid.extproxy.loadresponse.queryinvalid";

}

if (errorCode) {

ColdFusion.handleError(params._cf_grid_errorHandler, errorCode, "widget");

this.fireEvent("loadexception", this, params, data, e);

return;

}

result = params._cf_reader.readRecords(data);

}

        this.fireEvent("load", this, params, params._cf_arg);

        params._cf_callback.call(params._cf_scope, result, params._cf_arg, true);

    },

    

    update : function(dataSet)

{

    },

    

    updateResponse : function(dataSet)

{

    }

});

--

Regards,

Andrew Scott

WebSite: http://www.andyscott.id.au/

Google+: http://plus.google.com/108193156965451149543

[Denny]

On 9/9/11 2:17 AM, Justin Edwards wrote:
> My main reason was it would leverage a lot of code already created and
> it could be used in a variety of ways. Through mobile applications
> (phonegap http://www.phonegap.com/) and from many browsers. I
> haven't seen an issue with speed yet. I am pretty new to CFML
> development, and one of my coworkers points out better ways to do
> things sometimes.

...

The speed is going to be in the data transfer. Especially large data sets.

I used to bitch about how queries were formatted (That's not JSON!
(yes, it is)) when serialized to JSON, but I had the revelation of what
Micha just mentioned-- what a waste! Why push the *exact same* data
across the line so many times?

FWIW, if you use ORM, serializeJSON will work the way you'd expect on
object queries (cuz they're objects vs. rows of data), but you do have
to be careful about relationships (the serialization process will grab
things marked as "lazy", producing more queries & JSON objects than you
might at first expect). That's hard-core JSON, including object trees.
It can be good or bad, depending on what and how you're doing stuff.

One of the reasons (sorry kitty!) that I love dojo is that they
abstracted away the data access. *Nothing* cares about how the data is
formatted, as it's going through a level of abstraction, and it's the
abstraction's responsibility to handle the actual data. To leverage
*cfquery* serialized JSON constructs I just added a simple loop in one
of the existing "data stores" and called it CFQueryDataStore (or
something like that). In sum, dojo widgets will happily consume JSON
format X, CSV, XML, yadda yadda- without needing to change the widget
itself (and they've got some *awesome* widgets) ;]p).

It's trivial to do it on the client side, and so long as you're not
using eval in yer loop, I'd reckon it would be a far sight faster to let
the client assemble the JSON into the format it needs, vs. pushing say,
600k across the wire when there's only 100k of actual data.

All that said, I *love* Micha's suggestion for serializeJSON! It's the
intuitive way to go about it, IMHO.

:Denny

--
Railo Technologies: getrailo.com Professional Open Source
skype: valliantster (505)510.1336 de...@getrailo.com
GnuPG-FP: DDEB 16E1 EF43 DCFD 0AEE 5CD0 964B B7B0 1C22 CB62

[Todd Rafferty]

Thanks for the laugh this morning. :D

On Fri, Sep 9, 2011 at 4:43 AM, Denny <de...@getrailo.com> wrote:

One of the reasons (sorry kitty!) that I love dojo is that they

abstracted away the data access. 

--
~Todd Rafferty
Volunteer Community Manager
Railo Server - Open Source
----

Get Railo: http://getrailo.org/
Write Documentation: http://wiki.getrailo.org/

Report Bugs: https://issues.jboss.org/browse/RAILO
Request Features: https://railo.uservoice.com/

[Mark Drew]

Lost the reference? 

Some Americanism I should be aware of?

Mark Drew
Railo Technologies, Ltd.
Professional Open Source
skype: mark_railo
ma...@getrailo.com
+44 7971 852296
www.getrailo.com

[Todd Rafferty]

I said earlier on the list that every time that Den mentions dojo, a kitten dies. I was teasing him. He should just propose to dojo and marry it already. ;)

On Fri, Sep 9, 2011 at 6:37 AM, Mark Drew <mark...@gmail.com> wrote:

Lost the reference? 

Some Americanism I should be aware of?

Mark Drew
Railo Technologies, Ltd.
Professional Open Source
skype: mark_railo
ma...@getrailo.com
+44 7971 852296
www.getrailo.com

[Denny]

On 9/9/11 5:03 AM, Todd Rafferty wrote:
>
> I said earlier on the list that every time that Den mentions dojo, a
> kitten dies. I was teasing him. He should just propose to dojo and marry
> it already. ;)

Good news! I proposed, and it accepted!

The wedding will be in Utah (I'm already married) around July. I'm
having a body fabricated for "her" in Japan.

Wife #1 said that only a robot could match my libido, so...

If I hear any jokes about her "promiscuous" license, I'm knocking heads.

[Robert Zehnder]

lmfao

--
Robert Zehnder

[James Holmes]

Sure, you marry Dojo, but you just know eventually you're going to go visit jQuery and stuff money in her g-string.
--
Shu Ha Ri: Agile and .NET blog
http://www.bifrost.com.au/

[Denny]

On 9/12/11 6:40 PM, James Holmes wrote:
> Sure, you marry Dojo, but you just know eventually you're going to go
> visit jQuery and stuff money in her g-string.

Oh, no doubt. I've put money in that g-string many a time. The awesome
part? Dojoette is right there with me. She's down for the group
action. ;)

http://www.sitepen.com/blog/2010/11/15/from-jquery-to-large-applications/

jQuery is "easy", I reckon, but...

http://www.slideshare.net/rmurphey/the-jquery-divide-5287573

Not that *anything* -- even a luscious fabricated silicone mistress --
can keep me from writing crappy code. :)

I talk a good game, but truthfully, I love 'em all. I don't care what
I've got to work with. There's goodness to structure and badness to
structure. It's all a delicate balancing act. Or, more of a dance, if
you will.

Speaking of... do I hear a beat dropping? I believe so!

[Todd Rafferty]

Alright Den, I'll bite.. I'll fondle Dojoette sometime this weekend.