Repository: tobymao/sqlglot

Issue:
Postgres to Clikchouse query giving wrong key for json column.
```import sqlglot
from sqlglot import expressions as exp

# Your PostgreSQL query
postgres_query = """
SELECT a_1.varchar_10                                                                                    AS "Lead Source ",
       count(base_1.id)                                                                                  AS "Total",
       count(CASE WHEN base_1.varchar_2 <> 'Enquiry' OR base_1.varchar_2 IS NULL THEN 1 END)             AS "Touched",
       count(CASE
                 WHEN (base_1.varchar_2 <> 'Enquiry' OR base_1.varchar_2 IS NULL) AND
                      (base_1.varchar_2 <> 'Attempt to Contact' OR base_1.varchar_2 IS NULL) THEN 1 END) AS "Connected",
       count(CASE WHEN (base_1.js_short_text ->> '12') = 'Valid' THEN 1 END)                             AS "Valid"
FROM se_opportunities base_1
         LEFT JOIN se_leads a_1 ON base_1.relation_2 = a_1.id AND (a_1.org_id = 'AbdSvZ' AND a_1.is_deleted = 0 AND
                                                                   a_1.object_id = 'AbdSvZ_xb9MPfZd' AND
                                                                   a_1.object_id = 'AbdSvZ_xb9MPfZd')
WHERE (base_1.created_at > 1740594600000 AND base_1.created_at < 1741199399999)
  AND ((base_1.org_id = 'AbdSvZ' AND base_1.is_deleted = 0 AND base_1.object_id = 'AbdSvZ_pnDQ8OLx'))
GROUP BY a_1.varchar_10
ORDER BY "Total" DESC
LIMIT 100;
"""


def test_clickhouse():
    clickhouse_query = sqlglot.transpile(
        postgres_query,
        read='postgres',  # Source dialect
        write='clickhouse',  # Target dialect
        pretty=True
    )[0]

    print(clickhouse_query)


if __name__ == "__main__":
    test_clickhouse()
```

output is
```SELECT
  a_1.varchar_10 AS "Lead Source ",
  COUNT(base_1.id) AS "Total",
  COUNT(CASE WHEN base_1.varchar_2 <> 'Enquiry' OR base_1.varchar_2 IS NULL THEN 1 END) AS "Touched",
  COUNT(
    CASE
      WHEN (
        base_1.varchar_2 <> 'Enquiry' OR base_1.varchar_2 IS NULL
      )
      AND (
        base_1.varchar_2 <> 'Attempt to Contact' OR base_1.varchar_2 IS NULL
      )
      THEN 1
    END
  ) AS "Connected",
  COUNT(CASE WHEN (
    JSONExtractString(base_1.js_short_text, 13)
  ) = 'Valid' THEN 1 END) AS "Valid"
FROM se_opportunities AS base_1
LEFT JOIN se_leads AS a_1
  ON base_1.relation_2 = a_1.id
  AND (
    a_1.org_id = 'AbdSvZ'
    AND a_1.is_deleted = 0
    AND a_1.object_id = 'AbdSvZ_xb9MPfZd'
    AND a_1.object_id = 'AbdSvZ_xb9MPfZd'
  )
WHERE
  (
    base_1.created_at > 1740594600000 AND base_1.created_at < 1741199399999
  )
  AND (
    (
      base_1.org_id = 'AbdSvZ'
      AND base_1.is_deleted = 0
      AND base_1.object_id = 'AbdSvZ_pnDQ8OLx'
    )
  )
GROUP BY
  a_1.varchar_10
ORDER BY
  "Total" DESC NULLS FIRST
LIMIT 100```

here we can see in original query CASE WHEN (base_1.js_short_text ->> '12') = 'Valid' is converted to CASE WHEN (
    JSONExtractString(base_1.js_short_text, 13)
  ) = 'Valid' THEN 1 END

Discussion:
@superkashyap: here we can see in original query CASE WHEN (base_1.js_short_text ->> '12') = 'Valid' is converted to CASE WHEN (
    JSONExtractString(base_1.js_short_text, 13)
  ) = 'Valid' THEN 1 END


Solution patch:
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 690530eeea..63ba247aa9 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -992,6 +992,7 @@ class Generator(generator.Generator):
             exp.DataType.Type.BIGDECIMAL: "BIGNUMERIC",
             exp.DataType.Type.BIGINT: "INT64",
             exp.DataType.Type.BINARY: "BYTES",
+            exp.DataType.Type.BLOB: "BYTES",
             exp.DataType.Type.BOOLEAN: "BOOL",
             exp.DataType.Type.CHAR: "STRING",
             exp.DataType.Type.DECIMAL: "NUMERIC",
