[
https://issues.apache.org/jira/browse/DRILL-7375?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
benj updated DRILL-7375:
------------------------
Description:
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}
was:
As it possible to cast varchar to map (JSON) with convert_from
{code:sql}
SELECT a, typeof(a) from (SELECT CONVERT_FROM('{a : 100, b: 200}' ,'JSON') a);
+-------------------+--------+
| a | EXPR$1 |
+-------------------+--------+
| {"a":100,"b":200} | MAP |
+-------------------+--------+
{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}
Please note that these possibility of course exists in other database systems
Example with postgres:
{code:sql}
SELECT '{"a":100,"b":200}'::json::text;
SELECT (array[1,2,3])::text;
{code}
> composite type map cast/convert_to
> ----------------------------------
>
> 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)
