Repository: tobymao/sqlglot

Issue:
Unable to parse the oracle query when query has JSON components
Hello Toby & Team,
Warm greetings!

**Thank you for fixing all the issues we reported in the past. We really appreciate your efforts! 
We have been using your parser for our work and it has been a great help!**

Could you please help us by providing support for following -

Parser is throwing an error when there are following JSON components are present in the query, like-

1. JSON_OBJECT
2. JSON_ARRAYAGG
3. JSON_ARRAY
4. JSON_TABLE
5. JSON_VALUE

**Oracle Documentation -** 
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html#GUID-6D56077D-78DE-4CC0-9498-225DDC42E054


**Code for your reference -**

```
import sqlglot.expressions as exp

query1 ="""
SELECT
CASE WHEN DBMS_LOB.getlength(info) < 32000
THEN DBMS_LOB.SUBSTR(info)
END info_txt
, info info_clob
FROM schemaname.tablename ar
INNER JOIN JSON_TABLE(:emps,'$[*]' COLUMNS empno NUMBER PATH '$') jt
ON ar.empno = jt.empno
"""

query2 ="""
SELECT DISTINCT res.party_id,
CASE WHEN JSON_VALUE(info,'$.grp_id') IS NOT NULL THEN 'CF'
          WHEN JSON_VALUE(info,'$.txn_id') IS NOT NULL THEN 'PF'
          WHEN JSON_VALUE(info,'$.agnt_id') IS NOT NULL THEN 'SF'
END run_typ
FROM schemaname.tablename res
CROSS JOIN JSON_TABLE([res.info](http://res.info/), '$.results[*]' COLUMNS
     curr_role VARCHAR2(20) PATH '$.curr_role'
     , proj_hiatus_dt VARCHAR2(10) PATH '$.proj_cooloff'
     , clock_reset VARCHAR2(10) PATH '$.clock_reset'
) jt
WHERE proj_hiatus_dt < TO_CHAR(CURRENT_DATE,'YYYY-MM-DD')
OR clock_reset < TO_CHAR(CURRENT_DATE,'YYYY-MM-DD')
"""

query3 ="""
SELECT JSON_ARRAYAGG(JSON_OBJECT(
        KEY 'RNK' VALUE RNK,
        KEY 'RATING_CODE' VALUE RATING_CODE,
        KEY 'DATE_VALUE' VALUE DATE_VALUE,
        KEY 'AGENT_ID' VALUE AGENT_ID
        RETURNING CLOB)  RETURNING CLOB)
        as JSON_DATA
from tablename
"""

expressions = sqlglot.parse_one(query1, read='oracle')
x = list(expressions.find_all(exp.Table))

for i in x:
  listTables = str(i).split(' ')[0]
  print(listTables)

```

Above code fails as it cannot process JSON components.
Request you to add support for JSON in query parsing. So that it won't fail.

Thanks ton in advance!

Thanks, 
Sachin

Discussion:

Patch:
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 90b402d1..61db98f3 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -81,6 +81,7 @@ class Oracle(Dialect):
                 this=self._parse_format_json(self._parse_bitwise()),
                 order=self._parse_order(),
             ),
+            "JSON_TABLE": lambda self: self._parse_json_table(),
             "XMLTABLE": _parse_xml_table,
         }
 
@@ -94,10 +95,33 @@ class Oracle(Dialect):
         # Reference: https://stackoverflow.com/a/336455
         DISTINCT_TOKENS = {TokenType.DISTINCT, TokenType.UNIQUE}
 
+        # Note: this is currently incomplete; it only implements the "JSON_value_column" part
+        def _parse_json_column_def(self) -> exp.JSONColumnDef:
+            this = self._parse_id_var()
+            kind = self._parse_types(allow_identifiers=False)
+            path = self._match_text_seq("PATH") and self._parse_string()
+            return self.expression(exp.JSONColumnDef, this=this, kind=kind, path=path)
+
+        def _parse_json_table(self) -> exp.JSONTable:
+            this = self._parse_format_json(self._parse_bitwise())
+            path = self._match(TokenType.COMMA) and self._parse_string()
+            error_handling = self._parse_on_handling("ERROR", "ERROR", "NULL")
+            empty_handling = self._parse_on_handling("EMPTY", "ERROR", "NULL")
+            self._match(TokenType.COLUMN)
+            expressions = self._parse_wrapped_csv(self._parse_json_column_def)
+
+            return exp.JSONTable(
+                this=this,
+                expressions=expressions,
+                path=path,
+                error_handling=error_handling,
+                empty_handling=empty_handling,
+            )
+
         def _parse_json_array(self, expr_type: t.Type[E], **kwargs) -> E:
             return self.expression(
                 expr_type,
-                null_handling=self._parse_null_handling(),
+                null_handling=self._parse_on_handling("NULL", "NULL", "ABSENT"),
                 return_type=self._match_text_seq("RETURNING") and self._parse_type(),
                 strict=self._match_text_seq("STRICT"),
                 **kwargs,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index c06bd852..6975212d 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4525,6 +4525,23 @@ class JSONArrayAgg(Func):
     }
 
 
