Repository: tobymao/sqlglot

Issue:
Support OBJECT_CONSTRUCT_KEEP_NULL (Snowflake)
A [Snowflake OBJECT_CONSTRUCT_KEEP_NULL](https://docs.snowflake.com/en/sql-reference/functions/object_construct_keep_null#examples) example, where the key is NULL:

```sql
SELECT OBJECT_CONSTRUCT_KEEP_NULL('key_1', 'one', NULL, 'two') AS KEEP_NULL_2
{\n "key_1": "one"\n}
```


which maps to JSON_OBJECT in duckdb, eg:

```sql
SELECT JSON_OBJECT('key_1', 'one', NULL, 'two') AS KEEP_NULL_2
{"key_1":"one"}
```

Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index 230e529f..d89ac5f9 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -17,6 +17,7 @@ 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,
@@ -349,11 +350,12 @@ class DuckDB(Dialect):
             exp.IntDiv: lambda self, e: self.binary(e, "//"),
             exp.IsInf: rename_func("ISINF"),
             exp.IsNan: rename_func("ISNAN"),
+            exp.JSONBExtract: arrow_json_extract_sql,
+            exp.JSONBExtractScalar: arrow_json_extract_scalar_sql,
             exp.JSONExtract: arrow_json_extract_sql,
             exp.JSONExtractScalar: arrow_json_extract_scalar_sql,
             exp.JSONFormat: _json_format_sql,
-            exp.JSONBExtract: arrow_json_extract_sql,
-            exp.JSONBExtractScalar: arrow_json_extract_scalar_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/snowflake.py b/sqlglot/dialects/snowflake.py
index 6b5f71b3..f6bc55d5 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -14,6 +14,7 @@ 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,
@@ -445,6 +446,7 @@ class Snowflake(Dialect):
         FUNCTION_PARSERS = {
             **parser.Parser.FUNCTION_PARSERS,
             "DATE_PART": _parse_date_part,
+            "OBJECT_CONSTRUCT_KEEP_NULL": lambda self: self._parse_json_object(),
         }
         FUNCTION_PARSERS.pop("TRIM")
 
@@ -694,6 +696,8 @@ 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"),
             exp.Map: lambda self, e: var_map_sql(self, e, "OBJECT_CONSTRUCT"),


Test patch:
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index f42a3315..5de56573 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -174,6 +174,18 @@ WHERE
             "CAST(x AS VARCHAR)",
         )
 
+        self.validate_all(
+            "OBJECT_CONSTRUCT_KEEP_NULL('key_1', 'one', 'key_2', NULL)",
+            read={
+                "bigquery": "JSON_OBJECT(['key_1', 'key_2'], ['one', NULL])",
+                "duckdb": "JSON_OBJECT('key_1', 'one', 'key_2', NULL)",
+            },
+            write={
+                "bigquery": "JSON_OBJECT('key_1', 'one', 'key_2', NULL)",
+                "duckdb": "JSON_OBJECT('key_1', 'one', 'key_2', NULL)",
+                "snowflake": "OBJECT_CONSTRUCT_KEEP_NULL('key_1', 'one', 'key_2', NULL)",
+            },
+        )
         self.validate_all(
             "SELECT * FROM example TABLESAMPLE (3) SEED (82)",
             read={