Cannot cast to array(varchar) on presto when unnesting a column

June 28, 2022

My data on the column _idcounts is like the following:

00A=10;500=20;500=3;00e=11;001(ta)=1;

As I want to unnest this column I did my query as:

SELECT t._idcounts, anotherField
 from myDataBase
CROSS JOIN UNNEST( cast(_idcounts as array<varchar>)) AS t (_idcounts);

But I have this as error:

Failed to output to file. Query failed: Cannot cast varchar to array(varchar).

Edit:
If I remove the "array" from the "cast" command, so change to CROSS JOIN UNNEST( cast(_idcounts as varchar))
I have this error Cannot unnest type: varchar

Ideas? 🙂

>Solution :

Just split on ;:

--sample data
WITH dataset(id_str) AS (
  SELECT ('00A=10;500=20;500=3;00e=11;001(ta)=1;')
)

--query
SELECT t._idcounts
FROM dataset,
UNNEST(split(id_str,';')) AS t (_idcounts);

Output: