Unnesting JSON arrays in BigQuery from Sigma

Worksheet Operations
#1

While Sigma’s JSON “Extract columns” 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
#2

One way to achieve the above request as of today until we actually build this into Sigma would be to create a dataset backed by a custom SQL query which expands the particular array.

In BigQuery, we would do something like the following:

SELECT
    items.item_id,
    subitem_index,
    subitem_data
FROM items,
UNNEST(items.subitems_data) subitem_data WITH OFFSET subitem_index

You can then join the original dataset to this (be aware that you’ll get multiple rows back for each original row, so watch out for fan traps).