如何在标准SQL中拆分DuckDB的STRUCT列生成独立字段？

针对你遇到的场景（同一JSONL文件导入的表中STRUCT结构一致，不同文件可能不同），下面提供两种兼容标准SQL的DuckDB实现方案：

方案一：创建新表快速拆分（推荐） #

这种方法利用DuckDB的EXCLUDE语法快速排除原STRUCT字段，同时一键展开其所有子字段，无需手动指定每个字段：

-- 创建包含拆分后字段的新表
CREATE TABLE your_new_table AS
SELECT 
  * EXCLUDE (meta),  -- 保留原表除meta外的所有字段
  meta.*             -- 将meta的所有子字段展开为独立列
FROM your_table;

-- 替换原表（可选，根据需求调整）
DROP TABLE your_table;
ALTER TABLE your_new_table RENAME TO your_table;

注意：如果原表有索引、约束或分区配置，需要在新表中重新添加这些属性。

方案二：修改原表结构（保留原表属性） #

如果需要保留原表的索引、约束等配置，可以通过分步修改表结构来实现：

1. 添加新列：根据STRUCT的子字段类型，为每个子字段创建对应列

   -- 示例：假设meta包含id、name、create_time三个子字段
   ALTER TABLE your_table
   ADD COLUMN meta_id INT,
   ADD COLUMN meta_name VARCHAR,
   ADD COLUMN meta_create_time TIMESTAMP;
   

2. 填充新列数据：将STRUCT子字段的值写入对应新列

   UPDATE your_table
   SET meta_id = meta.id,
       meta_name = meta.name,
       meta_create_time = meta.create_time;
   

3. 删除原STRUCT字段

   ALTER TABLE your_table DROP COLUMN meta;
   

动态处理不同STRUCT结构 #

如果需要批量处理不同STRUCT结构的表，可以通过查询DuckDB系统表自动生成SQL语句：

-- 生成添加新列的语句
SELECT 
  'ALTER TABLE your_table ADD COLUMN meta_' || field_name || ' ' || field_type || ';' AS alter_sql
FROM duckdb_columns
WHERE table_name = 'your_table' 
  AND column_name = 'meta'
  AND field_name IS NOT NULL;

-- 生成更新数据的语句
SELECT 
  'UPDATE your_table SET meta_' || field_name || ' = meta.' || field_name || ';' AS update_sql
FROM duckdb_columns
WHERE table_name = 'your_table' 
  AND column_name = 'meta'
  AND field_name IS NOT NULL;

执行上述查询生成的SQL语句后，再执行ALTER TABLE your_table DROP COLUMN meta;即可完成拆分。

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