Splitting arrays into rows in Redshift with dbt macros


#1

Some of our data is stored like this:

id tags
1 a
2 a, b
3 c , d
4 a,b,e
5 NULL

When really we want it like this:

id tag
1 a
2 a
2 b
3 c
3 d
4 a
4 b

In postgres, you can use the UNNEST function, however, there is no equivalent in Redshift.

I took inspiration from this blogpost, and combined it with the power of dbt to create a macro that unnests lists!

I also created a macro that unnests json arrays.

Check out this gist to see the code for the macros, along with some examples (I’ve kept it as a gist since there’s multiple files).