coldfusion and JSON

[Guest]

i want to take a query and format it into JSON. i'm looking at the SerializeJSON function but for whatever reason CF is putting the result in a strange format not consistent with JSON.

For example this query:

<cfquery name="data" datasource="dSource">

    SELECT first_name, last_name

    from emps

    where 1= 1

    and fiscal_yr = 2012

    group by last_name, first_name

    ORDER BY last_name

</cfquery>

theJSON = SerializeJSON(data)

the value of theJSON is something like:

{"COLUMNS":["FIRST_NAME","LAST_NAME"],"DATA":[["Doe","John"],["Smith","Jane"],["Patel","Joe"]]}

but I want it like this (in the standard JSON format):

{

    "items": [

        {

            "first_name": "Doe",

            "last_name": "John"

        },

        {

            "first_name": "Smith",

            "last_name": "Jane"

        },

        {

            "first_name": "Patel",

            "last_name": "Joe"

        }

    ]

}

how can this be accomplished without writing something tedious like this:

{

    "items": [

            <cfoutput query="data">

            {

            "first_name": "#first_name#",

            "last_name": "#last_name#"           

        }<cfif currentrow neq data.recordcount>,<cfelse>]</cfif>

            </cfoutput>

}

[JR__Bob__Dobbs]

When converting a query object to JSON ColdFusion does not create an array of key/value pairs.  The format used is documented at http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-79fa.html

I suspect that in order to get key/value pairs you will need to write your own conversion code.

[BKBK]

BosDog wrote:

theJSON = SerializeJSON(data)

the value of theJSON is something like:

{"COLUMNS":["FIRST_NAME","LAST_NAME"],"DATA":[["Doe","John"],["Smith", "Jane"],["Patel","Joe"]]}

I think that this design for the JSON in ColdFusion is intentional. Those in the business of designing computer languages usually go for efficiency and for symmetrical, easy-to-reverse procedures.

As you can see, there is no repetition of labels in ColdFusion's JSON. Also, I suspect that the thinking is that, in the reverse procedure, deserializeJSON, arrays (square brackets) are easier to reverse than structs (curly brackets).

how can this be accomplished without writing something tedious like this:

{

    "items": [

            <cfoutput query="data">

            {

            "first_name": "#first_name#",

            "last_name": "#last_name#"           

        }<cfif currentrow neq data.recordcount>,<cfelse>]</cfif>

            </cfoutput>

}

Like JR \"Bob\" Dobbs, I think you will have to do something tedious to get the JSON structure you want.

Message was edited by: BKBK