Repository: tobymao/sqlglot

Issue:
clickhouse: support the new json dynamic type resolution syntax
There's a new syntax `.:` that works like this:
```
create table t (data JSON) engine = MergeTree order by tuple()

insert into t values ('{"a": {"b": "foo"}}')
insert into t values ('{"a": {"b": 10}}')

select data.a.b.:Int32 from t
select data.a.b.:String from t
```

I will try to get a PR soon.

Discussion:
@georgesittas: Hey @pkit 👋
Are you actively looking into this? I could take a quick look if not.@pkit: @georgesittas 
I will have time for it on Monday only it seems.
So if you can look into it sooner, it will be appreciated!@pkit: Just FYI, the docs are [here](https://clickhouse.com/docs/sql-reference/data-types/newjson#reading-json-paths-as-sub-columns)
But it's just a one-liner. Unfortunately the new `JSON` columns are unusable in practice without that "nullable cast" syntax.

Solution patch:
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index 72cec31ecd..42cbaede98 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -218,6 +218,7 @@ class Tokenizer(tokens.Tokenizer):
 
         KEYWORDS = {
             **tokens.Tokenizer.KEYWORDS,
+            ".:": TokenType.DOTCOLON,
             "ATTACH": TokenType.COMMAND,
             "DATE32": TokenType.DATE32,
             "DATETIME64": TokenType.DATETIME64,
@@ -1008,6 +1009,7 @@ class Generator(generator.Generator):
             exp.Explode: rename_func("arrayJoin"),
             exp.Final: lambda self, e: f"{self.sql(e, 'this')} FINAL",
             exp.IsNan: rename_func("isNaN"),
+            exp.JSONCast: lambda self, e: f"{self.sql(e, 'this')}.:{self.sql(e, 'to')}",
             exp.JSONExtract: json_extract_segments("JSONExtractString", quoted_index=False),
             exp.JSONExtractScalar: json_extract_segments("JSONExtractString", quoted_index=False),
             exp.JSONPathKey: json_path_key_only_name,
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 920d4344cb..078cd953b8 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -5564,6 +5564,11 @@ class TryCast(Cast):
     pass
 
 
+# https://clickhouse.com/docs/sql-reference/data-types/newjson#reading-json-paths-as-sub-columns
+class JSONCast(Cast):
+    pass
+
+
 class Try(Func):
     pass
 
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index f078fccdd7..dbfc48d8b0 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -3518,6 +3518,9 @@ def sub_sql(self, expression: exp.Sub) -> str:
     def trycast_sql(self, expression: exp.TryCast) -> str:
         return self.cast_sql(expression, safe_prefix="TRY_")
 
+    def jsoncast_sql(self, expression: exp.JSONCast) -> str:
+        return self.cast_sql(expression)
+
     def try_sql(self, expression: exp.Try) -> str:
         if not self.TRY_SUPPORTED:
             self.unsupported("Unsupported TRY function")
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index f267b63787..e8a2e3a183 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -735,6 +735,11 @@ class Parser(metaclass=_Parser):
 
     COLUMN_OPERATORS = {
         TokenType.DOT: None,
+        TokenType.DOTCOLON: lambda self, this, to: self.expression(
+            exp.JSONCast,
+            this=this,
+            to=to,
+        ),
         TokenType.DCOLON: lambda self, this, to: self.expression(
             exp.Cast if self.STRICT_CAST else exp.TryCast,
             this=this,
@@ -5318,7 +5323,7 @@ def _parse_column_ops(self, this: t.Optional[exp.Expression]) -> t.Optional[exp.
             op_token = self._prev.token_type
             op = self.COLUMN_OPERATORS.get(op_token)
 
-            if op_token == TokenType.DCOLON:
+            if op_token in (TokenType.DCOLON, TokenType.DOTCOLON):
                 field = self._parse_dcolon()
                 if not field:
                     self.raise_error("Expected type")
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index 0f40ccd891..afbbe285f4 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -37,6 +37,7 @@ class TokenType(AutoName):
     DASH = auto()
     PLUS = auto()
     COLON = auto()
+    DOTCOLON = auto()
     DCOLON = auto()
     DQMARK = auto()
     SEMICOLON = auto()


Test patch:
diff --git a/tests/dialects/test_clickhouse.py b/tests/dialects/test_clickhouse.py
index 1f541df949..80c1891f93 100644
--- a/tests/dialects/test_clickhouse.py
+++ b/tests/dialects/test_clickhouse.py
@@ -29,6 +29,8 @@ def test_clickhouse(self):
         self.assertEqual(expr.sql(dialect="clickhouse"), "COUNT(x)")
         self.assertIsNone(expr._meta)
 
+        self.validate_identity("SELECT json.a.:Int64")
+        self.validate_identity("SELECT json.a.:JSON.b.:Int64")
         self.validate_identity("WITH arrayJoin([(1, [2, 3])]) AS arr SELECT arr")
         self.validate_identity("CAST(1 AS Bool)")
         self.validate_identity("SELECT toString(CHAR(104.1, 101, 108.9, 108.9, 111, 32))")