+# 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):
+    arg_types = {"this": True, "kind": False, "path": False}
+
+
+# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
+class JSONTable(Func):
+    arg_types = {
+        "this": True,
+        "expressions": True,
+        "path": False,
+        "error_handling": False,
+        "empty_handling": False,
+    }
+
+
 class OpenJSONColumnDef(Expression):
     arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 999c4eec..9500fc52 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -2068,6 +2068,27 @@ class Generator:
             suffix=f"{order}{null_handling}{return_type}{strict})",
         )
 
+    def jsoncolumndef_sql(self, expression: exp.JSONColumnDef) -> str:
+        this = self.sql(expression, "this")
+        kind = self.sql(expression, "kind")
+        kind = f" {kind}" if kind else ""
+        path = self.sql(expression, "path")
+        path = f" PATH {path}" if path else ""
+        return f"{this}{kind}{path}"
+
+    def jsontable_sql(self, expression: exp.JSONTable) -> str:
+        this = self.sql(expression, "this")
+        path = self.sql(expression, "path")
+        path = f", {path}" if path else ""
+        error_handling = expression.args.get("error_handling")
+        error_handling = f" {error_handling}" if error_handling else ""
+        empty_handling = expression.args.get("empty_handling")
+        empty_handling = f" {empty_handling}" if empty_handling else ""
+        columns = f" COLUMNS ({self.expressions(expression, skip_first=True)})"
+        return self.func(
+            "JSON_TABLE", this, suffix=f"{path}{error_handling}{empty_handling}{columns})"
+        )
+
     def openjsoncolumndef_sql(self, expression: exp.OpenJSONColumnDef) -> str:
         this = self.sql(expression, "this")
         kind = self.sql(expression, "kind")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 19ecb364..0a80df29 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -4189,15 +4189,13 @@ class Parser(metaclass=_Parser):
 
         return self.expression(exp.FormatJson, this=this)
 
-    def _parse_null_handling(self) -> t.Optional[str]:
-        # Parses the {NULL|ABSENT} ON NULL syntax
-        null_handling = None
-        if self._match_text_seq("NULL", "ON", "NULL"):
-            null_handling = "NULL ON NULL"
-        elif self._match_text_seq("ABSENT", "ON", "NULL"):
-            null_handling = "ABSENT ON NULL"
+    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)
+        for value in values:
+            if self._match_text_seq(value, "ON", on):
+                return f"{value} ON {on}"
 
-        return null_handling
+        return None
 
     def _parse_json_object(self) -> exp.JSONObject:
         star = self._parse_star()
@@ -4206,7 +4204,7 @@ class Parser(metaclass=_Parser):
             if star
             else self._parse_csv(lambda: self._parse_format_json(self._parse_json_key_value()))
         )
-        null_handling = self._parse_null_handling()
+        null_handling = self._parse_on_handling("NULL", "NULL", "ABSENT")
 
         unique_keys = None
         if self._match_text_seq("WITH", "UNIQUE"):


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 8f72f7d9..f96e1b01 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -26,6 +26,9 @@ class TestOracle(Validator):
         self.validate_identity("SELECT * FROM table_name@dblink_name.database_link_domain")
         self.validate_identity("SELECT * FROM table_name SAMPLE (25) s")
         self.validate_identity("SELECT * FROM V$SESSION")
+        self.validate_identity(
+            "SELECT JSON_ARRAYAGG(JSON_OBJECT('RNK': RNK, 'RATING_CODE': RATING_CODE, 'DATE_VALUE': DATE_VALUE, 'AGENT_ID': AGENT_ID RETURNING CLOB) RETURNING CLOB) AS JSON_DATA FROM tablename"
+        )
         self.validate_identity(
             "SELECT JSON_ARRAY(FOO() FORMAT JSON, BAR() NULL ON NULL RETURNING CLOB STRICT)"
         )
@@ -197,3 +200,17 @@ MATCH_RECOGNIZE (
 ) MR""",
             pretty=True,
         )
+
+    def test_json_table(self):
+        self.validate_identity(
+            "SELECT * FROM JSON_TABLE(foo FORMAT JSON, 'bla' ERROR ON ERROR NULL ON EMPTY COLUMNS (foo PATH 'bar'))"
+        )
+        self.validate_identity(
+            """SELECT
+  CASE WHEN DBMS_LOB.GETLENGTH(info) < 32000 THEN DBMS_LOB.SUBSTR(info) END AS info_txt,
+  info AS info_clob
+FROM schemaname.tablename ar
+INNER JOIN JSON_TABLE(:emps, '$[*]' COLUMNS (empno NUMBER PATH '$')) jt
+  ON ar.empno = jt.empno""",
+            pretty=True,
+        )