Snowflake中如何获取Object类型对象的完整嵌套Schema结构

Snowflake原生没有直接输出全嵌套结构Schema的内置单函数：object_keys仅能返回对象第一层键名，typeof仅支持判断单个值的基础类型，要实现你需要的递归提取全层级键、类型、嵌套结构的能力，最简便的方式是创建递归JavaScript UDF，调用方式完全匹配你预期的写法。

推荐方案：递归JavaScript UDF（调用最简单，输出格式匹配需求） #

Snowflake的JavaScript语言UDF支持递归逻辑，可以直接遍历嵌套对象、数组结构，输出你需要的结构化Schema对象，创建语句如下：

CREATE OR REPLACE FUNCTION magic_object_schema_fn(input_obj VARIANT)
RETURNS VARIANT
LANGUAGE JAVASCRIPT
AS
$$
function parseNode(val) {
    if (val === null) return "null";
    const valType = typeof val;
    // 处理基础类型
    if (valType !== "object") {
        switch(valType) {
            case "string": return "string";
            case "number": return Number.isInteger(val) ? "int" : "float";
            case "boolean": return "boolean";
            default: return valType;
        }
    }
    // 处理数组类型
    if (Array.isArray(val)) {
        if (val.length === 0) return "array<empty>";
        // 取第一个非空元素解析schema，异构数组可按需调整为多类型联合
        for (const item of val) {
            if (item !== null) return `array<${parseNode(item)}>`;
        }
        return "array<null>";
    }
    // 处理嵌套对象，递归遍历所有键
    const schema = {};
    for (const key of Object.keys(val)) {
        schema[key] = parseNode(val[key]);
    }
    return schema;
}

if (INPUT_OBJ === null) return "null";
if (typeof INPUT_OBJ !== "object") return typeof INPUT_OBJ;
return parseNode(INPUT_OBJ);
$$;

创建完成后直接用你写的语句调用即可：

select magic_object_schema_fn(obj_column) from foo_table;

测试验证：

select magic_object_schema_fn(parse_json('{
    "a": "test",
    "b": 123,
    "c": {
         "d": 45.67
    }
}')) as schema_result;

返回结果和你给出的示例格式完全一致：

{
  "a": "string",
  "b": "int",
  "c": {
    "d": "float"
  }
}

这个UDF同时支持数组嵌套、多层对象嵌套的场景，比如数组内存储对象会返回array<{id:int, name:string}>这类结构，数字类型默认区分int和float，有特殊类型判断需求直接修改JS里的类型判断分支即可。

备选方案：纯SQL递归CTE（无UDF创建权限时使用） #

如果因为账号权限限制无法创建JavaScript UDF，可以用递归CTE配合lateral flatten、object_keys实现递归遍历，缺点是默认输出扁平化的路径-类型列表，无法直接生成嵌套对象结构，需要二次聚合加工：

with recursive traverse_schema as (
    -- 锚点：解析第一层键
    select
        key as node_path,
        this as node_val,
        typeof(this) as node_type
    from foo_table,
    lateral flatten(input => object_keys(obj_column)) k,
    lateral flatten(input => obj_column:k.value)
    union all
    -- 递归层：向下遍历嵌套对象
    select
        concat(ts.node_path, '.', child_k.value) as node_path,
        child_v.this as node_val,
        typeof(child_v.this) as node_type
    from traverse_schema ts,
    lateral flatten(input => case when ts.node_type = 'OBJECT' then object_keys(ts.node_val) end) child_k,
    lateral flatten(input => ts.node_val:child_k.value) child_v
    where ts.node_type = 'OBJECT'
)
select node_path, node_type from traverse_schema;

这个查询输出的是a|string、b|int、c.d|float格式的扁平化路径结果，适合需要全量键路径清单的场景，要转成嵌套对象结构需要额外用object_construct做多层聚合，实现复杂度远高于JS UDF方案。

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