嵌套JSON导入DuckDB报错：Table 'Level2'无'Field1'列求助

场景说明 #

待导入的嵌套JSON内容如下：

{
   "MainLevel":[
      {
         "More":{
         }
      },
      {
         "More":{
            "Level2":[
               {
                  "Field1":"A"
               }
            ]
         }
      }
   ]
}

使用以下SQL执行导入时触发报错：

CREATE TABLE duckdbtest1.main.nested_JSON AS
SELECT 
    Level2.Field1,
FROM 
    (SELECT unnest(MainLevel) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999))
     as MainLevel,
    unnest(MainLevel.More.Level2) as Level2;

报错信息 #

SQL Error: java.sql.SQLException: Binder Error: Table "Level2" does not have a column named "Field1"

LINE 3: Level2.Field1,

推测是第一个More节点缺失Level2字段导致的问题，以下是测试过程及结果：

测试过程及结果 #

测试1：查询所有字段 #

SELECT 
    *
FROM 
    (SELECT unnest(MainLevel) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999))
     as MainLevel,
    unnest(MainLevel.More.Level2) as Level2;

返回结果：

测试2：仅查询MainLevel字段 #

SELECT 
    Mainlevel
FROM 
    (SELECT unnest(MainLevel) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999))
     as MainLevel,
    unnest(MainLevel.More.Level2) as Level2;

返回结果：

测试3：不嵌套unnest Level2 #

SELECT 
    Mainlevel
FROM 
    (SELECT unnest(MainLevel) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999))
     as MainLevel;

返回结果：

测试4：使用递归unnest #

SELECT 
    Mainlevel
FROM 
    (SELECT unnest(MainLevel, recursive := true) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999))
     as MainLevel;

返回结果：

解决方法 #

问题根源是当MainLevel.More.Level2为null时，unnest无法识别Field1的字段结构，导致绑定失败。以下是三种有效解决方式：

方法1：LEFT JOIN UNNEST + TRY_CAST #

通过左连接保留所有记录，同时用TRY_CAST处理字段不存在的情况：

CREATE TABLE duckdbtest1.main.nested_JSON AS
SELECT 
    TRY_CAST(Level2.Field1 AS VARCHAR) AS Field1
FROM 
    (SELECT unnest(MainLevel) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999)) AS MainLevel
LEFT JOIN UNNEST(MainLevel.More.Level2) AS Level2 ON TRUE;

TRY_CAST会在字段不存在时返回null，避免绑定报错。

方法2：提前过滤Level2为null的记录 #

如果不需要保留Level2为null的记录，可先过滤再执行unnest：

CREATE TABLE duckdbtest1.main.nested_JSON AS
SELECT 
    Level2.Field1
FROM 
    (SELECT unnest(MainLevel) as MainLevel
     FROM read_JSON_auto('C:\\jsonfiles\\*.json', maximum_object_size = 999999999)
     WHERE MainLevel.More.Level2 IS NOT NULL) AS MainLevel,
    unnest(MainLevel.More.Level2) AS Level2;

方法3：利用read_json_auto的扁平化参数 #

直接使用read_json_auto的内置参数扁平化嵌套结构，简化操作：

CREATE TABLE duckdbtest1.main.nested_JSON AS
SELECT 
    Field1
FROM read_JSON_auto(
    'C:\\jsonfiles\\*.json', 
    maximum_object_size = 999999999,
    flatten = true,
    recursive = true
)
WHERE Field1 IS NOT NULL;

内容的提问来源于stack exchange，提问作者Confused-Donut