Coldfusion + JSON issue for jqGrid

Question

I'm working to setup a jqGrid JSON web service to populate JQUERY's jqGrid plugin. Currently I am outputting the following with my code:

Current: {"total":2,"records":13,"page":1, "ROWS":{"arrUsers":[{"1":1,"4":"bgf","3":"faaff","5":"ASD","2":"asd","7":"1231231233'","6":"123asd"}]}}

The desired output which is what jqGrid expects is:

Desired:

{"page":"1","total":2,"records":"13",    "rows":[{"id":"13","cell":["13","2007-10-06","Client 3","1000.00","0.00","1000.00",null]},{"id":"12","cell":["12","2007-10-06","Client 2","700.00","140.00","840.00",null]},{"id":"11","cell":["11","2007-10-06","Client 1","600.00","120.00","720.00",null]},{"id":"10","cell":["10","2007-10-06","Client 2","100.00","20.00","120.00",null]},{"id":"9","cell":["9","2007-10-06","Client 1","200.00","40.00","240.00",null]},{"id":"8","cell":["8","2007-10-06","Client 3","200.00","0.00","200.00",null]},{"id":"7","cell":["7","2007-10-05","Client 2","120.00","12.00","134.00",null]},{"id":"6","cell":["6","2007-10-05","Client 1","50.00","10.00","60.00",null]},{"id":"5","cell":["5","2007-10-05","Client 3","100.00","0.00","100.00","no tax"]},{"id":"4","cell":["4","2007-10-04","Client 3","150.00","0.00","150.00","no tax"]}],"userdata":{"amount":3220,"tax":342,"total":3564,"name":"Totals:"}}

The formatting my code is writing is incorrect, can anyone offer any suggestions to resolve?

Thanks!

Code:

<cfscript>
thestruct["page"] = 1;
thestruct["total"] = 2;
thestruct["records"] = 13;

thestruct.rows["arrUsers"] = arraynew(1);
thestruct.rows.arrUsers[1]["id"] = 1;
thestruct.rows.arrUsers[1]["FirstName"] = "asd";
thestruct.rows.arrUsers[1]["LastName"] = "faaff";
thestruct.rows.arrUsers[1]["DisplayName"] = "bgf";
thestruct.rows.arrUsers[1]["UserName"] = "ASD";
thestruct.rows.arrUsers[1]["UserAccountingCode"] = "123asd";
thestruct.rows.arrUsers[1]["Phone"] = "1231231233'";

</cfscript>



<cfinvoke component="_system.components.JSON" method="encode" data="#thestruct#" returnvariable="result" />

<cfoutput>#result#</cfoutput>

Answer 1

This is Adobe's JSON formatted return for a query object. It's nice, in that the overall data packet size is smaller, but it makes it interesting when working with frameworks that all expect the same format.

You'll either have to find a custom data reader (I wrote one for ExtJs), or you'll have to stop using the JSON return format, and use Json.CFC (google it) to gen your output.

Answer 2

I see a few basic problems to start. For one, you just need to drop the "arrUsers" element. The "rows" key is what should be an array.

Second, in your "desired" format, the values of "page" and "records" are strings (that contain numbers), but the value of "total" is a number.

"page":"1","total":2,"records":"13"

I would try wrapping the ones that should be strings in quotes (if they need to be strings in the JSON) so that CF encodes them as strings instead of numeric.

Other than that, it doesn't look like you're trying to match the exact same fields, so I can't fix it any more than this. Let me know if you still have problems.

<cfscript>
    thestruct["page"] = "1";
    thestruct["total"] = 2;
    thestruct["records"] = "13";

    thestruct.rows = arrayNew(1);
    thestruct.rows[1] = structNew();
    thestruct.rows[1]["id"] = 1;
    thestruct.rows[1]["FirstName"] = "asd";
    thestruct.rows[1]["LastName"] = "faaff";
    thestruct.rows[1]["DisplayName"] = "bgf";
    thestruct.rows[1]["UserName"] = "ASD";
    thestruct.rows[1]["UserAccountingCode"] = "123asd";
    thestruct.rows[1]["Phone"] = "1231231233'";
</cfscript>