Repository: tobymao/sqlglot

Issue:
Conversion from Snowflake Object to Spark StructType does not produce syntactically correct result 
Ran the issue using main `sqlglot-19.0.4.dev18`

Read: `snowflake`
Write: `spark`

We are trying to convert Snowflake SQL code that creates an `OBJECT` using `{foo: bar}` syntax. In Spark, we would like to create the corresponding data type, a `StructType`. The transpiled Spark code is not syntactically correct and throws an error `AnalysisException: [INVALID_EXTRACT_BASE_FIELD_TYPE] Can't extract a value from "Manitoba". Need a complex type [STRUCT, ARRAY, MAP] but got "STRING".` 

This is presumably because the transpiled code is trying to make use the the `foo[bar]` method in SparkSQL to extract struct fields. However, this syntax doesn't work when _creating_ a struct. 

Code snippet: 

```
from pyspark.sql.session import SparkSession
from sqlglot import transpile

spark = SparkSession.builder.getOrCreate()
query = "SELECT { 'Manitoba': 'Winnipeg', 'foo': 'bar' } AS province_capital;"

transpiled_query = transpile(query, read='snowflake', write='spark', pretty=True, error_level=None)[0]

spark.sql(transpiled_query)
```

Produces the following string **(INCORRECT RESULT)**

```
SELECT
  STRUCT('Manitoba'['Winnipeg'], 'foo'['bar']) AS province_capital
```

Properly converted code in Spark SQL: 

```
SELECT STRUCT('Winnipeg' AS Manitoba, 'bar' AS foo) AS province_capital
```

Images with results 



Snowflake documentation for creating semi-structured data types using `{foo: bar}` syntax. 
https://docs.snowflake.com/en/sql-reference/data-types-semistructured

Spark SQL `struct` function 
https://spark.apache.org/docs/3.5.0/api/sql/#struct


Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index af557b5c..3433cdf2 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -84,7 +84,8 @@ def _parse_date_diff(args: t.List) -> exp.Expression:
 
 def _struct_sql(self: DuckDB.Generator, expression: exp.Struct) -> str:
     args = [
-        f"'{e.name or e.this.name}': {self.sql(e, 'expression')}" for e in expression.expressions
+        f"'{e.name or e.this.name}': {self.sql(e.expressions[0]) if isinstance(e, exp.Bracket) else self.sql(e, 'expression')}"
+        for e in expression.expressions
     ]
     return f"{{{', '.join(args)}}}"
 
diff --git a/sqlglot/dialects/presto.py b/sqlglot/dialects/presto.py
index f0aa1dc8..554c7c11 100644
--- a/sqlglot/dialects/presto.py
+++ b/sqlglot/dialects/presto.py
@@ -375,8 +375,6 @@ class Presto(Dialect):
             exp.Xor: bool_xor_sql,
         }
 
-        KEY_VALUE_DEFINITONS = (exp.EQ, exp.PropertyEQ, exp.Slice)
-
         def struct_sql(self, expression: exp.Struct) -> str:
             if any(isinstance(arg, self.KEY_VALUE_DEFINITONS) for arg in expression.expressions):
                 self.unsupported("Struct with key-value definitions is unsupported.")
diff --git a/sqlglot/dialects/spark2.py b/sqlglot/dialects/spark2.py
index da84bd86..38a06567 100644
--- a/sqlglot/dialects/spark2.py
+++ b/sqlglot/dialects/spark2.py
@@ -224,6 +224,19 @@ class Spark2(Hive):
         WRAP_DERIVED_VALUES = False
         CREATE_FUNCTION_RETURN_AS = False
 
+        def struct_sql(self, expression: exp.Struct) -> str:
+            args = []
+            for arg in expression.expressions:
+                if isinstance(arg, self.KEY_VALUE_DEFINITONS):
+                    if isinstance(arg, exp.Bracket):
+                        args.append(exp.alias_(arg.this, arg.expressions[0].name))
+                    else:
+                        args.append(exp.alias_(arg.expression, arg.this.name))
+                else:
+                    args.append(arg)
+
+            return self.func("STRUCT", *args)
+
         def temporary_storage_provider(self, expression: exp.Create) -> exp.Create:
             # spark2, spark, Databricks require a storage provider for temporary tables
             provider = exp.FileFormatProperty(this=exp.Literal.string("parquet"))
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index b89c30ad..f4193cda 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -378,6 +378,8 @@ class Generator:
         exp.Paren,
     )
 
+    KEY_VALUE_DEFINITONS = (exp.Bracket, exp.EQ, exp.PropertyEQ, exp.Slice)
+
     SENTINEL_LINE_BREAK = "__SQLGLOT__LB__"
 
     # Autofilled


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index a02b5a35..c78d3efd 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -356,14 +356,14 @@ class TestDuckDB(Validator):
             "STRUCT_PACK(x := 1, y := '2')",
             write={
                 "duckdb": "{'x': 1, 'y': '2'}",
-                "spark": "STRUCT(x = 1, y = '2')",
+                "spark": "STRUCT(1 AS x, '2' AS y)",
             },
         )
         self.validate_all(
             "STRUCT_PACK(key1 := 'value1', key2 := 42)",
             write={
                 "duckdb": "{'key1': 'value1', 'key2': 42}",
-                "spark": "STRUCT(key1 = 'value1', key2 = 42)",
+                "spark": "STRUCT('value1' AS key1, 42 AS key2)",
             },
         )
         self.validate_all(
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index 7452a8c9..edbdd005 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -87,6 +87,14 @@ class TestSnowflake(Validator):
         self.validate_all("CAST(x AS CHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
         self.validate_all("CAST(x AS CHARACTER VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
         self.validate_all("CAST(x AS NCHAR VARYING)", write={"snowflake": "CAST(x AS VARCHAR)"})
+        self.validate_all(
+            "SELECT { 'Manitoba': 'Winnipeg', 'foo': 'bar' } AS province_capital",
+            write={
+                "duckdb": "SELECT {'Manitoba': 'Winnipeg', 'foo': 'bar'} AS province_capital",
+                "snowflake": "SELECT OBJECT_CONSTRUCT('Manitoba', 'Winnipeg', 'foo', 'bar') AS province_capital",
+                "spark": "SELECT STRUCT('Manitoba' AS Winnipeg, 'foo' AS bar) AS province_capital",
+            },
+        )
         self.validate_all(
             "SELECT COLLATE('B', 'und:ci')",
             write={