diff --git a/sqlglot/dialects/clickhouse.py b/sqlglot/dialects/clickhouse.py
index feb0dbc2d7..9aa811c10f 100644
--- a/sqlglot/dialects/clickhouse.py
+++ b/sqlglot/dialects/clickhouse.py
@@ -920,6 +920,7 @@ class Generator(generator.Generator):
         ARRAY_SIZE_NAME = "LENGTH"
 
         STRING_TYPE_MAPPING = {
+            exp.DataType.Type.BLOB: "String",
             exp.DataType.Type.CHAR: "String",
             exp.DataType.Type.LONGBLOB: "String",
             exp.DataType.Type.LONGTEXT: "String",
diff --git a/sqlglot/dialects/dialect.py b/sqlglot/dialects/dialect.py
index 72d31bb319..da4ec855a2 100644
--- a/sqlglot/dialects/dialect.py
+++ b/sqlglot/dialects/dialect.py
@@ -1651,7 +1651,7 @@ def _builder(args: t.List) -> F:
                 return expr_type.from_arg_list(args)
 
             text = arg.name
-            if is_int(text):
+            if is_int(text) and (not arrow_req_json_type or not arg.is_string):
                 index = int(text)
                 segments.append(
                     exp.JSONPathSubscript(this=index if zero_based_indexing else index - 1)
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index b5702b7599..b4c4ebbc24 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -486,6 +486,7 @@ class Generator(generator.Generator):
         TYPE_MAPPING = {
             **generator.Generator.TYPE_MAPPING,
             exp.DataType.Type.BIT: "BOOLEAN",
+            exp.DataType.Type.BLOB: "BINARY",
             exp.DataType.Type.DATETIME: "TIMESTAMP",
             exp.DataType.Type.ROWVERSION: "BINARY",
             exp.DataType.Type.TEXT: "STRING",
diff --git a/sqlglot/dialects/mysql.py b/sqlglot/dialects/mysql.py
index f4ef031d29..927642abb9 100644
--- a/sqlglot/dialects/mysql.py
+++ b/sqlglot/dialects/mysql.py
@@ -192,6 +192,7 @@ class Tokenizer(tokens.Tokenizer):
             "CHARSET": TokenType.CHARACTER_SET,
             # The DESCRIBE and EXPLAIN statements are synonyms.
             # https://dev.mysql.com/doc/refman/8.4/en/explain.html
+            "BLOB": TokenType.BLOB,
             "EXPLAIN": TokenType.DESCRIBE,
             "FORCE": TokenType.FORCE,
             "IGNORE": TokenType.IGNORE,
@@ -830,6 +831,7 @@ class Generator(generator.Generator):
         TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMTEXT)
         TYPE_MAPPING.pop(exp.DataType.Type.LONGTEXT)
         TYPE_MAPPING.pop(exp.DataType.Type.TINYTEXT)
+        TYPE_MAPPING.pop(exp.DataType.Type.BLOB)
         TYPE_MAPPING.pop(exp.DataType.Type.MEDIUMBLOB)
         TYPE_MAPPING.pop(exp.DataType.Type.LONGBLOB)
         TYPE_MAPPING.pop(exp.DataType.Type.TINYBLOB)
diff --git a/sqlglot/dialects/oracle.py b/sqlglot/dialects/oracle.py
index 0fd2be7c89..837f8e7380 100644
--- a/sqlglot/dialects/oracle.py
+++ b/sqlglot/dialects/oracle.py
@@ -283,6 +283,7 @@ class Generator(generator.Generator):
             exp.DataType.Type.VARBINARY: "BLOB",
             exp.DataType.Type.ROWVERSION: "BLOB",
         }
+        TYPE_MAPPING.pop(exp.DataType.Type.BLOB)
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index e19fee35d2..775617aab0 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -527,6 +527,7 @@ class Generator(generator.Generator):
             exp.DataType.Type.VARBINARY: "BYTEA",
             exp.DataType.Type.ROWVERSION: "BYTEA",
             exp.DataType.Type.DATETIME: "TIMESTAMP",
+            exp.DataType.Type.BLOB: "BYTEA",
         }
 
         TRANSFORMS = {
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index ec18327155..4bebccfd7d 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -166,6 +166,7 @@ class Generator(Postgres.Generator):
         TYPE_MAPPING = {
             **Postgres.Generator.TYPE_MAPPING,
             exp.DataType.Type.BINARY: "VARBYTE",
+            exp.DataType.Type.BLOB: "VARBYTE",
             exp.DataType.Type.INT: "INTEGER",
             exp.DataType.Type.TIMETZ: "TIME",
             exp.DataType.Type.TIMESTAMPTZ: "TIMESTAMP",
diff --git a/sqlglot/dialects/sqlite.py b/sqlglot/dialects/sqlite.py
index 73b15aad3e..c1cacd571f 100644
--- a/sqlglot/dialects/sqlite.py
+++ b/sqlglot/dialects/sqlite.py
@@ -165,6 +165,7 @@ class Generator(generator.Generator):
             exp.DataType.Type.BINARY: "BLOB",
             exp.DataType.Type.VARBINARY: "BLOB",
         }
+        TYPE_MAPPING.pop(exp.DataType.Type.BLOB)
 
         TOKEN_MAPPING = {
             TokenType.AUTO_INCREMENT: "AUTOINCREMENT",
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index 677b4ef2b3..3b74f1d40c 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -4414,6 +4414,7 @@ class Type(AutoName):
         BIGSERIAL = auto()
         BINARY = auto()
         BIT = auto()
+        BLOB = auto()
         BOOLEAN = auto()
         BPCHAR = auto()
         CHAR = auto()
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 34b93d08ef..b318826cea 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -468,6 +468,7 @@ class Generator(metaclass=_Generator):
         exp.DataType.Type.MEDIUMTEXT: "TEXT",
         exp.DataType.Type.LONGTEXT: "TEXT",
         exp.DataType.Type.TINYTEXT: "TEXT",
+        exp.DataType.Type.BLOB: "VARBINARY",
         exp.DataType.Type.MEDIUMBLOB: "BLOB",
         exp.DataType.Type.LONGBLOB: "BLOB",
         exp.DataType.Type.TINYBLOB: "BLOB",
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 65eab91397..4872adc9f2 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -327,6 +327,7 @@ class Parser(metaclass=_Parser):
         TokenType.TEXT,
         TokenType.MEDIUMTEXT,
         TokenType.LONGTEXT,
+        TokenType.BLOB,
         TokenType.MEDIUMBLOB,
         TokenType.LONGBLOB,
         TokenType.BINARY,
diff --git a/sqlglot/tokens.py b/sqlglot/tokens.py
index ddffb09add..2944debbda 100644
--- a/sqlglot/tokens.py
+++ b/sqlglot/tokens.py
@@ -142,6 +142,7 @@ class TokenType(AutoName):
     TEXT = auto()
     MEDIUMTEXT = auto()
     LONGTEXT = auto()
+    BLOB = auto()
     MEDIUMBLOB = auto()
     LONGBLOB = auto()
     TINYBLOB = auto()


Test patch:
diff --git a/tests/dialects/test_mysql.py b/tests/dialects/test_mysql.py
index 4a28176ccf..173cd7920b 100644
--- a/tests/dialects/test_mysql.py
+++ b/tests/dialects/test_mysql.py
@@ -346,6 +346,22 @@ def test_canonical_functions(self):
                 "tsql": "CHAR(10)",
             },
         )
