[
https://issues.apache.org/jira/browse/DRILL-7375?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
benj updated DRILL-7375:
------------------------
Summary: composite/nested type map/array convert_to/cast to varchar (was:
composite type map cast/convert_to)
> composite/nested type map/array convert_to/cast to varchar
> ----------------------------------------------------------
>
> Key: DRILL-7375
> URL: https://issues.apache.org/jira/browse/DRILL-7375
> Project: Apache Drill
> Issue Type: Wish
> Components: Functions - Drill
> Affects Versions: 1.16.0
> Reporter: benj
> Priority: Major
>
> As it possible to cast varchar to map (convert_from + JSON) with convert_from
> or transform a varchar to array (split)
> {code:sql}
> 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 |
> +-------------------+---------+--------+
> {code}
> 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:
> {code:sql}
> 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 |
> +-------------------+------+-------------------+---------+
> {code}
> # to have the capacity to "cast" the +_ARRAY_ into VARCHAR+ with a "cast
> syntax" or any other method
> Expected
> {code:sql}
> SELECT 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 |
> +-------------------+------+-------------------+---------+
> {code}
> Please note that these possibility of course exists in other database systems
> Example with Postgres:
> {code:sql}
> SELECT '{"a":100,"b":200}'::json::text;
> => {"a":100,"b":200}
> SELECT array[1,2,3]::text;
> => {1,2,3}
> {code}
--
This message was sent by Atlassian Jira
(v8.3.2#803003)
