Hi, need help with some syntax.
Currently we have a column of type text[]. It is a 1 dimensional array of text values. Each of them we have formatted in such a way that it can be split into two values. We are trying to convert it into a two dimensional array but are unsure how to do that.
i.e. converting:
{a^^val1, b^^val2, c^^val3}
to
{{a,val1}, {b,val2}, {c,val3}}
I can do
Select array(select '{"' || replace(unnest(col1), '^^', '","') || '"}') from table;
But that still gives me one dimensional text array. I can't cast it to text[] because there are no array of arrays. How do I get this into a 2 dimensional array?