Repository: tobymao/sqlglot

Issue:
Missing Struct field wrapped in UNNEST when transpiling to BigQuery dialect
**Before you file an issue**
I have a SQL that tries to unnest a list field of a struct. However, if the table alias of the unnest table factor is the same as the field name in the struct, it will be removed after transpiling to BigQuery dialect.

**Fully reproducible code snippet**
The following code can reproduce the issue:
```python
import sqlglot

# The `member` is a struct column of the `groups` table.
sql = """
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
"""
print("input: \n", sql)
result = sqlglot.transpile(sql, read=None, write="bigquery")[0]
print("output: \n", result)
```
The result is
```sql
input: 
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
output: 
SELECT * FROM `groups`, UNNEST(`groups`.`list`) AS `element`
```

However, the expected result is
```sql
SELECT * FROM `groups`, UNNEST(`groups`.`members`.`list`) AS `element`
```

Discussion:
@georgesittas: Hey @goldmedal, can you provide some more details on what you're trying to achieve? Looks like you're transpiling from another dialect to BigQuery, but it's not clear what that is.@goldmedal: > Hey [@goldmedal](https://github.com/goldmedal), can you provide some more details on what you're trying to achieve? Looks like you're transpiling from another dialect to BigQuery, but it's not clear what that is.

Thanks for replying @georgesittas 
I think it's a generic case (that's why I tried `None` as the input dialect). Anyway, let me use DuckDB as an example to explain clearly. The SQL is used to unnest the array in the struct. In DuckDB, the valid SQL is like:
```
create table "groups" (member struct(list int[]));
D select * from "groups", unnest("groups"."member"."list") as "members"("element");
┌────────────────────────┬─────────┐
│         member         │ element │
│ struct(list integer[]) │  int32  │
├────────────────────────┴─────────┤
│              0 rows              │
└──────────────────────────────────┘
```

However, if I tried to transpile this SQL to the BigQuery dialect:
```python
import sqlglot

# The `member` is a struct column of the `groups` table.
sql = """
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
"""
print("input: \n", sql)
result = sqlglot.transpile(sql, read="duckdb", write="bigquery")[0]
print("output: \n", result)
```
The result is
```
input: 
SELECT * FROM groups, UNNEST("groups"."members"."list") "members" ("element")
output: 
SELECT * FROM `groups`, UNNEST(`groups`.`list`) AS `element`
```
See the output, we can find that the `members` is missing. The expected result should be:
```SQL
SELECT * FROM `groups`, UNNEST(`groups`.`members`.`list`) AS `element`
```
Refer to [The doc of BigQuery unnest](https://cloud.google.com/bigquery/docs/arrays#flattening_arrays), the table alias is the column name of the unnest result. I guessed that there are some issues when removing the table name `"members"("element")`

Solution patch:
diff --git a/sqlglot/transforms.py b/sqlglot/transforms.py
index 84e4c45dc9..da462732bf 100644
--- a/sqlglot/transforms.py
+++ b/sqlglot/transforms.py
@@ -309,10 +309,9 @@ def unqualify_unnest(expression: exp.Expression) -> exp.Expression:
         }
         if unnest_aliases:
             for column in expression.find_all(exp.Column):
-                if column.table in unnest_aliases:
-                    column.set("table", None)
-                elif column.db in unnest_aliases:
-                    column.set("db", None)
+                leftmost_part = column.parts[0]
+                if leftmost_part.arg_key != "this" and leftmost_part.this in unnest_aliases:
+                    leftmost_part.pop()
 
     return expression
 


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index 377cfe81d1..c4c15e403f 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1684,6 +1684,39 @@ def test_bigquery(self):
             "EXPORT DATA WITH CONNECTION myproject.us.myconnection OPTIONS (URI='gs://path*.csv.gz', FORMAT='CSV') AS SELECT * FROM all_rows"
         )
 
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t1`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1") "t1" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t1, UNNEST(`t1`) AS `col`",
+                "redshift": 'SELECT * FROM t1, "t1" AS "col"',
+            },
+        )
+
+        self.validate_all(
+            "SELECT * FROM t, UNNEST(`t2`.`t3`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t, UNNEST("t1"."t2"."t3") "t1" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t, UNNEST(`t2`.`t3`) AS `col`",
+                "redshift": 'SELECT * FROM t, "t2"."t3" AS "col"',
+            },
+        )
+
+        self.validate_all(
+            "SELECT * FROM t1, UNNEST(`t1`.`t2`.`t3`.`t4`) AS `col`",
+            read={
+                "duckdb": 'SELECT * FROM t1, UNNEST("t1"."t2"."t3"."t4") "t3" ("col")',
+            },
+            write={
+                "bigquery": "SELECT * FROM t1, UNNEST(`t1`.`t2`.`t3`.`t4`) AS `col`",
+                "redshift": 'SELECT * FROM t1, "t1"."t2"."t3"."t4" AS "col"',
+            },
+        )
+
     def test_errors(self):
         with self.assertRaises(TokenError):
             transpile("'\\'", read="bigquery")