Repository: tobymao/sqlglot

Issue:
Presto to Spark query translation incorrect
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- Check if the issue still exists on main

**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.

Presto query
```
SELECT
  JSON_EXTRACT_SCALAR(
    TRY(
      FILTER(
        CAST(JSON_EXTRACT(context, '$.active_sessions') AS ARRAY(MAP(VARCHAR, VARCHAR))),
        x -> x['event_data_schema'] = 'PresentationSession'
      )[1]['event_data']
    ),
    '$.thread_id'
  )
```

SQL Glot translated it to following incorrect Spark sql
```
SELECT
  GET_JSON_OBJECT(
    TRY(
      FILTER(
        CAST(GET_JSON_OBJECT(context, '$.active_sessions') AS ARRAY<MAP<STRING, STRING>>),
        x -> x['event_data_schema'] = 'PresentationSession'
      )[0]['event_data']
    ),
    '$.thread_id'
  )
```

1. TRY does not work in spark.
2. CAST output of GET_JSON_OBJECT to ARRAY does not work in Spark.

Correct Spark sql is as follows
```
SELECT
  GET_JSON_OBJECT(
      FILTER(
        FROM_JSON(GET_JSON_OBJECT(context, '$.active_sessions'), 'ARRAY<MAP<STRING, STRING>>'),
        x -> x['event_data_schema'] = 'PresentationSession'
      )[0]['event_data']
    ,
    '$.thread_id'
  )
```


**Official Documentation**
Please include links to official SQL documentation related to your issue.


Discussion:

Patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index c95305a9..2e04aca3 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -161,6 +161,9 @@ class _Dialect(type):
         if enum not in ("", "bigquery"):
             klass.generator_class.SELECT_KINDS = ()
 
+        if enum not in ("", "athena", "presto", "trino"):
+            klass.generator_class.TRY_SUPPORTED = False
+
         if enum not in ("", "databricks", "hive", "spark", "spark2"):
             modifier_transforms = klass.generator_class.AFTER_HAVING_MODIFIER_TRANSFORMS.copy()
             for modifier in ("cluster", "distribute", "sort"):
diff --git a/sqlglot/dialects/spark2.py b/sqlglot/dialects/spark2.py
index 5264f39d..9030d32e 100644
--- a/sqlglot/dialects/spark2.py
+++ b/sqlglot/dialects/spark2.py
@@ -259,12 +259,15 @@ class Spark2(Hive):
             return Generator.struct_sql(self, expression)
 
         def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
-            if is_parse_json(expression.this):
+            arg = expression.this
+            is_json_extract = isinstance(arg, (exp.JSONExtract, exp.JSONExtractScalar))
+
+            if is_parse_json(arg) or is_json_extract:
                 schema = f"'{self.sql(expression, 'to')}'"
-                return self.func("FROM_JSON", expression.this.this, schema)
+                return self.func("FROM_JSON", arg if is_json_extract else arg.this, schema)
 
             if is_parse_json(expression):
-                return self.func("TO_JSON", expression.this)
+                return self.func("TO_JSON", arg)
 
             return super(Hive.Generator, self).cast_sql(expression, safe_prefix=safe_prefix)
 
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 9909e7a5..862b3f7a 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4872,6 +4872,10 @@ class TryCast(Cast):
     pass
 
 
+class Try(Func):
+    pass
+
+
 class CastToStrType(Func):
     arg_types = {"this": True, "to": True}
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index f4e3c39f..e82ec346 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -348,6 +348,9 @@ class Generator(metaclass=_Generator):
     # Whether COPY statement has INTO keyword
     COPY_HAS_INTO_KEYWORD = True
 
+    # Whether the conditional TRY(expression) function is supported
+    TRY_SUPPORTED = True
+
     TYPE_MAPPING = {
         exp.DataType.Type.NCHAR: "CHAR",
         exp.DataType.Type.NVARCHAR: "VARCHAR",
@@ -3167,6 +3170,13 @@ class Generator(metaclass=_Generator):
     def trycast_sql(self, expression: exp.TryCast) -> str:
         return self.cast_sql(expression, safe_prefix="TRY_")
 
+    def try_sql(self, expression: exp.Try) -> str:
+        if not self.TRY_SUPPORTED:
+            self.unsupported("Unsupported TRY function")
+            return self.sql(expression, "this")
+
+        return self.func("TRY", expression.this)
+
     def log_sql(self, expression: exp.Log) -> str:
         this = expression.this
         expr = expression.expression


Test patch:
diff --git a/tests/dialects/test_presto.py b/tests/dialects/test_presto.py
index 4bafc081..18433fe4 100644
--- a/tests/dialects/test_presto.py
+++ b/tests/dialects/test_presto.py
@@ -1059,6 +1059,15 @@ class TestPresto(Validator):
         )
 
     def test_json(self):
+        with self.assertLogs(helper_logger):
+            self.validate_all(
+                """SELECT JSON_EXTRACT_SCALAR(TRY(FILTER(CAST(JSON_EXTRACT('{"k1": [{"k2": "{\\"k3\\": 1}", "k4": "v"}]}', '$.k1') AS ARRAY(MAP(VARCHAR, VARCHAR))), x -> x['k4'] = 'v')[1]['k2']), '$.k3')""",
+                write={
+                    "presto": """SELECT JSON_EXTRACT_SCALAR(TRY(FILTER(CAST(JSON_EXTRACT('{"k1": [{"k2": "{\\"k3\\": 1}", "k4": "v"}]}', '$.k1') AS ARRAY(MAP(VARCHAR, VARCHAR))), x -> x['k4'] = 'v')[1]['k2']), '$.k3')""",
+                    "spark": """SELECT GET_JSON_OBJECT(FILTER(FROM_JSON(GET_JSON_OBJECT('{"k1": [{"k2": "{\\\\"k3\\\\": 1}", "k4": "v"}]}', '$.k1'), 'ARRAY<MAP<STRING, STRING>>'), x -> x['k4'] = 'v')[0]['k2'], '$.k3')""",
+                },
+            )
+
         self.validate_all(
             "SELECT CAST(JSON '[1,23,456]' AS ARRAY(INTEGER))",
             write={
@@ -1073,7 +1082,6 @@ class TestPresto(Validator):
                 "presto": 'SELECT CAST(JSON_PARSE(\'{"k1":1,"k2":23,"k3":456}\') AS MAP(VARCHAR, INTEGER))',
             },
         )
-
         self.validate_all(
             "SELECT CAST(ARRAY [1, 23, 456] AS JSON)",
             write={