[ 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 (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} > 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)