Repository: tobymao/sqlglot

Issue:
Length transpiled to duckdb doesn't handle json type
```
sqlglot.parse_one(
    """select length('{"ids": [1,2]}'::json)""",
    read="snowflake",
).sql(dialect="duckdb")
```
produces

```
SELECT
    CASE
        TYPEOF(CAST('{"ids": [1,2]}' AS JSON))
        WHEN 'VARCHAR' THEN LENGTH(CAST(CAST('{"ids": [1,2]}' AS JSON) AS TEXT))
        WHEN 'BLOB' THEN OCTET_LENGTH(CAST(CAST('{"ids": [1,2]}' AS JSON) AS BLOB))
    END
```

Which results in `NULL` when run in duckdb because the CASE does not handle the `JSON` type

Discussion:

Patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index b362cfea..61d8ac58 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -1032,10 +1032,10 @@ class DuckDB(Dialect):
 
             case = (
                 exp.case(self.func("TYPEOF", arg))
-                .when(
-                    "'VARCHAR'", exp.Anonymous(this="LENGTH", expressions=[varchar])
-                )  # anonymous to break length_sql recursion
                 .when("'BLOB'", self.func("OCTET_LENGTH", blob))
+                .else_(
+                    exp.Anonymous(this="LENGTH", expressions=[varchar])
+                )  # anonymous to break length_sql recursion
             )
 
             return self.sql(case)


Test patch:
diff --git a/tests/dialects/test_bigquery.py b/tests/dialects/test_bigquery.py
index def45287..1a682512 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1486,7 +1486,7 @@ WHERE
                 "snowflake": "SELECT LENGTH(foo)",
             },
             write={
-                "duckdb": "SELECT CASE TYPEOF(foo) WHEN 'VARCHAR' THEN LENGTH(CAST(foo AS TEXT)) WHEN 'BLOB' THEN OCTET_LENGTH(CAST(foo AS BLOB)) END",
+                "duckdb": "SELECT CASE TYPEOF(foo) WHEN 'BLOB' THEN OCTET_LENGTH(CAST(foo AS BLOB)) ELSE LENGTH(CAST(foo AS TEXT)) END",
                 "snowflake": "SELECT LENGTH(foo)",
                 "": "SELECT LENGTH(foo)",
             },