presto sql get string of elements to get first and second items

Question

Have a table which has a column of string type and the data looks like

items
['apple','pear','orange']
['weather','news']
.....

How could I get the first and second items as separated columns

expected output

first_item     second_item
apple          pear
weather        news

tried to cast column as array then slice it but failed

cast(itmes as array(varchar))

got Cannot cast varchar to array(varchar) Any suggestions?

Answer 1

Depending on actual data you can try treating it as JSON:

-- sample data
WITH dataset(items) AS (
   values ('["apple","pear","orange"]'),
    ('["weather","news"]')
)

-- query
select cast(json_parse(items) as array(varchar))[1] first_item, -- element_at
       cast(json_parse(items) as array(varchar))[2] second_item -- element_at
from dataset;

Output:

first_item	second_item
apple	pear
weather	news

Or just:

-- query
select json_extract_scalar(items, '$[0]') first_item,
       json_extract_scalar(items, '$[1]') second_item
from dataset;