Repository: tobymao/sqlglot

Issue:
Missing Support for JSON EXISTS in ORACLE
**Description**
I'm encountering an issue with the Oracle dialect in SQLGlot where it fails to parse queries containing JSON conditions like JSON_EXISTS. The expected behavior is to convert the Oracle query to an AST, but SQLGlot does not currently support these conditions, resulting in a parse failure.
**JSON_EXISTS() Function
Syntax:**
> JSON_EXISTS(json_document, json_path [error_clause])
**error_clause:**

- > TRUE ON ERROR: Returns TRUE if an error occurs during the evaluation.
- > FALSE ON ERROR: Returns FALSE if an error occurs during the evaluation.
- > UNKNOWN ON ERROR: Returns UNKNOWN if an error occurs during the evaluation.

**Example**
The following code demonstrates the issue:
```from sqlglot.dialects.oracle import Oracle
from sqlglot import parse_one

sql = """
SELECT name FROM t
WHERE JSON_EXISTS(name, '$[1].middle' TRUE ON ERROR);
"""

ast = parse_one(sql, dialect=Oracle)
print(repr(ast))
```
**Error Encountered**
When executing the code above, the following error is encountered:
`   raise error
sqlglot.errors.ParseError: Expecting ). Line 2, Col: 45.
 `
[ link ](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SQL-JSON-Conditions.html#GUID-57762C80-0C8A-4B18-9BA7-9B3F5ABDC988)for the document

Discussion:

Solution patch:
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 294a329ff8..669a4b7643 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -391,6 +391,9 @@ class Dialect(metaclass=_Dialect):
     STRICT_JSON_PATH_SYNTAX = True
     """Whether failing to parse a JSON path expression using the JSONPath dialect will log a warning."""
 
+    ON_CONDITION_EMPTY_BEFORE_ERROR = True
+    """Whether "X ON EMPTY" should come before "X ON ERROR" (for dialects like T-SQL, MySQL, Oracle)"""
+
     SET_OP_DISTINCT_BY_DEFAULT: t.Dict[t.Type[exp.Expression], t.Optional[bool]] = {
         exp.Except: True,
         exp.Intersect: True,
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index eff423891d..47a518b18e 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -666,17 +666,6 @@ def concat_exprs(
             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()
@@ -688,8 +677,7 @@ def _parse_on_options() -> t.Optional[exp.Expression] | str:
                 this=this,
                 path=self.dialect.to_json_path(path),
                 returning=returning,
-                on_error=_parse_on_options(),
-                on_empty=_parse_on_options(),
+                on_condition=self._parse_on_condition(),
             )
 
     class Generator(generator.Generator):
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 6c1e516426..a596ef6f33 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -47,6 +47,7 @@ class Oracle(Dialect):
     LOCKING_READS_SUPPORTED = True
     TABLESAMPLE_SIZE_IS_PERCENT = True
     NULL_ORDERING = "nulls_are_large"
+    ON_CONDITION_EMPTY_BEFORE_ERROR = False
 
     # See section 8: https://docs.oracle.com/cd/A97630_01/server.920/a96540/sql_elements9a.htm
     NORMALIZATION_STRATEGY = NormalizationStrategy.UPPERCASE
@@ -138,6 +139,7 @@ class Parser(parser.Parser):
                 order=self._parse_order(),
             ),
             "XMLTABLE": lambda self: self._parse_xml_table(),
+            "JSON_EXISTS": lambda self: self._parse_json_exists(),
         }
 
         PROPERTY_PARSERS = {
@@ -226,6 +228,18 @@ def _parse_query_restrictions(self) -> t.Optional[exp.Expression]:
                 expression=self._match(TokenType.CONSTRAINT) and self._parse_field(),
             )
 
+        def _parse_json_exists(self) -> exp.JSONExists:
+            this = self._parse_format_json(self._parse_bitwise())
+            self._match(TokenType.COMMA)
+            return self.expression(
+                exp.JSONExists,
+                this=this,
+                path=self.dialect.to_json_path(self._parse_bitwise()),
+                passing=self._match_text_seq("PASSING")
+                and self._parse_csv(lambda: self._parse_alias(self._parse_bitwise())),
+                on_condition=self._parse_on_condition(),
+            )
+
     class Generator(generator.Generator):
         LOCKING_READS_SUPPORTED = True
         JOIN_HINTS = False
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 6d2765da6e..cf0d6c2fab 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -2230,6 +2230,10 @@ class OnConflict(Expression):
     }
 
 
+class OnCondition(Expression):
+    arg_types = {"error": False, "empty": False, "null": False}
+
+
 class Returning(Expression):
     arg_types = {"expressions": True, "into": False}
 
@@ -5585,6 +5589,10 @@ class JSONArrayAgg(Func):
     }
 
 
+class JSONExists(Func):
+    arg_types = {"this": True, "path": True, "passing": False, "on_condition": False}
+
+
 # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
 # Note: parsing of JSON column definitions is currently incomplete.
 class JSONColumnDef(Expression):
@@ -5601,8 +5609,7 @@ class JSONValue(Expression):
         "this": True,
         "path": True,
         "returning": False,
-        "on_empty": False,
-        "on_error": False,
+        "on_condition": False,
     }
 
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 3df88cf77f..7b5f9d267f 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -4191,13 +4191,10 @@ def _generate_on_options(arg: t.Any) -> str:
         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_condition = self.sql(expression, "on_condition")
