如何查询数组结构体类型属性以获取Size对应的values数组？

问题描述 #

我有一个数据类型为array<struct<id:string,name:string,values:array<string>>>的字段options，示例值如下：

[
    {id=gid://test/1234, name=Size, values=[L, M, S, XS]},
    {id=gid://test/12345, name=Color, values=[Black]}
]

我希望通过查询得到Size对应的完整值数组：[L, M, S, XS]。我尝试了以下SQL语句，但没有得到预期结果：

SELECT    * FROM     mytable
CROSS JOIN UNNEST(options) AS t (option_struct)
CROSS JOIN UNNEST(option_struct.values) AS size_value
WHERE     option_struct.name = 'Size'

请问该如何正确实现此查询？

解决方案 #

你当前的SQL用了两次UNNEST，第二次UNNEST(option_struct.values)把目标数组拆成了单个元素，所以得到的是每行一个尺寸值的结果，而非完整数组。只需调整逻辑，过滤出目标struct后直接提取其values字段即可，以下是两种通用实现方式：

方式1：基于UNNEST的基础写法

只展开外层的struct数组，过滤出name为Size的项后，直接返回它的values字段：

SELECT option_struct.values AS size_values
FROM mytable
CROSS JOIN UNNEST(options) AS option_struct
WHERE option_struct.name = 'Size'

方式2：数组函数简化写法（适用于支持数组操作的引擎）

如果你的SQL引擎支持数组过滤函数（比如BigQuery、Spark SQL），可以不用展开数组，直接在数组内定位目标项：

• BigQuery版本：

SELECT (
    SELECT values
    FROM UNNEST(options)
    WHERE name = 'Size'
) AS size_values
FROM mytable

• Spark SQL版本：

SELECT filter(options, x -> x.name = 'Size')[0].values AS size_values
FROM mytable

特殊情况处理：存在多个Size项

如果表中可能存在多个name为Size的struct，可以用ARRAY_AGG收集所有匹配的数组：

SELECT ARRAY_AGG(option_struct.values) AS all_size_values
FROM mytable
CROSS JOIN UNNEST(options) AS option_struct
WHERE option_struct.name = 'Size'

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