Is there a way to separate values like "csv" strings on multiple columns on Redshift SQL?

Question

For example: if I do a select preferences from stores I get this outcome:

|preferences                                                           |
|----------------------------------------------------------------------|
|"debit_rate"=>"0.00", "credit_rate_1"=>"0.01", "credit_rate_2"=>"0.02"|
|"debit_rate"=>"0.03", "credit_rate_1"=>"0.04", "credit_rate_2"=>"0.05"|
|"debit_rate"=>"0.06", "credit_rate_1"=>"0.07", "credit_rate_2"=>"0.08"|
|"debit_rate"=>"0.09", "credit_rate_1"=>"0.10", "credit_rate_2"=>"0.11"|

Is there a way for me to get this outcome?

debit_rate	credit_rate_1	credit_rate_2
0.00	0.01	0.02
0.03	0.04	0.05
0.06	0.07	0.08
0.09	0.10	0.11

Answer 1

It looks like you are small change from making these sting into valid json. redshift has json functions that will allow for more intelligent parsing of these strings. See https://docs.aws.amazon.com/redshift/latest/dg/json-functions.html

If you just change the '=>' to ':' and wrap the whole thing in curly braces '{}' you should be there.

Then you can cast these strings to be type SUPER and access the data by key value. See: https://docs.aws.amazon.com/redshift/latest/dg/query-super.html