Nested JSON Results

Digging through JSON objects to get what we need.

Revisit some bad code written from a previous post.

This is a bit of a rebuild or rebuttle from a previous post about the funny thing with ColdFusion. I know in that post some of what I did was bad programming. Most of that in the name of pushing it to the server and for the sake of the example and illustrating the parts of ColdFusion.
I couldn’t let the bad code sit though and have people think this is perfect. I am sure there are some scenarios, more complicated scenarios where something similar might be needed. Here, I am going to try to make that example and scenario more complicated and show something.

If you remember we ended with ColdFusion returning to JavaScript a Serialized JSON array such as below.

        <!--- separating the "data" from the "columns" --->
        <cfset local.response1 = (DeserializeJSON(SerializeJson(local.storeStatusSet)).DATA) >
        
        <!--- serialize in JSON format the data returned from the query --->
        <cfset local.response = SerializeJson(local.response1)>
        
        <cfreturn local.response>

To complicate things, I have a dozen (yes 12) results returned that are all part of that CFQuery and the Seialized JSON array. For demonstration purposes we are only going to go with 3.
This would make the above example something similar to below.

        <cfstoredproc datasource="#session.dsn#" returnCode="true" result="storesStatus" procedure="dbo.getStoreStatus" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@startDate"      NULL="#NOT(len(arguments.startDt))#"         value="#arguments.startDt#" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@endDate"        NULL="#NOT(len(arguments.endDt))#"           value="#arguments.endDt#" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@storeList"      NULL="#NOT(len(arguments.storeList))#"	   value="#arguments.storeList#" >

            <cfprocresult name="local.storeStatus1" resultset="1" >
            <cfprocresult name="local.storeStatus2" resultset="2" >
            <cfprocresult name="local.storeStatus3" resultset="3" >
        </cfstoredproc>

        <!--- separating the "data" from the "columns" --->
        <cfset local.response1 = (DeserializeJSON(SerializeJson(local.storeStatus1)).DATA) >
        <cfset local.response2 = (DeserializeJSON(SerializeJson(local.storeStatus2)).DATA) >
        <cfset local.response3 = (DeserializeJSON(SerializeJson(local.storeStatus3)).DATA) >
        
        <!--- serialize in JSON format the data returned from the query --->
        <cfset local.response = SerializeJson({"IssueStatus":"#local.response1#","Progress":"#local.response2#","Backlog":"#local.response3#"})>
        
        <cfreturn local.response>

This is then utilized on the client side for the dozen (in this example only 3) charts and graphs.

    $.ajax({
        data: {....},
        type: 'POST',
        success: function(data) {
            createIssueStatusChart(JSON.parse(data)['IssueStatus']);
            createProgressChart(JSON.parse(data)['Progress']);
            createBacklogChart(JSON.parse(data)['Backlog']);
        }
    });

Side note, I like how JSON has become a ‘universal’ data type of sorts. It is the default standard of just passing data between any two things, and both things know it and know how it should be. Its simple and elegent and looks great. Often, at the core, an “array” in one language is not structured or exactly the same as an “array” in another language, and some simpler languages don’t have array’s. This, JSON, makes a great bridge that is universal and easy to read.

That is ugly and unnecessary and hard to read on the server side with the Serialization and deserialization and such. Ultimately, there is not much process improvement with a larger data set.

        <cfstoredproc datasource="#session.dsn#" returnCode="true" result="storesStatus" procedure="dbo.getStoreStatus" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@startDate"      NULL="#NOT(len(arguments.startDt))#"         value="#arguments.startDt#" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@endDate"        NULL="#NOT(len(arguments.endDt))#"           value="#arguments.endDt#" >
            <cfprocparam type="in" cfsqltype="cf_sql_varchar" dbvarname="@storeList"      NULL="#NOT(len(arguments.storeList))#"	   value="#arguments.storeList#" >

            <cfprocresult name="local.storeStatus1" resultset="1" >
            <cfprocresult name="local.storeStatus2" resultset="2" >
            <cfprocresult name="local.storeStatus3" resultset="3" >
        </cfstoredproc>
        
        <!--- serialize in JSON format the data returned from the query --->
        <cfset local.response = SerializeJson({"IssueStatus":"#local.storeStatus1#","Progress":"#local.storeStatus2#","Backlog":"#local.storeStatus3#"})>
        
        <cfreturn local.response>

Much cleaner ColdFusion now, though it sends over the unneccesary (in this case) “COLUMNS” JSON object in the the returned result for the JSON array as ColdFusion breaks the column names / headers of a CFQuery and puts it into the “COLUMNS” in the array and then “DATA” into a separate JSON object. In this scenario, the library only wants data, no key->value pairs or labels or columns and such. Lets take care of that in the JavaScript now.

    $.ajax({
        data: {....},
        type: 'POST',
        success: function(data) {
            createIssueStatusChart( (JSON.parse(data)['IssueStatus'])["DATA"] );
            createProgressChart( (JSON.parse(data)['Progress'])["DATA"] );
            createBacklogChart( (JSON.parse(data)['Backlog'])["DATA"] );
        }
    });

One thing that was a “gotcha” for me is that I wanted to reference the “DATA” like this…

JSON.parse(data)['IssueStatus']["DATA"]

The thought was an “order of operations” type of thought. I have the data variable that has the JSON string, let me parse that, then reference the needed data set ( IssueStatus ) and then get the data.
With the above wrong example, what this is really looking for is nested element in the JSON array that is at the location [‘IssueStatus’][‘Data’]. To put it in another way, its looking for the column named ‘data’ within the row named ‘IssueStatus’. Almost like it was expecting a multi-dimensional array, which is slightly different than a nested objects in an array. That is not exactly it. However the thing is that there is a nested array in the array, so we have to make sure that it realizes that.
With that “math” mind and thinking of the “order of operations”, use parenthesis to make sure certain things are done first before others or atleast separated out from other parts. In this case, its getting the array object of IssueStatus after we parse the JSON array of data. Then we have two arrays in that from ColdFusion, one for columns and one for data. So now we can reference the data object part of the array and get the results we want.

(JSON.parse(data)['IssueStatus'])["DATA"]

The coldfusion is much cleaner now, the JavaScript is about the same but can be improved upon, and there is no gain/loss for doing things this way.

Enjoy!