Unnesting JSON arrays in Redshift from Sigma

While Sigma’s JSON extractor modal allows you to pick out specific keys it doesn’t yet have an option for expanding a JSON array into multiple rows.
What options exist for dealing with such records?

1 Like

One way to achieve the above request as of today until we actually built this into Sigma would be to write a custom sql query which expands the particular array.

In Redshift we would do something like the following:

   with arr as (
      select max(json_array_length(<array_key>, True )) as arrlen, <array_key>, ....other columns

    numbers as (
      select generate_series(0, (select arrlen from arr)) as ordinal

    joined as (
      select json_extract_array_element_text(arr.items, numbers.ordinal::int, True) as item, <other_columns> from arr
      cross join numbers where numbers.ordinal < json_array_length(arr.items, True)
    select * from joined

Essentially the cross-join in combination with json_extract_array_element_text() allows us to explode the array where each array element is matched with a number generated by generate_series().

1 Like