Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-7375

composite/nested type map/array convert_to/cast to varchar

    XMLWordPrintableJSON

    Details

    • Type: Wish
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.16.0
    • Fix Version/s: None
    • Component/s: Functions - Drill
    • Labels:
      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 :

      1. 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 |
        +-------------------+------+-------------------+---------+
        
      1. to have the capacity to "cast" the ARRAY into VARCHAR with a "cast syntax" or any other method
        Expected
        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 |
        +-------------------+------+-------------------+---------+
        

      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}
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              benj641 benj
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: