GitHub user ryux1 added a comment to the discussion: Query data with "WHERE"
clause for nested objects
You do not need a join for this shape. In DataFusion, `unnest(...)` is used as
a set-returning expression in the `SELECT` list. Unnest the outer list first,
access fields on the resulting struct, then unnest its nested `f` list:
```sql
WITH a_rows AS (
SELECT unnest(items) AS a
FROM test_data
),
b_rows AS (
SELECT
a['c'] AS c,
a['d'] AS d,
unnest(a['f']) AS b
FROM a_rows
)
SELECT c, d, b['e'] AS e
FROM b_rows
WHERE b['e'] = 'target';
```
For the data in the question, that returns:
```text
1 first target
```
The important distinction is that `a` and `b` above are struct-valued columns,
not table aliases. Bracket field access (`a['f']`, `b['e']`) makes that
explicit and avoids the ambiguity in `CROSS JOIN UNNEST(items) AS a`.
I verified this query against current DataFusion `main` with the same
`List<Struct<..., f: List<Struct<...>>>>` shape; the focused SQLLogicTest
passes.
GitHub link:
https://github.com/apache/datafusion/discussions/17714#discussioncomment-18322226
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
