ColdFusion多维数组转Query对象时键名点号报错解决

问题场景 #

调用API返回的JSON数据反序列化后，结构体键名包含点号。ColdFusion Query对象列名仅支持以字母开头，内容仅可包含字母、数字、下划线，不允许出现点号，需要将键名中的点号替换为下划线后写入Query。原有代码执行到addRow逻辑时抛出列名非法错误。

复现代码 #

测试数据源 #

<cfset varResults = '[{"roW_NUMBER":1,"membership.Individual.LocalID":999,"membership.Individual.FirstName":"AA","membership.Individual.LastName":"AA","membership.Individual.Company":null,"membership.Individual.Work_Address_Line1":"aaaa","membership.Individual.Work_Address_Line2":null,"membership.Individual.Work_Address_City":"Alexandria","membership.Individual.Work_Address_State":"VA","membership.Individual.Work_Address_PostalCode":"9999-2518","membership.Individual.Work_Address_CountryName":"UNITED STATES","membership.Individual.EmailAddress":"AA.AA@gmail.com","membership.Type.Name":"Regular","membership.JoinDate":"2009-05-01T00:00:00","membership.ExpirationDate":"2023-04-30T00:00:00","membership.Individual.FellowElectDate__c":null,"chapter.Name":"DDDD","id":"0e04bc98-0074-cc58-bf6a-0b438d800e7d"},{"roW_NUMBER":2,"membership.Individual.LocalID":9998,"membership.Individual.FirstName":"AA","membership.Individual.LastName":"AA","membership.Individual.Company":null,"membership.Individual.Work_Address_Line1":"ASASASAAASAS","membership.Individual.Work_Address_Line2":null,"membership.Individual.Work_Address_City":"Washington","membership.Individual.Work_Address_State":"DC","membership.Individual.Work_Address_PostalCode":"9999","membership.Individual.Work_Address_CountryName":"UNITED STATES","membership.Individual.EmailAddress":"ASASASA@gmail.com","membership.Type.Name":"Regular","membership.JoinDate":"2020-01-01T00:00:00","membership.ExpirationDate":"2022-12-31T00:00:00","membership.Individual.FellowElectDate__c":null,"chapter.Name":"DDDD","id":"0e04bc98-0074-c1ae-f171-0b438d800e7c"}]'>

使用前需调用deserializeJSON(varResults)将JSON字符串转为CF结构体数组。

原有错误实现 #

<cfscript>
    function arrayToQuery(varChapterRoster) {
        return varChapterRoster.reduce(function(accumulator, element) {
            element.each(function(key) {
                if (!accumulator.keyExists(replace(key,".","_","all"))) {
                    accumulator.addColumn(replace(key,".","_","all"), []);
                }
            });
            accumulator.addRow(element);
            return accumulator;
        }, QueryNew(""));
    }
    varChapterRoster = deserializeJSON(varResults);
    writeDump(arrayToQuery(varChapterRoster));
</cfscript>

报错信息 #

Column names must be valid variable names. They must start with a letter and can only include letters, numbers, and underscores.（列名必须为合法变量名，以字母开头，仅可包含字母、数字与下划线）

根因分析 #

代码仅在keyExists校验、addColumn新增列两个环节对键名做了点号替换，传入addRow方法的原始结构体element仍保留带点号的原始键名，Query写入行数据时校验列名合法性，识别到非法字符直接抛出错误。

修复方案 #

遍历单条数据时同步生成键名替换后的合法行结构体，所有列操作、行写入均使用替换后的合法键名：

<cfscript>
    function arrayToQuery(varChapterRoster) {
        return varChapterRoster.reduce(function(accumulator, element) {
            var cleanedRow = {};
            // 统一处理键名替换，生成合法行数据
            element.each(function(key, value) {
                var safeKey = replace(key, ".", "_", "all");
                // 列不存在则先新增
                if (!accumulator.keyExists(safeKey)) {
                    accumulator.addColumn(safeKey, []);
                }
                cleanedRow[safeKey] = value;
            });
            // 传入键名合法的结构体写入行
            accumulator.addRow(cleanedRow);
            return accumulator;
        }, QueryNew(""));
    }

    varChapterRoster = deserializeJSON(varResults);
    writeDump(arrayToQuery(varChapterRoster));
</cfscript>

该实现统一了键名处理逻辑，避免重复调用替换方法，从根源上规避了非法列名问题，可兼容任意包含点号的键名场景。

内容的提问来源于stack exchange，提问作者Tim Gill