Repository: tobymao/sqlglot

Issue:
TSQL JSON_QUERY issue
import sqlglot
sqlglot.transpile("""
SELECT
JSON_QUERY(REPLACE(REPLACE(prediction_list , '''', '"'), '""', '"'))
FROM "MyAppDB"."dbo"."predictions_v5"
""", read="tsql", write="tsql")[0]

The query already runs well on my sql server (without transpile), but throws a failure if transpile
ParseError: Required keyword: 'expression' missing for <class 'sqlglot.expressions.JSONExtract'>. Line 3, Col: 68.

Discussion:

Patch:
diff --git a/sqlglot/dialects/tsql.py b/sqlglot/dialects/tsql.py
index fd3e48cc..9659f153 100644
--- a/sqlglot/dialects/tsql.py
+++ b/sqlglot/dialects/tsql.py
@@ -322,6 +322,15 @@ def _build_with_arg_as_text(
     return _parse
 
 
+def _build_json_query(args: t.List, dialect: Dialect) -> exp.JSONExtract:
+    if len(args) == 1:
+        # The default value for path is '$'. As a result, if you don't provide a
+        # value for path, JSON_QUERY returns the input expression.
+        args.append(exp.Literal.string("$"))
+
+    return parser.build_extract_json_with_path(exp.JSONExtract)(args, dialect)
+
+
 def _json_extract_sql(
     self: TSQL.Generator, expression: exp.JSONExtract | exp.JSONExtractScalar
 ) -> str:
@@ -510,7 +519,7 @@ class TSQL(Dialect):
             "GETDATE": exp.CurrentTimestamp.from_arg_list,
             "HASHBYTES": _build_hashbytes,
             "ISNULL": exp.Coalesce.from_arg_list,
-            "JSON_QUERY": parser.build_extract_json_with_path(exp.JSONExtract),
+            "JSON_QUERY": _build_json_query,
             "JSON_VALUE": parser.build_extract_json_with_path(exp.JSONExtractScalar),
             "LEN": _build_with_arg_as_text(exp.Length),
             "LEFT": _build_with_arg_as_text(exp.Left),


Test patch:
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index 9658a02c..a9c29ed3 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -1135,6 +1135,11 @@ WHERE
         self.validate_all("ISNULL(x, y)", write={"spark": "COALESCE(x, y)"})
 
     def test_json(self):
+        self.validate_identity(
+            """JSON_QUERY(REPLACE(REPLACE(x , '''', '"'), '""', '"'))""",
+            """ISNULL(JSON_QUERY(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'), JSON_VALUE(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'))""",
+        )
+
         self.validate_all(
             "JSON_QUERY(r.JSON, '$.Attr_INT')",
             write={