Preparing your workspaceloading the brief, studio & editor…

Loadingone moment…

Explode an array column into rows · Python scenario · D8LooP

Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.

CODE WORKSPACE

Explode an array column into rows

The orders feed carries the line items as an array inside each order. The warehouse's item-level table needs one row per item, so the loader explodes the array. Orders with no items — cancelled before anything was added — currently vanish, and the order count in the item table has never matched the source. Write explode_array(rows, array_field, keep_empty).

Function to write

explode_array(rows: list[dict], array_field: str, keep_empty: bool) -> list[dict]

One row per array element, with the element in place of the list and its position in an index column.

How to approach it

Decide what the cell explodes into first — list, single value, or nothing — then emit.

Sample cases

+ 2 held back until you submit

orders with their items

A two-item order, an empty list, a missing field, a bare value and an explicit null.

Input

Argument 1

[
  {
    'order_id': 'o1',
    'items': [
      'A1',
      'B2'
    ],
    'region': 'APAC'
  },
  {
    'order_id': 'o2',
    'items': [],
    'region': 'EMEA'
  },
  {
    'order_id': 'o3',
    'items': [
      'C3'
    ],
    'region': 'EMEA'
  },
  {
    'order_id': 'o4',
    'region': 'NA'
  },
  {
    'order_id': 'o5',
    'items': 'D4',
    'region': 'NA'
  },
  {
    'order_id': 'o6',
    'items': None,
    'region': 'NA'
  }
]

Argument 2

'items'

Argument 3

True

Returns

[
  {
    'order_id': 'o1',
    'items': 'A1',
    'region': 'APAC',
    'items_index': 0
  },
  {
    'order_id': 'o1',
    'items': 'B2',
    'region': 'APAC',
    'items_index': 1
  },
  {
    'order_id': 'o2',
    'items': None,
    'region': 'EMEA',
    'items_index': None
  },
  {
    'order_id': 'o3',
    'items': 'C3',
    'region': 'EMEA',
    'items_index': 0
  },
  {
    'order_id': 'o4',
    'region': 'NA',
    'items': None,
    'items_index': None
  },
  {
    'order_id': 'o5',
    'items': 'D4',
    'region': 'NA',
    'items_index': 0
  },
  {
    'order_id': 'o6',
    'items': None,
    'region': 'NA',
    'items_index': None
  }
]

dropping the empty ones

The same orders with empty arrays dropped, which is what a SQL explode does by default.

Input

Argument 1

[
  {
    'order_id': 'o1',
    'items': [
      'A1',
      'B2'
    ],
    'region': 'APAC'
  },
  {
    'order_id': 'o2',
    'items': [],
    'region': 'EMEA'
  },
  {
    'order_id': 'o3',
    'items': [
      'C3'
    ],
    'region': 'EMEA'
  },
  {
    'order_id': 'o4',
    'region': 'NA'
  },
  {
    'order_id': 'o5',
    'items': 'D4',
    'region': 'NA'
  },
  {
    'order_id': 'o6',
    'items': None,
    'region': 'NA'
  }
]

Argument 2

'items'

Argument 3

False

Returns

order_id	items	region	items_index
o1	A1	APAC	0
o1	B2	APAC	1
o3	C3	EMEA	0
o5	D4	NA	0

no rows

Nothing to explode returns an empty list rather than one placeholder row.

Input

Argument 1

[] (empty list)

Argument 2

'items'

Argument 3

True

Returns

[] (empty list)

Constraints

1A row whose array field is a list produces one output row per element, in list order, each carrying that element in place of the list.
2Each output row gains <array_field>_index, the element's position from zero.
3A row with an empty list, a null, or no array field at all explodes into nothing. When keep_empty is true, emit one row instead with both the value and the index set to None.
4A value that is not a list and not null is a single element at index 0. A producer that sends one item unwrapped means one item.
5Elements may themselves be dicts or lists. Place them as they are; do not descend.
6Keep the input row order, and keep every other field of the row unchanged.
7The generated index column replaces any existing column of that name.
8Return copies; the input rows are not modified.

Worked example

Order o1 has two items, so it becomes two rows with indexes 0 and 1 — the region and order id repeated on both. That repetition is the point: the item table is denormalised on purpose, and any aggregate over it has to count distinct orders rather than rows. Order o2 has an empty list. With keep_empty false it disappears, which is what SQL's LATERAL VIEW explode does and why the order counts never matched. With it true, o2 survives as one row with a null item — so a count of distinct orders in the item table equals the count in the source, and the cancelled orders are visible rather than merely absent.

What this tests

Controlled fan-out, and knowing that the default behaviour of every explode implementation drops the empty case. The index column is what makes the result reversible, which is the difference between a flatten and a loss.

PYTHONexplode_array(rows: list[dict], array_field: str, keep_empty: bool) -> list[dict]

AI evaluation

The reviewer reads your query.

Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.

Desktop only

This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.

Read the library instead Back to modules