Repository: tobymao/sqlglot

Issue:
Issues in parsing JSON_BOJECT and JSON_OBJECTAGG  
Hi Team,

I have observed that sqlglot parsing fails for few SQL/JSON functions like JSON_OBJECT, JSON_OBJECTAGG

**JSON_OBJECT is failing with IS clause.**
Eg.
```
select
JSON_OBJECT(
KEY 'key1' IS emp.column1,
KEY 'key2' IS emp.column1
) "emp_key"
from emp
```
**Documentation -**
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SQL-JSON-Conditions.html#GUID-08C75404-6E58-4EBE-A8B4-0B6041B0DB63

**JSON_OBJECTAGG is not supported yet, basic query parsing is failed**

eg. 
```
SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) "Department Numbers"
  FROM departments
  WHERE department_id <= 30
```

**Documentation -**
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_OBJECTAGG.html#GUID-09422D4A-936C-4D38-9991-C64101283D98

Request team to look into this. Thank you!

Thanks, 
Shweta

Discussion:

Patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 99801c26..22a7d2e6 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -16,7 +16,6 @@ from sqlglot.dialects.dialect import (
     format_time_lambda,
     if_sql,
     inline_array_sql,
-    json_keyvalue_comma_sql,
     max_or_greatest,
     min_or_least,
     no_ilike_sql,
@@ -30,6 +29,9 @@ from sqlglot.dialects.dialect import (
 from sqlglot.helper import seq_get, split_num_words
 from sqlglot.tokens import TokenType
 
+if t.TYPE_CHECKING:
+    from typing_extensions import Literal
+
 logger = logging.getLogger("sqlglot")
 
 
@@ -476,7 +478,15 @@ class BigQuery(Dialect):
 
             return table
 
-        def _parse_json_object(self) -> exp.JSONObject:
+        @t.overload
+        def _parse_json_object(self, agg: Literal[False]) -> exp.JSONObject:
+            ...
+
+        @t.overload
+        def _parse_json_object(self, agg: Literal[True]) -> exp.JSONObjectAgg:
+            ...
+
+        def _parse_json_object(self, agg=False):
             json_object = super()._parse_json_object()
             array_kv_pair = seq_get(json_object.expressions, 0)
 
@@ -531,6 +541,7 @@ class BigQuery(Dialect):
         LIMIT_ONLY_LITERALS = True
         SUPPORTS_TABLE_ALIAS_COLUMNS = False
         UNPIVOT_ALIASES_ARE_IDENTIFIERS = False
+        JSON_KEY_VALUE_PAIR_SEP = ","
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -562,7 +573,6 @@ class BigQuery(Dialect):
             exp.ILike: no_ilike_sql,
             exp.IntDiv: rename_func("DIV"),
             exp.JSONFormat: rename_func("TO_JSON_STRING"),
-            exp.JSONKeyValue: json_keyvalue_comma_sql,
             exp.Max: max_or_greatest,
             exp.MD5: lambda self, e: self.func("TO_HEX", self.func("MD5", e.this)),
             exp.MD5Digest: rename_func("MD5"),
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 724a67fe..6c47f58a 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -905,11 +905,6 @@ def bool_xor_sql(self: Generator, expression: exp.Xor) -> str:
     return f"({a} AND (NOT {b})) OR ((NOT {a}) AND {b})"
 
 
-# Used to generate JSON_OBJECT with a comma in BigQuery and MySQL instead of colon
-def json_keyvalue_comma_sql(self: Generator, expression: exp.JSONKeyValue) -> str:
-    return f"{self.sql(expression, 'this')}, {self.sql(expression, 'expression')}"
-
-
 def is_parse_json(expression: exp.Expression) -> bool:
     return isinstance(expression, exp.ParseJSON) or (
         isinstance(expression, exp.Cast) and expression.is_type("json")
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index d89ac5f9..5e80857c 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -17,7 +17,6 @@ from sqlglot.dialects.dialect import (
     encode_decode_sql,
     format_time_lambda,
     inline_array_sql,
-    json_keyvalue_comma_sql,
     no_comment_column_constraint_sql,
     no_properties_sql,
     no_safe_divide_sql,
@@ -313,6 +312,7 @@ class DuckDB(Dialect):
         TABLESAMPLE_KEYWORDS = "USING SAMPLE"
         TABLESAMPLE_SEED_KEYWORD = "REPEATABLE"
         LAST_DAY_SUPPORTS_DATE_PART = False
+        JSON_KEY_VALUE_PAIR_SEP = ","
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -355,7 +355,6 @@ class DuckDB(Dialect):
             exp.JSONExtract: arrow_json_extract_sql,
             exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
             exp.JSONFormat: _json_format_sql,
-            exp.JSONKeyValue: json_keyvalue_comma_sql,
             exp.LogicalOr: rename_func("BOOL_OR"),
             exp.LogicalAnd: rename_func("BOOL_AND"),
             exp.MonthsBetween: lambda self, e: self.func(
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index 683cd70b..53194074 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -11,7 +11,6 @@ from sqlglot.dialects.dialect import (
     datestrtodate_sql,
     format_time_lambda,
     isnull_to_is_null,
-    json_keyvalue_comma_sql,
     locate_to_strposition,
     max_or_greatest,
     min_or_least,
@@ -631,6 +630,7 @@ class MySQL(Dialect):
         VALUES_AS_TABLE = False
         NVL2_SUPPORTED = False
         LAST_DAY_SUPPORTS_DATE_PART = False
+        JSON_KEY_VALUE_PAIR_SEP = ","
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -650,7 +650,6 @@ class MySQL(Dialect):
             exp.GroupConcat: lambda self, e: f"""GROUP_CONCAT({self.sql(e, "this")} SEPARATOR {self.sql(e, "separator") or "','"})""",
             exp.ILike: no_ilike_sql,
             exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
-            exp.JSONKeyValue: json_keyvalue_comma_sql,
             exp.Max: max_or_greatest,
             exp.Min: min_or_least,
             exp.Month: _remove_ts_or_ds_to_date(),
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 454df94c..f6da84d0 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -14,7 +14,6 @@ from sqlglot.dialects.dialect import (
     format_time_lambda,
     if_sql,
     inline_array_sql,
-    json_keyvalue_comma_sql,
     max_or_greatest,
     min_or_least,
     rename_func,
@@ -695,6 +694,7 @@ class Snowflake(Dialect):
         SUPPORTS_TABLE_COPY = False
         COLLATE_IS_FUNC = True
         LIMIT_ONLY_LITERALS = True
+        JSON_KEY_VALUE_PAIR_SEP = ","
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -723,7 +723,6 @@ class Snowflake(Dialect):
             exp.GroupConcat: rename_func("LISTAGG"),
             exp.If: if_sql(name="IFF", false_value="NULL"),
             exp.JSONExtract: lambda self, e: f"{self.sql(e, 'this')}[{self.sql(e, 'expression')}]",
-            exp.JSONKeyValue: json_keyvalue_comma_sql,
             exp.JSONObject: lambda self, e: self.func("OBJECT_CONSTRUCT_KEEP_NULL", *e.expressions),
             exp.LogicalAnd: rename_func("BOOLAND_AGG"),
             exp.LogicalOr: rename_func("BOOLOR_AGG"),
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index eb415bb6..5c3ef99a 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4823,6 +4823,16 @@ class JSONObject(Func):
     }
 
 
+class JSONObjectAgg(AggFunc):
+    arg_types = {
+        "expressions": False,
+        "null_handling": False,
+        "unique_keys": False,
+        "return_type": False,
+        "encoding": False,
+    }
+
+
 # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
 class JSONArray(Func):
     arg_types = {
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 35e9ff35..e007676f 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -251,6 +251,9 @@ class Generator:
     # Whether or not UNPIVOT aliases are Identifiers (False means they're Literals)
     UNPIVOT_ALIASES_ARE_IDENTIFIERS = True
 
+    # What delimiter to use for separating JSON key/value pairs
+    JSON_KEY_VALUE_PAIR_SEP = ":"
+
     TYPE_MAPPING = {
         exp.DataType.Type.NCHAR: "CHAR",
         exp.DataType.Type.NVARCHAR: "VARCHAR",
@@ -2315,29 +2318,35 @@ class Generator:
         return f"{self.func('MATCH', *expression.expressions)} AGAINST({self.sql(expression, 'this')}{modifier})"
 
     def jsonkeyvalue_sql(self, expression: exp.JSONKeyValue) -> str:
-        return f"{self.sql(expression, 'this')}: {self.sql(expression, 'expression')}"
+        return f"{self.sql(expression, 'this')}{self.JSON_KEY_VALUE_PAIR_SEP} {self.sql(expression, 'expression')}"
 
     def formatjson_sql(self, expression: exp.FormatJson) -> str:
         return f"{self.sql(expression, 'this')} FORMAT JSON"
 
-    def jsonobject_sql(self, expression: exp.JSONObject) -> str:
+    def jsonobject_sql(self, expression: exp.JSONObject | exp.JSONObjectAgg) -> str:
         null_handling = expression.args.get("null_handling")
         null_handling = f" {null_handling}" if null_handling else ""
+
         unique_keys = expression.args.get("unique_keys")
         if unique_keys is not None:
             unique_keys = f" {'WITH' if unique_keys else 'WITHOUT'} UNIQUE KEYS"
         else:
             unique_keys = ""
+
         return_type = self.sql(expression, "return_type")
         return_type = f" RETURNING {return_type}" if return_type else ""
         encoding = self.sql(expression, "encoding")
         encoding = f" ENCODING {encoding}" if encoding else ""
+
         return self.func(
-            "JSON_OBJECT",
+            "JSON_OBJECT" if isinstance(expression, exp.JSONObject) else "JSON_OBJECTAGG",
             *expression.expressions,
             suffix=f"{null_handling}{unique_keys}{return_type}{encoding})",
         )
 
+    def jsonobjectagg_sql(self, expression: exp.JSONObjectAgg) -> str:
+        return self.jsonobject_sql(expression)
+
     def jsonarray_sql(self, expression: exp.JSONArray) -> str:
         null_handling = expression.args.get("null_handling")
         null_handling = f" {null_handling}" if null_handling else ""
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 4c76a906..85aef43b 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -12,6 +12,8 @@ from sqlglot.tokens import Token, Tokenizer, TokenType
 from sqlglot.trie import TrieResult, in_trie, new_trie
 
 if t.TYPE_CHECKING:
+    from typing_extensions import Literal
+
     from sqlglot._typing import E
     from sqlglot.dialects.dialect import Dialect, DialectType
 
@@ -845,6 +847,7 @@ class Parser(metaclass=_Parser):
         "DECODE": lambda self: self._parse_decode(),
         "EXTRACT": lambda self: self._parse_extract(),
         "JSON_OBJECT": lambda self: self._parse_json_object(),
+        "JSON_OBJECTAGG": lambda self: self._parse_json_object(agg=True),
         "JSON_TABLE": lambda self: self._parse_json_table(),
         "MATCH": lambda self: self._parse_match_against(),
         "OPENJSON": lambda self: self._parse_open_json(),
@@ -929,6 +932,8 @@ class Parser(metaclass=_Parser):
     WINDOW_BEFORE_PAREN_TOKENS = {TokenType.OVER}
     WINDOW_SIDES = {"FOLLOWING", "PRECEDING"}
 
+    JSON_KEY_VALUE_SEPARATOR_TOKENS = {TokenType.COLON, TokenType.COMMA, TokenType.IS}
+
     FETCH_TOKENS = ID_VAR_TOKENS - {TokenType.ROW, TokenType.ROWS, TokenType.PERCENT}
 
     ADD_CONSTRAINT_TOKENS = {TokenType.CONSTRAINT, TokenType.PRIMARY_KEY, TokenType.FOREIGN_KEY}
@@ -4564,7 +4569,7 @@ class Parser(metaclass=_Parser):
     def _parse_json_key_value(self) -> t.Optional[exp.JSONKeyValue]:
         self._match_text_seq("KEY")
         key = self._parse_column()
-        self._match_set((TokenType.COLON, TokenType.COMMA))
+        self._match_set(self.JSON_KEY_VALUE_SEPARATOR_TOKENS)
         self._match_text_seq("VALUE")
         value = self._parse_bitwise()
 
@@ -4586,7 +4591,15 @@ class Parser(metaclass=_Parser):
 
         return None
 
-    def _parse_json_object(self) -> exp.JSONObject:
+    @t.overload
+    def _parse_json_object(self, agg: Literal[False]) -> exp.JSONObject:
+        ...
+
+    @t.overload
+    def _parse_json_object(self, agg: Literal[True]) -> exp.JSONObjectAgg:
+        ...
+
+    def _parse_json_object(self, agg=False):
         star = self._parse_star()
         expressions = (
             [star]
@@ -4609,7 +4622,7 @@ class Parser(metaclass=_Parser):
         encoding = self._match_text_seq("ENCODING") and self._parse_var()
 
         return self.expression(
-            exp.JSONObject,
+            exp.JSONObjectAgg if agg else exp.JSONObject,
             expressions=expressions,
             null_handling=null_handling,
             unique_keys=unique_keys,


Test patch:
diff --git a/tests/dialects/test_oracle.py b/tests/dialects/test_oracle.py
index 1719d304..62320e5f 100644
--- a/tests/dialects/test_oracle.py
+++ b/tests/dialects/test_oracle.py
@@ -53,6 +53,14 @@ class TestOracle(Validator):
         self.validate_identity(
             "SELECT MIN(column_name) KEEP (DENSE_RANK FIRST ORDER BY column_name DESC) FROM table_name"
         )
+        self.validate_identity(
+            """SELECT JSON_OBJECT(KEY 'key1' IS emp.column1, KEY 'key2' IS emp.column1) "emp_key" FROM emp""",
+            """SELECT JSON_OBJECT('key1': emp.column1, 'key2': emp.column1) AS "emp_key" FROM emp""",
+        )
+        self.validate_identity(
+            "SELECT JSON_OBJECTAGG(KEY department_name VALUE department_id) FROM dep WHERE id <= 30",
+            "SELECT JSON_OBJECTAGG(department_name: department_id) FROM dep WHERE id <= 30",
+        )
         self.validate_identity(
             "SYSDATE",
             "CURRENT_TIMESTAMP",