如何使用UNNEST将二维数组展开为对应列数的数据表

你给出的SQL语句无法直接实现二维数组转两列表的效果。
UNNEST的核心作用是把数组的每个外层元素拆成独立行，对二维数组来说，拆分后每行的内容是一个内层数组，属于单个数组类型字段，不会自动把内层数组的元素映射为多个独立列，因此直接写AS tbl (num,str)声明列名的写法会触发类型不匹配错误。

固定两列场景的正确写法 #

你示例中内层数组长度固定为2、要转成num、str两列的需求，只需要在UNNEST拆分外层数组后，通过数组下标手动提取内层元素对应到目标列即可，参考BigQuery语法的实现（其他支持UNNEST的引擎注意调整下标规则，部分引擎数组下标从1开始）：

SELECT
  inner_arr[OFFSET(0)] AS num,
  inner_arr[OFFSET(1)] AS str
FROM UNNEST([[1,'a'],[2,'b'], [3,'c']]) AS inner_arr

执行后返回的结果完全符合预期：

通用二维数组转n列的处理规则 #

对于结构为arr[rows][cols]的二维数组，要展开为n列（n为内层数组长度）的表，分两种场景处理：

• 若n是提前已知的固定值：不管n是多少，都可以沿用上述按下标取值的逻辑，逐个为每个下标的元素定义对应列即可，和两列场景的逻辑没有区别。
• 若n是动态变化、执行SQL前无法确定的值：无法通过静态SQL直接实现。SQL的返回列结构必须在查询执行前完成解析确定，不支持根据运行时读取到的数组长度动态生成列。这类场景可以选择两种方案：
  • 提前统计内层数组的最大可能长度，手动写全所有下标的取值字段，没有对应元素的位置会自动返回NULL
  • 使用动态SQL，先扫描数组获取内层数组的实际长度，拼接好包含所有下标取数字段的SELECT语句后再执行

注意：所有主流SQL引擎的UNNEST语法都不支持直接声明多个列名、自动把内层数组元素映射为对应列的能力，不要尝试这类写法。

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