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? :)

CodePudding user response：

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: