Details
-
Wish
-
Status: Open
-
Major
-
Resolution: Unresolved
-
1.16.0
-
None
-
None
Description
As it possible to cast varchar to map (convert_from + JSON) with convert_from or transform a varchar to array (split)
SELECT a, typeof(a), sqltypeof(a) from (SELECT CONVERT_FROM('{a : 100, b: 200}' ,'JSON') a); +-------------------+---------+--------+ | a | EXPR$1 | EXPR$2 | +-------------------+---------+--------+ | {"a":100,"b":200} | MAP | STRUCT | +-------------------+---------+--------+ SELECT a, typeof(a), sqltypeof(a)FROM (SELECT split(str,',') AS a FROM ( SELECT 'foo,bar' AS str)); +-------------------+---------+--------+ | a | EXPR$1 | EXPR$2 | +-------------------+---------+--------+ | ["foo","bar"] | VARCHAR | ARRAY | +-------------------+---------+--------+
It will be very usefull :
- to have the capacity to "cast" the MAP into VARCHAR with a "cast syntax" or with a "convert_to" possibility
Expected:SELECT a, typeof(a) ta, va, typeof(va) tva FROM ( SELECT a, CAST(a AS varchar) va FROM (SELECT CONVERT_FROM('{a : 100, b: 200}' ,'JSON') a)); +-------------------+------+-------------------+---------+ | a | ta | va | tva | +-------------------+------+-------------------+---------+ | {"a":100,"b":200} | MAP | {"a":100,"b":200} | VARCHAR | +-------------------+------+-------------------+---------+
- to have the capacity to "cast" the ARRAY into VARCHAR with a "cast syntax" or any other method
ExpectedSELECT a, sqltypeof(a) ta, va, sqltypeof(va) tva FROM ( SELECT a, CAST(a AS varchar) va FROM (SELECT split(str,',') AS a FROM ( SELECT 'foo,bar' AS str)); +-------------------+------+-------------------+---------+ | a | ta | va | tva | +-------------------+------+-------------------+---------+ | ["foo","bar"] | ARRAY| ["foo","bar"] | VARCHAR | +-------------------+------+-------------------+---------+
Please note that these possibility of course exists in other database systems
Example with Postgres:
SELECT '{"a":100,"b":200}'::json::text; => {"a":100,"b":200} SELECT array[1,2,3]::text; => {1,2,3}