+        on_condition = f" {on_condition}" if on_condition 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}")
+        return self.func("JSON_VALUE", expression.this, f"{path}{returning}{on_condition}")
 
     def conditionalinsert_sql(self, expression: exp.ConditionalInsert) -> str:
         else_ = "ELSE " if expression.args.get("else_") else ""
@@ -4211,3 +4208,45 @@ def multitableinserts_sql(self, expression: exp.MultitableInserts) -> str:
         expressions = self.seg(self.expressions(expression, sep=" "))
         res = f"INSERT {kind}{expressions}{self.seg(self.sql(expression, 'source'))}"
         return res
+
+    def oncondition_sql(self, expression: exp.OnCondition) -> str:
+        # Static options like "NULL ON ERROR" are stored as strings, in contrast to "DEFAULT <expr> ON ERROR"
+        empty = expression.args.get("empty")
+        empty = (
+            f"DEFAULT {empty} ON EMPTY"
+            if isinstance(empty, exp.Expression)
+            else self.sql(expression, "empty")
+        )
+
+        error = expression.args.get("error")
+        error = (
+            f"DEFAULT {error} ON ERROR"
+            if isinstance(error, exp.Expression)
+            else self.sql(expression, "error")
+        )
+
+        if error and empty:
+            error = (
+                f"{empty} {error}"
+                if self.dialect.ON_CONDITION_EMPTY_BEFORE_ERROR
+                else f"{error} {empty}"
+            )
+            empty = ""
+
+        null = self.sql(expression, "null")
+
+        return f"{empty}{error}{null}"
+
+    def jsonexists_sql(self, expression: exp.JSONExists) -> str:
+        this = self.sql(expression, "this")
+        path = self.sql(expression, "path")
+
+        passing = self.expressions(expression, "passing")
+        passing = f" PASSING {passing}" if passing else ""
+
+        on_condition = self.sql(expression, "on_condition")
+        on_condition = f" {on_condition}" if on_condition else ""
+
+        path = f"{path}{passing}{on_condition}"
+
+        return self.func("JSON_EXISTS", this, path)
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 1e30b177cd..68d6859146 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -1262,6 +1262,8 @@ class Parser(metaclass=_Parser):
         "ts": exp.Timestamp,
     }
 
+    ON_CONDITION_TOKENS = {"ERROR", "NULL", "TRUE", "FALSE", "EMPTY"}
+
     STRICT_CAST = True
 
     PREFIXED_PIVOT_COLUMNS = False
@@ -5914,12 +5916,43 @@ def _parse_format_json(self, this: t.Optional[exp.Expression]) -> t.Optional[exp
 
         return self.expression(exp.FormatJson, this=this)
 
-    def _parse_on_handling(self, on: str, *values: str) -> t.Optional[str]:
-        # Parses the "X ON Y" syntax, i.e. NULL ON NULL (Oracle, T-SQL)
+    def _parse_on_condition(self) -> t.Optional[exp.OnCondition]:
+        # MySQL uses "X ON EMPTY Y ON ERROR" (e.g. JSON_VALUE) while Oracle uses the opposite (e.g. JSON_EXISTS)
+        if self.dialect.ON_CONDITION_EMPTY_BEFORE_ERROR:
+            empty = self._parse_on_handling("EMPTY", *self.ON_CONDITION_TOKENS)
+            error = self._parse_on_handling("ERROR", *self.ON_CONDITION_TOKENS)
+        else:
+            error = self._parse_on_handling("ERROR", *self.ON_CONDITION_TOKENS)
+            empty = self._parse_on_handling("EMPTY", *self.ON_CONDITION_TOKENS)
+
+        null = self._parse_on_handling("NULL", *self.ON_CONDITION_TOKENS)
+
+        if not empty and not error and not null:
+            return None
+
+        return self.expression(
+            exp.OnCondition,
+            empty=empty,
+            error=error,
+            null=null,
+        )
+
+    def _parse_on_handling(
+        self, on: str, *values: str
+    ) -> t.Optional[str] | t.Optional[exp.Expression]:
+        # Parses the "X ON Y" or "DEFAULT <expr> ON Y syntax, e.g. NULL ON NULL (Oracle, T-SQL, MySQL)
         for value in values:
             if self._match_text_seq(value, "ON", on):
                 return f"{value} ON {on}"
 
+        index = self._index
+        if self._match(TokenType.DEFAULT):
+            default_value = self._parse_bitwise()
+            if self._match_text_seq("ON", on):
+                return default_value
+
+            self._retreat(index)
+
         return None
 
     @t.overload


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index dbcff13278..b4d4e2e9e9 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -537,3 +537,17 @@ def test_multitable_inserts(self):
             "WHEN salary > 6000 THEN INTO emp4 "
             "SELECT salary FROM employees"
         )
+
+    def test_json_functions(self):
+        for format_json in ("", " FORMAT JSON"):
+            for on_cond in (
+                "",
+                " TRUE ON ERROR",
+                " NULL ON EMPTY",
+                " DEFAULT 1 ON ERROR TRUE ON EMPTY",
+            ):
+                for passing in ("", " PASSING 'name1' AS \"var1\", 'name2' AS \"var2\""):
+                    with self.subTest("Testing JSON_EXISTS()"):
+                        self.validate_identity(
+                            f"SELECT * FROM t WHERE JSON_EXISTS(name{format_json}, '$[1].middle'{passing}{on_cond})"
+                        )