如何提取SQL中ROW()类型列包含的所有字段名

核心原因说明 #

你之前用UNNEST报错是因为UNNEST仅支持拆解数组（ARRAY）类型，ROW是结构化嵌套类型，不支持直接用UNNEST处理。 #

方法1：通过元数据表直接查询（优先推荐，无需扫描业务表数据） #

绝大多数支持ROW类型的SQL引擎（如BigQuery、Spark SQL、Presto等）都提供了专门的元数据表存储嵌套字段的结构信息，以BigQuery为例，查询COLUMN_FIELD_PATHS即可直接拿到结果：

SELECT field_path
FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS`
WHERE 
  table_name = 'tablename'
  AND column_name = 'column'
  AND field_path != 'column' -- 排除根字段本身，仅返回子字段名

执行后会直接返回单列的字段名列表：id、gameid、type、action、accountid，完全不需要手动解析类型字符串。 #

方法2：JSON序列化后提取（通用兼容方案，适配所有支持JSON函数的引擎） #

如果你使用的引擎没有对应的元数据表可查，可以先将ROW字段序列化为JSON，再提取JSON的键名：

-- BigQuery 写法示例
SELECT DISTINCT key
FROM `你的项目ID.你的数据集ID.tablename`,
UNNEST(JSON_EXTRACT_KEYS(TO_JSON_STRING(`column`))) AS key

只要表中ROW字段的结构是统一的，该方法可以稳定返回所有子字段名。 #

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