+        self.validate_identity("CREATE TABLE t (foo VARBINARY(5))")
+        self.validate_all(
+            "CREATE TABLE t (foo BLOB)",
+            write={
+                "mysql": "CREATE TABLE t (foo BLOB)",
+                "oracle": "CREATE TABLE t (foo BLOB)",
+                "postgres": "CREATE TABLE t (foo BYTEA)",
+                "tsql": "CREATE TABLE t (foo VARBINARY)",
+                "sqlite": "CREATE TABLE t (foo BLOB)",
+                "duckdb": "CREATE TABLE t (foo VARBINARY)",
+                "hive": "CREATE TABLE t (foo BINARY)",
+                "bigquery": "CREATE TABLE t (foo BYTES)",
+                "redshift": "CREATE TABLE t (foo VARBYTE)",
+                "clickhouse": "CREATE TABLE t (foo Nullable(String))",
+            },
+        )
 
     def test_escape(self):
         self.validate_identity("""'"abc"'""")
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 44f1e397e0..f84a7fd709 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -1368,3 +1368,25 @@ def test_recursive_cte(self):
         self.validate_identity(
             "WITH RECURSIVE search_graph(id, link, data, depth) AS (SELECT g.id, g.link, g.data, 1 FROM graph AS g UNION ALL SELECT g.id, g.link, g.data, sg.depth + 1 FROM graph AS g, search_graph AS sg WHERE g.id = sg.link) CYCLE id SET is_cycle USING path SELECT * FROM search_graph"
         )
+
+    def test_json_extract(self):
+        for arrow_op in ("->", "->>"):
+            with self.subTest(f"Ensure {arrow_op} operator roundtrips int values as subscripts"):
+                self.validate_all(
+                    f"SELECT foo {arrow_op} 1",
+                    write={
+                        "postgres": f"SELECT foo {arrow_op} 1",
+                        "duckdb": f"SELECT foo {arrow_op} '$[1]'",
+                    },
+                )
+
+            with self.subTest(
+                f"Ensure {arrow_op} operator roundtrips string values that represent integers as keys"
+            ):
+                self.validate_all(
+                    f"SELECT foo {arrow_op} '12'",
+                    write={
+                        "postgres": f"SELECT foo {arrow_op} '12'",
+                        "clickhouse": "SELECT JSONExtractString(foo, '12')",
+                    },
+                )