如何在未知结构下将Variant类型列扁平化为多列？

问题背景 #

我有一个Variant类型的列，但预先不清楚其内部结构，希望将该列扁平化为多列。例如原表结构如下：

+------------------------------------+
|                data                |
+------------------------------------+
| {a:1, b:2, c:{xx:"yy"}}            |
| {a:11, b:33, c:{zz:"qq"}, d="aaa"} |
+------------------------------------+

期望得到如下结构的表：

+--------+--------+------------+-----------+
| a(int) | b(int) | c(variant) | d(string) |
+--------+--------+------------+-----------+
|      1 |      2 | {xx:"yy"}  | null      |
|     11 |     33 | {zz:"qq"}  | "aaa"     |
+--------+--------+------------+-----------+

请问能否在不提前知晓data列结构的情况下实现该需求？

实现方案 #

可以实现，以Snowflake为例，核心思路是先自动提取Variant列中所有顶层键，再通过动态SQL生成扁平化查询，步骤如下：

1. 提取所有顶层键
   通过FLATTEN函数结合DISTINCT获取data列里所有出现过的顶层键：

SELECT DISTINCT f.key
FROM your_table,
LATERAL FLATTEN(input => data, mode => 'PATH') f;

2. 生成动态扁平化SQL
   用上面得到的键列表，拼接出查询每个键的SQL语句，自动处理缺失字段为NULL：

DECLARE
  cols STRING;
BEGIN
  SELECT LISTAGG('data:"' || key || '" AS "' || key || '"', ', ')
  INTO cols
  FROM (SELECT DISTINCT f.key FROM your_table, LATERAL FLATTEN(input => data, mode => 'PATH') f);
  
  EXECUTE IMMEDIATE 'SELECT ' || cols || ' FROM your_table';
END;

这段代码会自动生成包含所有顶层键的查询，缺失的字段会自动填充为NULL，完全匹配你期望的结果。

3. 嵌套结构处理（可选）
   如果需要进一步扁平化嵌套的Variant（比如例子中的c字段），可以递归提取嵌套键；若只需保留嵌套结构为Variant类型，上述方案即可满足需求。

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