Skip to content

Instantly share code, notes, and snippets.

@nvquanghuy
Last active January 21, 2021 07:39
Show Gist options
  • Save nvquanghuy/5faf0e201321c9873fdc92eb28e21511 to your computer and use it in GitHub Desktop.
Save nvquanghuy/5faf0e201321c9873fdc92eb28e21511 to your computer and use it in GitHub Desktop.
Unnesting strings in Redshift and MySQL
create table books (tags varchar(1000));
insert into books values
('A, B, C, D'),
('D, E'),
('F'),
('G, G, H')
;
select
TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(B.tags, ',', NS.n), ',', -1)) as tag
from (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) NS
inner join books B ON NS.n <= CHAR_LENGTH(B.tags) - CHAR_LENGTH(REPLACE(B.tags, ',', '')) + 1
create table books (tags varchar(1000));
insert into books values
('A, B, C, D'),
('D, E'),
('F'),
('G, G, H')
;
with NS AS (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
)
select
TRIM(SPLIT_PART(B.tags, ',', NS.n)) AS tag
from NS
inner join books B ON NS.n <= REGEXP_COUNT(B.tags, ',') + 1
create table books (tags varchar(1000));
insert into books values
('["A", "B", "C", "D"]'),
('["D", "E"]'),
('["F"]'),
('["G", "G", "H"]')
;
with NS AS (
select 1 as n union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
)
select
TRIM(JSON_EXTRACT_ARRAY_ELEMENT_TEXT(B.tags, NS.n - 1)) AS val
from NS
inner join books B ON NS.n <= JSON_ARRAY_LENGTH(B.tags)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment