CODEWITHSUNDEEP
sqlamazon-redshift
Atif
Atif

Reputation: 1142

How to explode an string which is basically an array of arrays and store it in separate rows in redshift?

I Have a table something like :

id col2 col_3 col_4 array_string
1 .. .. .. [[h1,r1],[h2,r2],[h3,r3]]

i want o/p as

id col2 col_3 col_4 col_h col_r
1 .. .. .. h1 r1
1 .. .. .. h2 r2
1 .. .. .. h3 r3

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.

Upvotes: 0

Views: 3408

Answers (2)

Bill Weiner
Bill Weiner

Reputation: 11032

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.

Upvotes: 1

Stefanov.sm
Stefanov.sm

Reputation: 13039

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;
col_h col_r
h1 r1
h2 r2
h3 r3

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;

Upvotes: 2

Related Questions