I Have a table something like :

i want o/p as

Column array_string is stored as a string and not a super or json type data type and it can consist of any number of elements (for example, here i have used an array with only three-element) I tried split_part, string_to_array but was still unable to solve the problem.

Any help will be really appreciated.

CodePudding user response：

This is a Postgres solution that may not work on Redhsift or may need to be touched.
Here is an illustration how to expand your string:

with t(a) as
(
  select jsonb_array_elements(regexp_replace(
    '[[h1,r1],[h2,r2],[h3,r3]]', '\y', '"', 'g')::jsonb)
)
select a->>0 col_h, a->>1 col_r from t;

The whole query will be

select t.id, t.col2, t.col_3, t.col_4, l.*
from the_table t
cross join lateral
(
 with t2(a) as
 (
  select jsonb_array_elements(regexp_replace(t.array_string,'\y','"','g')::jsonb)
 )
 select a->>0 col_h, a->>1 col_r from t2
) l;

CodePudding user response：

You will need to convert the string to a "super" data type using json_parse() - https://docs.aws.amazon.com/redshift/latest/dg/JSON_PARSE.html

And then unnest the array using PartiQL syntax now supported for this process - https://docs.aws.amazon.com/redshift/latest/dg/query-super.html

You case looks to be nearly identical to some of the examples so I won't repeat them here.