Repository: tobymao/sqlglot

Issue:
Support for try_parse_json
**Is your feature request related to a problem? Please describe.**
A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

I'm using sqlglot through sqlmesh and I have to use a macro for getting try_parse_json to work in duckdb.

**Describe the solution you'd like**
A clear and concise description of what you want to happen.

I would like try_parse_json to be supported in sqlglot.

**Describe alternatives you've considered**
A clear and concise description of any alternative solutions or features you've considered.

I tried using check_json as well but it's not supported either.

I ended up writing a conditional macro in sqlmesh to change the code depending on the gateway/dialect.

 @IF(
    @gateway = /* duckdb doesn't have try_parse_json and it's not in sqlglot */ 'local',
    CASE WHEN JSON_VALID(query_tag) THEN PARSE_JSON(query_tag) ELSE NULL END,
    TRY_PARSE_JSON(query_tag)
  ) AS query_tag,

**Additional context**
Add any other context or screenshots about the feature request here.

![image](https://github.com/tobymao/sqlglot/assets/110614540/cc48bd55-4154-4856-885d-574740cd5dbd)



Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 102f3e3a..4db30b14 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -432,7 +432,6 @@ class DuckDB(Dialect):
                 exp.cast(e.expression, exp.DataType.Type.TIMESTAMP, copy=True),
                 exp.cast(e.this, exp.DataType.Type.TIMESTAMP, copy=True),
             ),
-            exp.ParseJSON: rename_func("JSON"),
             exp.PercentileCont: rename_func("QUANTILE_CONT"),
             exp.PercentileDisc: rename_func("QUANTILE_DISC"),
             # DuckDB doesn't allow qualified columns inside of PIVOT expressions.
@@ -612,6 +611,12 @@ class DuckDB(Dialect):
         PROPERTIES_LOCATION[exp.LikeProperty] = exp.Properties.Location.POST_SCHEMA
         PROPERTIES_LOCATION[exp.TemporaryProperty] = exp.Properties.Location.POST_CREATE
 
+        def parsejson_sql(self, expression: exp.ParseJSON) -> str:
+            arg = expression.this
+            if expression.args.get("safe"):
+                return self.sql(exp.case().when(exp.func("json_valid", arg), arg).else_(exp.null()))
+            return self.func("JSON", arg)
+
         def timefromparts_sql(self, expression: exp.TimeFromParts) -> str:
             nano = expression.args.get("nano")
             if nano is not None:
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 505fc424..d8b03055 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -298,6 +298,7 @@ class Snowflake(Dialect):
             "TIMESTAMPDIFF": _build_datediff,
             "TIMESTAMPFROMPARTS": build_timestamp_from_parts,
             "TIMESTAMP_FROM_PARTS": build_timestamp_from_parts,
+            "TRY_PARSE_JSON": lambda args: exp.ParseJSON(this=seq_get(args, 0), safe=True),
             "TRY_TO_DATE": _build_datetime("TRY_TO_DATE", exp.DataType.Type.DATE, safe=True),
             "TO_DATE": _build_datetime("TO_DATE", exp.DataType.Type.DATE),
             "TO_NUMBER": lambda args: exp.ToNumber(
@@ -752,6 +753,9 @@ class Snowflake(Dialect):
             exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),
             exp.Max: max_or_greatest,
             exp.Min: min_or_least,
+            exp.ParseJSON: lambda self, e: self.func(
+                "TRY_PARSE_JSON" if e.args.get("safe") else "PARSE_JSON", e.this
+            ),
             exp.PartitionedByProperty: lambda self, e: f"PARTITION BY {self.sql(e, 'this')}",
             exp.PercentileCont: transforms.preprocess(
                 [transforms.add_within_group_for_percentiles]
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index c55aa62c..b13d7ab9 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5501,9 +5501,9 @@ class JSONArrayContains(Binary, Predicate, Func):
 
 class ParseJSON(Func):
     # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
+    # Snowflake also has TRY_PARSE_JSON, which is represented using `safe`
     _sql_names = ["PARSE_JSON", "JSON_PARSE"]
-    arg_types = {"this": True, "expressions": False}
-    is_var_len_args = True
+    arg_types = {"this": True, "expression": False, "safe": False}
 
 
 class Least(Func):
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 0aae2e40..2ba64aa2 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -5960,7 +5960,7 @@ class Parser(metaclass=_Parser):
         return None
 
     def _parse_var_or_string(self) -> t.Optional[exp.Expression]:
-        return self._parse_var() or self._parse_string()
+        return self._parse_string() or self._parse_var(any_token=True)
 
     def _parse_primary_or_var(self) -> t.Optional[exp.Expression]:
         return self._parse_primary() or self._parse_var(any_token=True)


Test patch:
diff --git a/tests/dialects/test_duckdb.py b/tests/dialects/test_duckdb.py
index 2bde4784..01a01167 100644
--- a/tests/dialects/test_duckdb.py
+++ b/tests/dialects/test_duckdb.py
@@ -18,6 +18,13 @@ class TestDuckDB(Validator):
             "WITH _data AS (SELECT [STRUCT(1 AS a, 2 AS b), STRUCT(2 AS a, 3 AS b)] AS col) SELECT col.b FROM _data, UNNEST(_data.col) AS col WHERE col.a = 1",
         )
 
+        self.validate_all(
+            """SELECT CASE WHEN JSON_VALID('{"x: 1}') THEN '{"x: 1}' ELSE NULL END""",
+            read={
+                "duckdb": """SELECT CASE WHEN JSON_VALID('{"x: 1}') THEN '{"x: 1}' ELSE NULL END""",
+                "snowflake": """SELECT TRY_PARSE_JSON('{"x: 1}')""",
+            },
+        )
         self.validate_all(
             "SELECT straight_join",
             write={
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 280ebbf2..bfdb2a68 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -117,6 +117,7 @@ class TestMySQL(Validator):
         )
 
     def test_identity(self):
+        self.validate_identity("SELECT CAST(COALESCE(`id`, 'NULL') AS CHAR CHARACTER SET binary)")
         self.validate_identity("SELECT e.* FROM e STRAIGHT_JOIN p ON e.x = p.y")
         self.validate_identity("ALTER TABLE test_table ALTER COLUMN test_column SET DEFAULT 1")
         self.validate_identity("SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:00.0000')")