Return to Answer

An unproblematic way to express the query would be:

SELECT Level2.Field1
FROM (SELECT unnest(MainLevel.More.Level2) as Level2
 FROM (SELECT unnest(MainLevel) as MainLevel,
 FROM read_JSON_auto('input.json') ));

The hierarchy of SELECT/FROM statements allows DuckDB to work its unnest magic in a straightforward way. In particular, since unnest(NULL) has the effect of zapping a row, by the time the SELECT Level2.Field1 statement starts executing, the row that would otherwise have caused it to fail has been removed.

By contrast, the statement having the form:

FROM 
 (SELECT unnest(MainLevel) as MainLevel
 FROM read_JSON_auto('input.json') as MainLevel,
 unnest(MainLevel.More.Level2) as Level2

seems to be at best dubious because of the way SELECT unnest(), unnest() works.

An unproblematic way to express the query would be:

SELECT Level2.Field1
FROM (SELECT unnest(MainLevel.More.Level2) as Level2
 FROM (SELECT unnest(MainLevel) as MainLevel,
 FROM read_JSON_auto('input.json') ));

The hierarchy of SELECT/FROM statements allows DuckDB to work its unnest magic in a straightforward way. In particular, since unnest(NULL) has the effect of zapping a row, by the time the SELECT Level2.Field1 statement starts executing, the row that would otherwise have caused it to fail has been removed.

By contrast, the statement having the form:

FROM 
 (SELECT unnest(MainLevel) as MainLevel
 FROM read_JSON_auto('input.json') as MainLevel,
 unnest(MainLevel.More.Level2) as Level2

seems to be at best dubious: notice that in the following, the result does not include any column labeled Level2:

D select ({'a':1}) as MainLevel, unnest(MainLevel) as Level2;
┌───────────────────┬───────┐
│ MainLevel │ a │
│ struct(a integer) │ int32 │
├───────────────────┼───────┤
│ {'a': 1} │ 1 │
└───────────────────┴───────┘