Extracting Values from Array in Redshift SQL
<p>I have some arrays stored in Redshift table "transactions" in the following format:</p>

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

<p>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.</p>

<pre><code>SELECT regexp_substr(breakdown, '\[([0-9]+),([0-9]+)\]')
FROM transactions
</code></pre>

<p>but I get an error returned that says </p>

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

<p>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.</p>

<p>Thanks for your help!</p>