SQL query to read Array values of a Order Value column

[DineshRkumar]

Hi Team,  

In Data feed raw data, Order_value column has multiple values inside the Array.  Can you please help me with Query to sum up array. 

for instance:  

[MandyGeorge · Accepted solution]

There might be an easier way to do this, but it's been a while since I've had to do SQL regularly.

If you want to sum up the values in an array, you need to split out the values of that array, you can do that with the "SPLIT" function, which will put each value on it's own line using a delimiter. But you also have the brackets at the start/end of the array, so you need to get rid of those, I did that with the "REPLACE" function. Just replaced the bracket with an empty value.

split(replace(replace(rev_values, "[", ""), "]", ""), ",")

Once all of the values are on their own row, you can do your normal aggregations to sum.