CF to output usable JSON

[nikos101]

Hi, anyone know a good way to get CF to output JSON from a query in the standard format that JS devs use and not the way CF does it?

[Adam Cameron.]

JSON is JSON: there's not different flavours of it.  What about the JSON is not to your JS chums' liking?

--

Adam

[nikos101]

Basically CF gives us this:

{"COLUMNS":["SWIFTID","CURRENCY"],"DATA":[["EUR","Euro"],["USD","Dollar"]]}

Basically we need something like this instead that we can easily loop through with JS

{

     {"SWIFTID":"EUR","CURRENCY":Euro},

     {"SWIFTID":"USD","CURRENCY":Dollar}

}

Is there a simple way to get this?

[Adam Cameron.]

Can you not just as easily loop through the DATA array?

If you need to know the column names in your code (for whatever reason), they correspond to the equivalent entry in the COLUMNS array.

Your mooted idea would not work for a recordset, as it's a struct and structs have no sense of ordering, which is intrinsic to a recordset.  Plus why would you want to reproduce the column names EVERY ROW?  They don't change.  That's just bloat.

The short answer is "no: there is no way to 'simply' change the way CF serialises a query into JSON to fit your somewhat idiosyncratic format (which loses important information)".  You'd need to loop over the query and build substructs per row, and stick those into a parent struct.  Then serialise that.  But I really don't think you want to be doing that.

--
Adam

[BKBK · Accepted solution]

As Adam says, you will have to loop through the data yourself. There are no conversion functions to do it for you. At least, none that I am aware of.

ColdFusion's JSON representation of the query is a struct that has 2 keys, Columns and Data. Columns (always) has one element, an array comprising the column names of the result-set. Data contains an array which itself consists of subarrays. The subarrays represent the respective rows of data. The number of subarrays is therefore arbitrary.

The JS version of JSON you've shown is, in ColdFusion terms, a structure of a structure. You could convert from the ColdFusion version to the JS version as follows:

<cfset testJSON={"COLUMNS":["SWIFTID","CURRENCY","COUNTRY"],"DATA":[["EUR","Euro","EU"],["USD","Dollar","USA"],["GBP","Pound Sterling","UK"],["KRR","Krone","Denmark"],["JPY","Japanese Yen","Japan"]]}>

<cfscript>

function reJSON(json) {

    var newJSON = structnew();

    var i = 1;

    var j = 1;

    /* loop across subarrays that represent rows of data*/

    for(i = 1; i LTE arrayLen(json.data); i=i+1) {

        newJSON = structnew();

        /* Create struct of struct, using each column name as key */

        for(j=1; j LTE arrayLen(json.columns); j=j+1) {

            newJSON[json.columns]=json.data;

        }

    } return newJSON;

}

</cfscript>

<cfdump var="#rejson(testJSON)#">

[nikos101]

Wow, thanks so much BKBK, thats really cool code sir!

I do get an error though

Invalid CFML construct found on line 1 at column 17.

[nikos101]

and if I pass in this: <cfdump var="#rejson(serializeJSON(rates))#">

I get this:

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.

im using CF8

[BKBK]

Ah, it's just a minor conversion question. My example works because testJSON is already a struct object. When you serialize a query, you get a JSON string. So you first have to convert that string to a struct object. This is done by deserializing.

You can proceed in one of 2 ways. Firstly, assuming the query is rates,

jsonString = serializeJSON(rates);

testJSON = deserializeJSON(jsonString);

Then proceed as above. Secondly, you may redesign the function to accept a string instead. Deserialization should then take place within the function. Something like this:

testJSONString = serializeJSON(rates);

<cfscript>

function reJSON(json_string) {

    var json = deserializejson(json_string);

    var newJSON = structnew();

    var i = 1;

    var j = 1;

    /* loop across subarrays representing rows of data*/

    for(i = 1; i LTE arrayLen(json.data); i=i+1) {

        newJSON = structnew();

        /* Create struct of struct, using each column name as key */

        for(j=1; j LTE arrayLen(json.columns); j=j+1) {

            newJSON[json.columns]=json.data;

        }

    } return newJSON;

}

</cfscript>

<cfdump var="#rejson(testJSONString)#">

[nikos101]

ah, thanks BKBK, always a helping hand to me

[nikos101]

the code works now, is there an easy way to remove the row number, ie 56 & 19?

{"56":{"COUNTRY":"Viet Nam","CODE":"VND"},"19":{"COUNTRY":"E  Carib","CODE":"XCD"}}

[Adam Cameron.]

Nikos, you need to understand that all JSON is is a serialised struct (for all intents and purposes).  There's no magic to any of this.

To answer your question: a struct is a data structure that has key/value pairs, so it needs to have keys (the keys being "56" and "19" in this case).  If you don't want to have keys, then use an array instead.  It'll still have indices, but they're implicit.

TBH, the best approach here would be to simply use the data stucture that CF gives you alredy for a serialised query: it's already in the most sensible structure.  Don't mess around converting it into some other structure based on your JS developers "not getting it".  You should not be doing more work than is necessary based on a developer not knowing how to do something, which is what it sounds like here.

--

Adam