# Splitting arrays into rows in Redshift with dbt macros

**URL:** <https://discourse.getdbt.com/t/splitting-arrays-into-rows-in-redshift-with-dbt-macros/19>\
**Category:** Archive\
**Tags:** jinja, redshift\
**Created:** [April 19, 2018, 3:40am UTC](https://discourse.getdbt.com/t/splitting-arrays-into-rows-in-redshift-with-dbt-macros/19 "2018-04-19T03:40:56Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![claire](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/claire/32/522_2.png) [@claire](https://discourse.getdbt.com/u/claire)\
**Post date:** [April 19, 2018, 3:40am UTC](https://discourse.getdbt.com/t/splitting-arrays-into-rows-in-redshift-with-dbt-macros/19/1 "2018-04-19T03:40:56Z")

</div>

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](https://blog.holistics.io/splitting-array-string-into-rows-in-amazon-redshift-or-mysql/), 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](https://gist.github.com/clrcrl/58062fbbe2af91cceab0e9187a510101) to see the code for the macros, along with some examples (I’ve kept it as a gist since there’s multiple files).

---

<div class="post-metadata">

**Author:** ![tristan](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/tristan/32/15_2.png) [@tristan](https://discourse.getdbt.com/u/tristan)\
**Post date:** [May 14, 2019, 1:57pm UTC](https://discourse.getdbt.com/t/splitting-arrays-into-rows-in-redshift-with-dbt-macros/19/2 "2019-05-14T13:57:58Z")

</div>

I just wrote a whole blog post on [unnesting arrays in Redshift](https://blog.fishtownanalytics.com/how-to-unnest-arrays-in-redshift-d4fd15f87656) after having to do this for a client project. It walks through the entire process, including all of the functions required. Hopefully attempts to build some intuitions as well as just sharing the code.

I actually specifically chose not to macro-ize this code. In my specific situation I only had to do this once within an entire project and hopefully you don’t have excessive use of semi-structured data in Redshift either. Given that, i found that the complexity of the macro version would have actually been greater than simply writing the SQL to do the thing. Obviously, dbt-utils’ `generate_series` is still super-useful 🙂

Hopefully a good resource for folks who have to do this for the first time.