Repository: tobymao/sqlglot
Pull request: fix MySQL json_extract_scalar
Fix https://github.com/tobymao/sqlglot/issues/1349

Problem statement:
JSON_EXTRACT_SCALAR is not a valid function in MySQL
```
import sqlglot
s = '''select * from requests where get_json_object(stream_data, '$.data.results') is not null'''
sqlglot.transpile(s, read='hive', write='mysql')
```

the result is `["SELECT * FROM requests WHERE NOT JSON_EXTRACT_SCALAR(stream_data, '$.data.results') IS NULL"]`, while JSON_EXTRACT_SCALAR is not a valid function in MySQL.

Hints from issue discussion:
What is the equivalent way of expressing this in MySQL (+bonus for docs)? JSON handling is non-standard, so it's not sufficiently covered yet across many dialects.

Commit message: fix MySQL json_extract_scalar

Patch to review:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index ab26ebc0e9..b9071dc3cd 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -404,6 +404,8 @@ class Generator(generator.Generator):
             exp.DayOfYear: rename_func("DAYOFYEAR"),
             exp.WeekOfYear: rename_func("WEEKOFYEAR"),
             exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
+            exp.JSONExtract: rename_func("JSON_EXTRACT"),
+            exp.JSONExtractScalar: rename_func("JSON_EXTRACT"),
             exp.StrToDate: _str_to_date_sql,
             exp.StrToTime: _str_to_date_sql,
             exp.Trim: _trim_sql,
diff --git a/tests/dialects/test_dialect.py b/tests/dialects/test_dialect.py
index c6508e8669..a1b652975e 100644
--- a/tests/dialects/test_dialect.py
+++ b/tests/dialects/test_dialect.py
@@ -812,11 +812,13 @@ def test_json(self):
         self.validate_all(
             "JSON_EXTRACT(x, 'y')",
             read={
+                "mysql": "JSON_EXTRACT(x, 'y')",
                 "postgres": "x->'y'",
                 "presto": "JSON_EXTRACT(x, 'y')",
                 "starrocks": "x -> 'y'",
             },
             write={
+                "mysql": "JSON_EXTRACT(x, 'y')",
                 "oracle": "JSON_EXTRACT(x, 'y')",
                 "postgres": "x -> 'y'",
                 "presto": "JSON_EXTRACT(x, 'y')",
@@ -830,6 +832,7 @@ def test_json(self):
                 "presto": "JSON_EXTRACT_SCALAR(x, 'y')",
             },
             write={
+                "mysql": "JSON_EXTRACT(x, 'y')",
                 "postgres": "x ->> 'y'",
                 "presto": "JSON_EXTRACT_SCALAR(x, 'y')",
             },


Code review comments:
diff_hunk: @@ -404,6 +404,8 @@ class Generator(generator.Generator):
             exp.DayOfYear: rename_func("DAYOFYEAR"),
             exp.WeekOfYear: rename_func("WEEKOFYEAR"),
             exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
+            exp.JSONExtract: rename_func("JSON_EXTRACT"),
path: sqlglot/dialects/mysql.py
text: @user1:
This isn't needed, because `JSONExtract` is already generated as `"JSON_EXTRACT"` by default.

@author:
correct, removed