I have a redshift table with a column of super object data type. it contains array list values such as this :
"[["SN", "Month", "1st", "5th", "10th", "15th", "20th", "25th", "30th", "Monthly"], [1, "Apr 2023", 4412.39, 4535.39, 2061.37, 1722.37, 1263.87, 375.76, 58478.42, 6083.96], [2, "May 2023", 35378.42, 31661.67, 26926.67, 23084.58, 20711.16, 16820.18, 87310.88, 39704.02], [3, "Jun 2023", 64039.89, 58800.98, 53679.57, 50715.57, 39428.21, 184063.46, 226433.47, 94967.87]]"
It has the list of headers comma seperated and then data for each month upto n number of months in all rows depending on the id for each row. (the rows are grouped by id and in the column value for each id may contain n number of records in single row in the format mentioned above.)
I need to extract this data excluding headers and divide it into multiple columns and rows based on the records.
Can anyone help me with how can I achieve this?
table looks like this :
id | balance
123 | "[["SN", "Month", "1st", "5th", "10th", "15th", "20th", "25th", "30th", "Monthly"], [1, "Apr 2023", 4412.39, 4535.39, 2061.37, 1722.37, 1263.87, 375.76, 58478.42, 6083.96], [2, "May 2023", 35378.42, 31661.67, 26926.67, 23084.58, 20711.16, 16820.18, 87310.88, 39704.02], [3, "Jun 2023", 64039.89, 58800.98, 53679.57, 50715.57, 39428.21, 184063.46, 226433.47, 94967.87]]"
I need the output as such :
id | SN | Month | 1st | 5th | ....| 30th | Monthly
123 | 1 | April 2023 | 4412.39 | 4535.39 | .... | 58478.42 | 6083.96
123 | 2 | May 2023 | 35378.42 | 31661.67 | ..... | 87310.88 | 39704.02
.... and so on
I need them extracted into seperate rows and columns like above.