How do I ensure SerializeJSON keeps trailing/leading zeroes?

Question

EDIT 3 Problem below exists for Coldfusion 9.0, updating to 9.0.1 does indeed fix this

I have an application that is using SerializeJSON to encode query results:

#SerializeJSON('Ok works fine')#

Unfortunately it trims the trailing zeroes from numbers:

#SerializeJSON(12345.50)#

manually if i was to make the same value a string, same thing occurs

#SerializeJSON('12345.50')#

How can I prevent this from happening?

EDIT - my scenario specifics

Database (Oracle) has these example values stored on a row

• benefactor_id : 0000729789 varchar2(10)
• life_gift_credit_amt : 12345.50 number(14,2)

When I query using Coldfusion 9.0.1 (cfscript if it matters) , here is an RC dump, notice the id string retains leading zeroes, but the number column has removed trailing zero. While that is interesting, it doesnt matter to the Original issue as i can create a query manually to retain that trailing zero like below, it still gets lost in the serializeJSON

I take the query results, and encode the values using serializeJSON. The JSON is consumed by jquery Datatables ajax. Notice the id string has become a number, and has added the '.0' as Miguel-F mentioned

<cfscript>
...
  rc.sql = q.setsql;
  rc.qResult = q.execute().getresult();

  savecontent variable="rc.aaData" {
    for (i=1; i <= rc.qResult.RecordCount; i++) {
      writeOutput('{');
      for (col=1; col <= iColumnsLen; col++) {
        // the following line contains a conditional specific to this example
        writeOutput('"#aColumns[col]#":#SerializeJSON(rc.qResult[aColumns[col]][i])#'); 
        //former statement, discarded due to not being able to handle apostrophe's ... writeOutput('"#jsStringFormat(rc.qResult[aColumns[col]][i])#"');
        writeOutput((col NEQ iColumnsLen) ? ',' : '');
      }
      writeOutput('}');
      writeOutput((i NEQ rc.qResult.RecordCount) ? ',' : '');
    }
  };
</cfscript>  

I was oringially using jsStringFormat instead of serializeJSON, but this would return invalid JSON due to the comments text area containing apostrophe's ect

{
    "sEcho": 1,
    "iTotalRecords": 65970,
    "iTotalDisplayRecords": 7657,
    "aaData": [
        {
            "nd_event_id": 525,
            "benefactor_id": 729789.0,
            "seq_number": 182163,
            "life_gift_credit_amt": 12345.5,
            "qty_requested": 2,
            "b_a_comment": "#swap",
            "pref_mail_name": "Jay P. Rizzi"
        }
    ]
}

EDIT 2

a quick sidenote, if i change my serialization line to

writeOutput('"#aColumns[col]#": "#SerializeJSON(rc.qResult[aColumns[col]][i])#"'); 

then my result set changes to placing records in double quoting , but also double double quotes strings, while still removing the trailing zero; It leads me to believe serializeJSON is casting the value as a type?

