如何在DuckDB中扁平化嵌套JSON列（拆分嵌套字段为独立列）

方法1：手动提取指定字段 #

如果JSON结构固定且字段不多，直接使用箭头操作符->>（提取字符串值）或->（提取嵌套JSON/结构体）获取目标字段：

SELECT
  id,
  data->>'type' AS type,
  data->>'purpose' AS purpose,
  data->>'ts' AS ts,
  data->>'userId' AS userId,
  data->'context'->>'ip' AS "context.ip",
  data->'context'->'sth'->>'else' AS "context.sth.else"
FROM some_table;

方法2：自动推断结构并转为STRUCT后展开 #

如果JSON结构复杂、字段较多，可以先将JSON列转为STRUCT类型，再批量展开所有字段：

1. 先获取JSON的结构（取一条样本数据即可）：

SELECT json_structure(data) FROM some_table LIMIT 1;

执行后会得到类似如下的结构描述：

STRUCT(type VARCHAR, purpose VARCHAR, ts VARCHAR, userId VARCHAR, context STRUCT(ip VARCHAR, sth STRUCT("else" VARCHAR)))

2. 使用from_json将JSON列转为STRUCT，通过交叉连接展开所有字段：

SELECT
  id,
  flattened.*
FROM some_table,
  from_json(
    data,
    'STRUCT(type VARCHAR, purpose VARCHAR, ts VARCHAR, userId VARCHAR, context STRUCT(ip VARCHAR, sth STRUCT("else" VARCHAR)))'
  ) AS flattened;

方法3：使用json_normalize函数（DuckDB v0.8.0+） #

从DuckDB v0.8.0版本开始，官方提供了类似pandas.json_normalize的json_normalize函数，可自动识别嵌套结构并扁平化：

SELECT * FROM json_normalize(some_table, 'data');

该函数会自动将所有层级的嵌套字段展开为单独列，列名用.分隔嵌套路径，完全匹配你的需求。

关于unnest报错的说明 #

unnest仅支持处理数组、结构体或NULL类型，直接对JSON类型调用会触发绑定错误。需要先将JSON列转为STRUCT类型（如方法2所示），再通过交叉连接结构体的方式实现类似展开效果。

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