Convert string to array of arrays in PostgreSQL

Question

Want to 'simply' select the following string row (note that 'it is already' an array of arrays) to one of type float[][]

From:

'[[1.1, 1], [2.2, 2]]'

To:

[[1.1, 1], [2.2, 2]]

Any advice? Thanks!

Answer 1

You can simply replace the brackets with braces, and you have the string representation of the array, which you can cast to the desired data type.

SELECT CAST (replace(
                replace(
                   '[[1.1, 1], [2.2, 2]]', '[', '{'
                ), ']', '}'
             ) AS float[]
            );

      replace      
-------------------
 {{1.1,1},{2.2,2}}
(1 row)

Abelisto's version is even simpler:

SELECT translate('[[1.1, 1], [2.2, 2]]', '[]', '{}')::float[];

Here, :: is the PostgreSQL shortcut for the standard CAST syntax.