如何用Snowflake SQL/Python将VARIANT类型JSON值展开为独立列

直接选自动生成SQL的思路，flatten不适合这个场景，不用额外写Python脚本，纯SQL就能零硬写适配你上百列的需求，步骤如下：

为什么不推荐用flatten()做这个需求 #

• flatten()的核心作用是把JSON结构拆成多行数据，不是拆成多列。你要的结果是保留原表每一行的ID，把所有JSON里的value提取成同一行的独立列，用flatten之后还要做二次行转列（pivot），逻辑冗余，性能还比直接路径访问差很多。
• 不管是单层还是两层嵌套的JSON，根本不需要拆行，直接用半结构化类型的路径语法就能拿到值：比如property_address:value直接取单层列的value，properties:address.value直接取嵌套结构里address对应的value，完全没必要绕一圈拆行再聚合。

具体落地方法（1分钟搞定，适配任意数量VARIANT列） #

以下逻辑基于Snowflake语法（VARIANT是Snowflake的原生半结构化类型，如果你用的是其他支持JSON类型的数仓，对应改下路径访问符就行），不需要手动枚举任何列，直接查元数据自动生成你需要的建表SQL：

1. 先跑下面这段生成SQL的查询，把里面的表名、schema名替换成你自己的：

WITH generate_select_clause AS (
  -- 处理所有类似property_address、property_revenue的单层VARIANT列
  SELECT 
    LISTAGG(
      COLUMN_NAME || ':value AS ' || COLUMN_NAME, 
      ', '
    ) WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS col_segment
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'YOUR_SOURCE_TABLE'
    AND TABLE_SCHEMA = 'YOUR_SCHEMA'
    AND COLUMN_NAME NOT IN ('ID', 'PROPERTIES') -- 排除主键和嵌套结构列
    AND DATA_TYPE = 'VARIANT'

  UNION ALL

  -- 处理properties这类嵌套结构的VARIANT列，自动解析所有一级key
  SELECT
    LISTAGG(
      'PROPERTIES:' || f.key || '.value AS prop_' || f.key,
      ', '
    ) AS col_segment
  FROM (
    SELECT DISTINCT key
    FROM YOUR_SOURCE_TABLE,
    LATERAL FLATTEN(input => PROPERTIES)
    LIMIT 2000 -- 避免脏数据生成冗余列，可按需调整
  ) f
)
-- 拼接最终可直接执行的建表语句
SELECT 
  'CREATE OR REPLACE TABLE YOUR_TARGET_TABLE AS SELECT ID, ' || 
  LISTAGG(col_segment, ', ') || 
  ' FROM YOUR_SOURCE_TABLE;' AS final_sql
FROM generate_select_clause;

2. 上面的查询会输出1条完整的SQL语句，复制出来直接执行，就能得到你要的新表：所有列都是提取好的纯value内容，单层VARIANT列直接用原列名，properties里的嵌套字段会自动生成prop_xxx格式的独立列，上百列也不会漏。

方案优势 #

• 性能最好：所有字段都是直接通过JSON路径访问，没有拆行、聚合的额外开销，全表扫描速度比flatten+pivot的方案快数倍。
• 维护成本极低：后续新增VARIANT列、properties里加新业务字段，只要重新跑一遍生成语句的查询就行，不用手动改代码。
• 如果你需要把这个逻辑嵌到自动化数据管道里，再用Python实现和上面完全一致的逻辑就行：查元数据拿列清单、采样拿嵌套JSON的key、拼接SQL执行，比纯SQL多一层数据库连接的步骤，单次做表转换完全没必要上Python。

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