DuckDB嵌套对象星号表达式使用问题及数据扁平化需求

在DuckDB中，顶层结构体可通过如下方式正常展开：

SELECT
    parent_id,
    top_level_struct.*
FROM 
    arrow_table AS root

但尝试展开经UNNEST处理后的嵌套结构体时会触发语法错误，错误示例代码如下：

SELECT
    parent_id,
    my_list_unnested.my_list.* EXCLUDE (list_struct),  -- 无法运行
    my_list_unnested.my_list.list_struct.* -- 无法运行
FROM 
    arrow_table AS root,
    UNNEST(root.my_list) AS my_list_unnested        

可复现完整代码 #

import duckdb
import pyarrow as pa

test_records = [
    {
        "parent_id": 123,
        "top_level_struct": {
            "top_level_hello": "World",
            "top_level_foo": "bar",
            "top_level_baz": "qux"
        },
        "my_list": [
            {
                "item_id": 123,
                "list_struct": {
                    "list_hello": "World",
                    "list_foo": "bar",
                    "list_baz": "qux"
                }
            }
        ]
    }
]

arrow_table = pa.Table.from_pylist(test_records)

# 可正常运行的代码
WORKING_SQL = """
    SELECT
        parent_id,
        top_level_struct.*
    FROM 
        arrow_table AS root
"""
df = duckdb.sql(WORKING_SQL)

# 无法运行的代码
NOT_WORKING_SQL = """
    SELECT
        parent_id,
        my_list_unnested.my_list.* EXCLUDE (list_struct),  -- 无法运行
        my_list_unnested.my_list.list_struct.* -- 无法运行
    FROM 
        arrow_table AS root,
        UNNEST(root.my_list) AS my_list_unnested        
"""

df = duckdb.sql(NOT_WORKING_SQL)

报错信息 #

duckdb.duckdb.ParserException: Parser Error: syntax error at or near "*"

期望扁平化结构 #

需将数据处理为如下结构：

desired_structure = [
    {
        "parent_id": 123,
        "top_level_hello": "World",
        "top_level_foo": "bar",
        "top_level_baz": "qux",
        "item_id": 123,
        "list_hello": "World",
        "list_foo": "bar",
        "list_baz": "qux"
    }
]

解决方法 #

问题出在UNNEST后的别名使用逻辑：UNNEST(root.my_list)得到的my_list_unnested已经是数组中的单个结构体元素，无需再通过.my_list访问字段。正确的SQL写法如下：

SELECT
    parent_id,
    top_level_struct.*,
    my_list_unnested.* EXCLUDE (list_struct),
    my_list_unnested.list_struct.*
FROM 
    arrow_table AS root,
    UNNEST(root.my_list) AS my_list_unnested        

将上述SQL替换原有的NOT_WORKING_SQL即可得到期望的扁平化结果。

环境版本：

• DuckDB: 0.9.2
• Python: 3.10.12
• Ubuntu 22.04

内容的提问来源于stack exchange，提问作者Roar S.