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

Hints from issue comments:
@georgesittas: @tekumara the input query is not valid Snowflake:

```
snowflake> select length('{"ids": [1,2]}'::json)
Unsupported data type 'JSON'.
```

Can you provide more details here please?@tekumara: Oops that should be 

```
sqlglot.parse_one(
    """select length(parse_json('{"ids": [1,2]}'))""",
    read="snowflake",
).sql(dialect="duckdb")
```@georgesittas: Thanks, makes sense 👍

Solution patch:
diff --git a/sqlglot/dialects/duckdb.py b/sqlglot/dialects/duckdb.py
index b362cfea22..61d8ac581b 100644
--- a/sqlglot/dialects/duckdb.py
+++ b/sqlglot/dialects/duckdb.py
@@ -1032,10 +1032,10 @@ def length_sql(self, expression: exp.Length) -> str:
 
             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 def45287b9..1a682512a6 100644
--- a/tests/dialects/test_bigquery.py
+++ b/tests/dialects/test_bigquery.py
@@ -1486,7 +1486,7 @@ def test_bigquery(self):
                 "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)",
             },