Extracting Values from Array in Redshift SQL

Question

I have some arrays stored in Redshift table "transactions" in the following format:

id, total, breakdown
1, 100, [50,50]
2, 200, [150,50]
3, 125, [15, 110]
...
n, 10000, [100,900]

Since this format is useless to me, I need to do some processing on this to get the values out. I've tried using regex to extract it.

SELECT regexp_substr(breakdown, '\[([0-9]+),([0-9]+)\]')
FROM transactions

but I get an error returned that says

Unmatched ( or \(
Detail: 
-----------------------------------------------
error:  Unmatched ( or \(
code:      8002
context:   T_regexp_init
query:     8946413
location:  funcs_expr.cpp:130
process:   query3_40 [pid=17533]
--------------------------------------------

Ideally I would like to get x and y as their own columns so I can do the appropriate math. I know I can do this fairly easy in python or PHP or the like, but I'm interested in a pure SQL solution - partially because I'm using an online SQL editor (Mode Analytics) to plot it easily as a dashboard.

Thanks for your help!

Answer 1

If breakdown really is an array you can do this:

select id, total, breakdown[1] as x, breakdown[2] as y
from transactions;

If breakdown is not an array but e.g. a varchar column, you can cast it into an array if you replace the square brackets with curly braces:

select id, total, 
       (translate(breakdown, '[]', '{}')::integer[])[1] as x,
       (translate(breakdown, '[]', '{}')::integer[])[2] as y
from transactions;

Answer 2

You can try this :

SELECT REPLACE(SPLIT_PART(breakdown,',',1),'[','') as x,REPLACE(SPLIT_PART(breakdown,',',2),']','') as y FROM transactions;

I tried this with redshift db and this worked for me.

Detailed Explanation:

• SPLIT_PART(breakdown,',',1) will give you [50.
• SPLIT_PART(breakdown,',',2) will give you 50].
• REPLACE(SPLIT_PART(breakdown,',',1),'[','') will replace the [ and will give just 50.
• REPLACE(SPLIT_PART(breakdown,',',2),']','') will replace the ] and will give just 50.