"aaData": [
    {
        "nd_event_id": "525",
        "benefactor_id": "729789.0",
        "seq_number": "182163",
        "life_gift_credit_amt": "12345.5",
        "qty_requested": "2",
        "b_a_comment": ""#swap"",
        "pref_mail_name": ""JayP.Rizzi""
    },

Answer 1

Taken from the comments

The original poster (OP) of this question initially reported that they were having this issue with ColdFusion 9.0.1. As it turned out they were actually running ColdFusion 9.0.0. This is significant because Adobe had made changes to how the SerializeJSON() function treats numbers in version 9.0.1. When the server was upgraded to version 9.0.1 these issues were resolved.

This blog post by Raymond Camden discusses the changes made in 9.0.1 - Not happy with the CF901 JSON Changes?

In that blog post he references bug 83638 that had been entered and then fixed in HotFix 1 for version 9.0.1 - Cumulative Hotfix 1 (CHF1) for ColdFusion 9.0.1

If you search the BugBase for JSON under version 9.0.1 there are several reporting the same issue as the OP.

Those reported bugs also mentioned another issue that the OP had not initially reported, that a .0 was being appended to integers as well. Later in the discussion the OP confirmed that they too were seeing this behavior. This lead them to verify the ColdFusion version being utilized and found that it was not 9.0.1.

Answer 2

This is a bit baffling... I tested in CF 9 as well. Not really knowing what you are doing with the serialized data (passing as a service, outputting on a page, etc.), I put together some test patterns. One possible solution is if only trying to serialize a sing value - don't. You can actually run deserialize against your numeric value without serializing, and all it does is strip the trailing 0. Otherwise, if you must serialize a single value and don't want the trailing 0 stripped, set the variable to contain the quotation marks

<cfset manualserial = '"111.10"'>
<cfdump var="#DeSerializeJson(manualserial)#">

At this point you can us Deserialize and see that it maintains the 0, with output of 111.10 Below is some additional testing, so you can see what happens when serializing an array while trying to keep the trailing 0... no luck. However when I forwent the built in CF serialize and just created a serialized string, the trailing 0 is maintained (refer to var customarr and d_customarr in WriteDump example below).

Hope that helps a little.

<cfscript>
/*initial testing*/
string = SerializeJSON('Ok works fine');
numericstring = SerializeJSON('12345.50');
numeric = SerializeJSON(12345.50);
arr =  SerializeJSON([12345.50,12345.10,'12345.20']);
arrFormat = SerializeJSON([NumberFormat(12345.50,'.00') & ' ',12345.10,'12345.20']);

d_string = DeSerializeJSON(string);
d_numericstring = DeSerializeJSON(numericstring);
d_numeric = DeSerializeJSON(numeric);
d_arr = DeSerializeJSON(arr);
d_arrFormat = DeSerializeJSON(arrFormat);


/*technically, there is no need to serialize a single string value, as running through DeSerialize just trims the trailing 0
if you need to do so, you would want to pass in as a string with quotation marks*/
customstring = '"12345.50"';
d_customstring  = DeSerializeJSON(customstring);
customarr = '["12345.50","12345.10","12345.20"]';   //--you can format your own array instead of using CF to serialize 
d_customarr  = DeSerializeJSON(customarr);

WriteDump(variables);
</cfscript>

=======appended possible solution b========

I think that manually serializing your records may be the most stable option, try this example, and if it works you should be able to add the function to a cfc or create a udf for re-use. Hope it helps.

<cfscript>
q = QueryNew('nd_event_id,benefactor_id,seq_number,life_gift_credit_amt,qty_requested,b_a_comment,pref_mail_name',
            'Integer,VarChar,Integer,Decimal,Integer,VarChar,VarChar');
r = queryaddrow(q,2);   
querysetcell(q, 'nd_event_id', 525, 1);
querysetcell(q, 'benefactor_id', 0000729789, 1);    
querysetcell(q, 'seq_number', 182163, 1);
querysetcell(q, 'life_gift_credit_amt', 12345.50, 1);   
querysetcell(q, 'qty_requested', 2, 1); 
querysetcell(q, 'b_a_comment', '##swap', 1);    
querysetcell(q, 'pref_mail_name', 'Jay P. Rizzi', 1);       
querysetcell(q, 'nd_event_id', 525, 2);
querysetcell(q, 'benefactor_id', 0000729790, 2);
querysetcell(q, 'seq_number', 182164, 2);   
querysetcell(q, 'life_gift_credit_amt', 12345.90, 2);   
querysetcell(q, 'qty_requested', 10, 2);    
querysetcell(q, 'b_a_comment', '##swap', 2);    
querysetcell(q, 'pref_mail_name', 'Jay P. Rizzi', 2);   
WriteDump(q);

s = membershipManualSerializer(q);

public string function membershipManualSerializer(required query q){
    var jsonString = '{"aaData":[';
    var cols = listtoarray(q.columnList,',');

    for(var i=1; i lte q.recordcount; i++){

        jsonString &= "{";

        for(var c=1;c lte arraylen(cols);c++){
            jsonString &= '"' & cols[c] & '":"' & q[cols[c]][i] & '"';
            jsonString &= (c lt arraylen(cols))? ",":"";
        }

        jsonString &= (i lt q.recordcount)? "},":"}]";
    }

    jsonString &="}";

    return jsonString;
}


WriteOutput(s);
WriteDump(DeserializeJson(s));      
</cfscript>