Using Redshift I am trying to unnest whatever is being packed in a SUPER datatype into several rows (if any).
Here is a small sample of my dataset item_lines :
| group_id | item_array |
|---|---|
| 01FVPEYJB30Y68EZR41RE7NVQX | “[{“”id””:””01FVPE27NHF3PAPATE3CZRW4F9″”},{“”id””:””01FVYCFBF1R7CM60XAZ1EFDZZJ””},{“”id””:””01FVPA0EFAY3RSVV1084TNNGB7″”},{“”id””:””01FWZG5T6WSWSD8CQKWJY2G1YW””}]” |
| 01FWHQC181AMGE35VSRMKWPZK9 | |
| 01GAQWPXMP5YKFEHH91Q7A25AJ | “[{“”id””:””01FVPA0EFAY3RSVV1084TNNGB7″”},{“”id””:””01GT99RP3QC16VMABN853R1E63″”}]” |
| 01GH924X4RB0C1PWPVM9QCBQ9X |
Here is what I am trying to achieve:
|group_id|item_id|
|:—- |:——:|
|01FVPEYJB30Y68EZR41RE7NVQX|01FVPE27NHF3PAPATE3CZRW4F9|
|01FVPEYJB30Y68EZR41RE7NVQX|01FVYCFBF1R7CM60XAZ1EFDZZJ|
|01FVPEYJB30Y68EZR41RE7NVQX|01FVPA0EFAY3RSVV1084TNNGB7|
|01FVPEYJB30Y68EZR41RE7NVQX|01FWZG5T6WSWSD8CQKWJY2G1YW|
|01FWHQC181AMGE35VSRMKWPZK9||
So far, I am having no luck on achieving it. What I tried was:
SELECT
il.group_id,
item
FROM item_lines AS il, il.item_array AS item
Unfortunately, it removes the group_ids without any array, and also when I tried adding another join into the from statement, the results came blank.
Can anyone support me on that?