[SQL] DuckDB nested JSON load struggles

Trying to load a nested JSON file into Duckdb.
Here is the JSON contents:

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


This is the Duckdb SQL script I am using to load:

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;


The Error I get is:

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


I have done similar things in the past with no issues. The only thing I can think of is that the first "More" has no "Level2". Could that be causing an issue? Ideas of how to get this to work?

Thanks in advance.

Have tried various combinations of unnest and left joins on unnest with no success. I'm sure it simple but for the life of me I can't figure it out.

Continue reading...