Presto Unnest varchar array field with {}

Question

I have a column with inconsistent data format, some of them are a list of array [], some of them are JSON_like objects {}

id	prices
1	[100,100,110]
2	{200,210,190}

create table test(id integer, prices varchar(255));
insert into test 
values
(1,'[100,100,110]'),
(2,'{200,210,190}');

When I tried to unnest, my query works fine for the first row, but it fails on the second row. Is there a way I can convert the {} to a list of array []?

This is my query:

select id,prices,price from test 
cross join UNNEST(cast(json_parse(prices) as array<varchar>)) as t (price)

Answer 1

You can use replace and then parse the data into array:

select json_parse(replace(replace('{200,210,190}', '}', ']'), '{', '['))

Output:

_col0
[200,210,190]