Repository: tobymao/sqlglot

Issue:
Missing Support for JSON Path Syntax in MySQL Dialect
### Description:
SQLGlot MySQL dialect does not support JSON path syntax functions like JSON_VALUE, resulting in a parse failure when attempting to convert a MySQL query to an Abstract Syntax Tree (AST).

### Details:

MySQL JSON Path Syntax Functions

MySQL supports JSON path syntax functions, such as JSON_VALUE, which allow extracting values from JSON documents.
JSON_VALUE Function Syntax
The specific functions and syntax that are not supported include:

**`JSON_VALUE()` Function:**

- **Syntax:**
  ```sql
  JSON_VALUE(json_doc, path [RETURNING type] [on_empty] [on_error])

```
Unsupported Syntax Elements

The specific syntax elements not supported by SQLGlot's MySQL dialect include:

-     RETURNING type
-     on_empty: {NULL | ERROR | DEFAULT value} ON EMPTY
-     on_error: {NULL | ERROR | DEFAULT value} ON ERROR
```
**CODE**
````from sqlglot.dialects.mysql import MySQL
from sqlglot import parse_one

sql = """
SELECT SELECT JSON_VALUE('{"item": "shoes", "price": "49.95"}', '$.price'
     RETURNING DECIMAL(4,2)) AS price;
"""

ast = parse_one(sql, dialect=MySQL)
print(repr(ast))
````


**ERROR** 

**sqlglot.errors.ParseError: Invalid expression / Unexpected token. Line 2, Col: 13.**


 [   link](https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html) for the documetation 
    
    
    

Discussion:

Patch:
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index c3826d2e..d7a2d926 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -335,6 +335,7 @@ class MySQL(Dialect):
             "VALUES": lambda self: self.expression(
                 exp.Anonymous, this="VALUES", expressions=[self._parse_id_var()]
             ),
+            "JSON_VALUE": lambda self: self._parse_json_value(),
         }
 
         STATEMENT_PARSERS = {
@@ -664,6 +665,33 @@ class MySQL(Dialect):
 
             return self.expression(exp.GroupConcat, this=this, separator=separator)
 
+        def _parse_json_value(self) -> exp.JSONValue:
+            def _parse_on_options() -> t.Optional[exp.Expression] | str:
+                if self._match_texts(("NULL", "ERROR")):
+                    value = self._prev.text.upper()
+                else:
+                    value = self._match(TokenType.DEFAULT) and self._parse_bitwise()
+
+                self._match_text_seq("ON")
+                self._match_texts(("EMPTY", "ERROR"))
+
+                return value
+
+            this = self._parse_bitwise()
+            self._match(TokenType.COMMA)
+            path = self._parse_bitwise()
+
+            returning = self._match(TokenType.RETURNING) and self._parse_type()
+
+            return self.expression(
+                exp.JSONValue,
+                this=this,
+                path=self.dialect.to_json_path(path),
+                returning=returning,
+                on_error=_parse_on_options(),
+                on_empty=_parse_on_options(),
+            )
+
     class Generator(generator.Generator):
         INTERVAL_ALLOWS_PLURAL_FORM = False
         LOCKING_READS_SUPPORTED = True
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 7cc4e656..ad79c9ae 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2172,6 +2172,7 @@ class Insert(DDL, DML):
         "stored": False,
         "partition": False,
         "settings": False,
+        "source": False,
     }
 
     def with_(
@@ -5558,6 +5559,17 @@ class JSONSchema(Expression):
     arg_types = {"expressions": True}
 
 
+# https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value
+class JSONValue(Expression):
+    arg_types = {
+        "this": True,
+        "path": True,
+        "returning": False,
+        "on_empty": False,
+        "on_error": False,
+    }
+
+
 # # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
 class JSONTable(Func):
     arg_types = {
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 08cb6f90..293daccd 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -1669,7 +1669,10 @@ class Generator(metaclass=_Generator):
         settings = self.sql(expression, "settings")
         settings = f" {settings}" if settings else ""
 
-        sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{expression_sql}"
+        source = self.sql(expression, "source")
+        source = f"TABLE {source}" if source else ""
+
+        sql = f"INSERT{hint}{alternative}{ignore}{this}{stored}{by_name}{exists}{partition_by}{settings}{where}{expression_sql}{source}"
         return self.prepend_ctes(expression, sql)
 
     def intersect_sql(self, expression: exp.Intersect) -> str:
@@ -4160,3 +4163,19 @@ class Generator(metaclass=_Generator):
         unique_sql = " UNIQUE KEYS" if expression.args.get("unique") else ""
 
         return f"JSON{this}{with_sql}{unique_sql}"
+
+    def jsonvalue_sql(self, expression: exp.JSONValue) -> str:
+        def _generate_on_options(arg: t.Any) -> str:
+            return arg if isinstance(arg, str) else f"DEFAULT {self.sql(arg)}"
+
+        path = self.sql(expression, "path")
+        returning = self.sql(expression, "returning")
+        returning = f" RETURNING {returning}" if returning else ""
+
+        on_empty = expression.args.get("on_empty")
+        on_empty = f" {_generate_on_options(on_empty)} ON EMPTY" if on_empty else ""
+
+        on_error = expression.args.get("on_error")
+        on_error = f" {_generate_on_options(on_error)} ON ERROR" if on_error else ""
+
+        return self.func("JSON_VALUE", expression.this, f"{path}{returning}{on_empty}{on_error}")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 040c07c2..17d5de3d 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -2569,6 +2569,7 @@ class Parser(metaclass=_Parser):
             overwrite=overwrite,
             alternative=alternative,
             ignore=ignore,
+            source=self._match(TokenType.TABLE) and self._parse_table(),
         )
 
     def _parse_kill(self) -> exp.Kill:


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 059b459f..45b79bf6 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -1251,3 +1251,15 @@ COMMENT='客户账户表'"""
                 write_sql="SELECT foo",
             )
             assert "AT TIME ZONE is not supported" in cm.output[0]
+
+    def test_json_value(self):
+        json_doc = """'{"item": "shoes", "price": "49.95"}'"""
+        self.validate_identity(f"""SELECT JSON_VALUE({json_doc}, '$.price')""")
+        self.validate_identity(
+            f"""SELECT JSON_VALUE({json_doc}, '$.price' RETURNING DECIMAL(4, 2))"""
+        )
+
+        for on_option in ("NULL", "ERROR", "DEFAULT 1"):
+            self.validate_identity(
+                f"""SELECT JSON_VALUE({json_doc}, '$.price' RETURNING DECIMAL(4, 2) {on_option} ON EMPTY {on_option} ON ERROR) AS price"""
+            )
diff --git a/tests/dialects/test_spark.py b/tests/dialects/test_spark.py
index feccd369..c9b78974 100644
--- a/tests/dialects/test_spark.py
+++ b/tests/dialects/test_spark.py
@@ -10,6 +10,7 @@ class TestSpark(Validator):
     dialect = "spark"
 
     def test_ddl(self):
+        self.validate_identity("INSERT OVERWRITE TABLE db1.tb1 TABLE db2.tb2")
         self.validate_identity("CREATE TABLE foo AS WITH t AS (SELECT 1 AS col) SELECT col FROM t")
         self.validate_identity("CREATE TEMPORARY VIEW test AS SELECT 1")
         self.validate_identity("CREATE TABLE foo (col VARCHAR(50))")