ColdFusion 11: query column types preserved when serialising to JSON, eh?

G'day:
I'm playing catch-up with the comments on the blog, and one of said comments from Roberto Marzialetti got me looking at an old article: "ColdFusion vs JSON. How to make a developer cry". In this article I mooted an alteration to how ColdFusion serialises query data so as to preserve the column types. This is critical with queries because CFML operations on this data such as QoQ is type-sensitive. So we need the data type.

Then I remembered reading something in the doc for ColdFusion 11 about preservation of data types in JSON, and I momentarily got "excited":

Data type preservation

The ColdFusion language is typeless and does not evaluate or preserve the type information at the time of code generation. Due to this reason, the datatype of a property is being decided during runtime, which may cause some unexpected behavior. At the time of JSON serialization, ColdFusion attempts at converting a string to a number. If the attempt is successful, then the passed data type is treated as number irrespective of whether you wanted it to be a string or not.

Starting from ColdFusion Splendor, the data type is preserved during the code execution time for Query and CFCs.

SerializeJSON considers datatypes defined in the database for serialization. If the database defines a column as a string, any number inserted into the column will still be treated as a string by SerializeJSON.

Cool! As with most of my excitement relating to how ColdFusion deals with JSON, my hopes were dashed as soon as I looked at it though. Here's some code:

fromDB = queryExecute("SELECT id,mi FROM words WHERE type = ?",["day"],{datasource="scratch_mssql"});
json = serializeJson(fromDB);
fromJson = deserializeJson(json, false);
writeDump([
    {data= {
        fromDB=fromDB,
        fromJson=fromJson
    }},
    {json=json},
    {metadata= {
        fromDB=getmetadata(fromDB),
        fromJson=getmetadata(fromJson)
    }}
]);

And the output:

array
1	struct data struct fromDB query ID MI 1 18 Rahina 2 19 Ratu 3 20 Raapa 4 21 Rapare 5 22 Ramere 6 23 Rahoroi 7 24 Ratapu fromJson query ID MI 1 18 Rahina 2 19 Ratu 3 20 Raapa 4 21 Rapare 5 22 Ramere 6 23 Rahoroi 7 24 Ratapu
2	struct json {"COLUMNS":["ID","MI"],"DATA":[[18,"Rahina"],[19,"Ratu"],[20,"Raapa"],[21,"Rapare"],[22,"Ramere"],[23,"Rahoroi"],[24,"Ratapu"]]}
3	struct metadata struct fromDB array 1 struct IsCaseSensitive NO Name id TypeName int identity 2 struct IsCaseSensitive NO Name mi TypeName nvarchar fromJson array 1 struct IsCaseSensitive NO Name ID 2 struct IsCaseSensitive NO Name MI

Note that the statement "SerializeJSON considers datatypes defined in the database for serialization" is ballocks. No it doesn't.

I wish Adobe had actually implemented this sensibly, and actually stored the datatypes along with the other metadata in a JSONified query, eg:

{
    "COLUMNS":["ID","MI"],
    "COLUMNTYPES":["integer","nvarchar"],
    "DATA":[
        [18,"Rahina"],[19,"Ratu"],[20,"Raapa"],[21,"Rapare"],[22,"Ramere"],[23,"Rahoroi"],[24,"Ratapu"]
    ]
}

But, no, as with everything else they have done with JSON: they've done a half-arsed job of it.

I forgot to raise a ticket for this last time, but I will do so shortly and cross-ref it back here (done: 3712823).

---

In other news, I kinda hoped the this.serialization.preserveCaseForStructKey (see "ColdFusion 11: preserveCaseForStructKey" for commentary on that one) setting might have worked for query columns here too. But... no. I think it should. This perhaps suggests that "preserveCaseForStructKey" needs a rename, too: there are more situations in which case ought to be preserved than simply structs. So let's not tie ourselves down here. I'll raise a ticket for this too (3712824).

Righto.

--
Adam