CODEWITHSUNDEEP
sqlpostgresql
AdriSolid
AdriSolid

Reputation: 2825

Convert string to array of arrays in PostgreSQL

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!

Upvotes: 1

Views: 3523

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246493

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.

Upvotes: 6

Related Questions