Lateral flatten/unnest JSON arrays in Snowflake from Sigma

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?

One way to achieve the above request is to create a new dataset backed by a custom SQL query.

In Snowflake, we would do something like the following:

SELECT
  items.id item_id,
  subitems.index subitem_index,
  subitems.value subitem_value
FROM items,
LATERAL FLATTEN(input => items.subitems) subitems

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 accidentally duplicating data.