如何避免Snowflake JS UDF返回JSON时十进制值转为科学计数法

问题根因 #

你遇到的现象是Snowflake JavaScript UDF的固有特性导致的：JS UDF内部使用JavaScript标准Number类型处理所有浮点数值，JS内置的数值序列化规则默认会对绝对值≥1e6、或<1e-4的数值自动转为科学计数法，同时会自动截断16位以上的有效数字，这和Snowflake原生PARSE_JSON保留原始十进制格式的逻辑完全不同。

解决方案1：JavaScript UDF递归处理（适配轻量场景） #

如果你的数值精度不超过16位有效数字，可以直接修改JS UDF，增加递归遍历逻辑，对所有数值做格式转换后再返回：

CREATE OR REPLACE FUNCTION F_DECIMAL(INPUT VARIANT)
RETURNS VARIANT
LANGUAGE javascript
AS
$$
// 辅助函数：将数值转为无科学计数法的字符串
function numberToFixedStr(num) {
  const numStr = num.toString();
  if (!numStr.includes('e')) return numStr;
  const [significand, exponent] = numStr.split('e');
  const exp = parseInt(exponent);
  const [intPart, decimalPart = ''] = significand.split('.');
  if (exp >= 0) {
    const shift = exp - decimalPart.length;
    return intPart + decimalPart + '0'.repeat(Math.max(shift, 0)) + 
      (shift < 0 ? '.' + decimalPart.slice(shift) : '');
  } else {
    return '0.' + '0'.repeat(Math.abs(exp) - 1) + intPart.replace('-', '') + decimalPart;
  }
}

// 递归遍历处理所有节点
function process(node) {
  if (typeof node === 'number') {
    return parseFloat(numberToFixedStr(node));
  }
  if (Array.isArray(node)) return node.map(process);
  if (typeof node === 'object' && node !== null) {
    const res = {};
    for (const k in node) res[k] = process(node[k]);
    return res;
  }
  return node;
}

return process(INPUT);
$$

解决方案2：SQL递归UDF（适配高精度场景） #

如果需要保留超过16位的原始十进制精度，完全规避JS的数值精度缺陷，可以使用Snowflake SQL递归UDF实现：

CREATE OR REPLACE FUNCTION F_DECIMAL_SQL(INPUT VARIANT)
RETURNS VARIANT
LANGUAGE SQL
AS
$$
CASE
  -- 处理数值类型，TO_DECIMAL的精度、小数位可根据业务需求调整
  WHEN IS_NUMBER(INPUT) THEN TO_VARIANT(TO_DECIMAL(INPUT, 38, 18))
  -- 处理数组
  WHEN IS_ARRAY(INPUT) THEN (
    SELECT ARRAY_AGG(F_DECIMAL_SQL(VALUE)) 
    FROM TABLE(FLATTEN(INPUT => INPUT))
  )
  -- 处理对象
  WHEN IS_OBJECT(INPUT) THEN (
    SELECT OBJECT_AGG(KEY, F_DECIMAL_SQL(VALUE))
    FROM TABLE(FLATTEN(INPUT => INPUT))
  )
  -- 其他类型直接返回
  ELSE INPUT
END
$$;

该方案不需要依赖JavaScript的数值处理逻辑，完全使用Snowflake原生的数值格式规则，返回结果和原生PARSE_JSON的格式完全一致，使用时替换原查询语句中的UDF名称即可。

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