Introduction

When a ksqlDB stream contains an ARRAY<STRUCT> field, the usual question is whether you need one specific element or one output row per element. Once that distinction is clear, the access pattern becomes much easier: index into the array for one element, or explode the array when each struct should be treated as its own record.

Start from the Declared Schema

Suppose the source stream contains an order with an array of item structs.

1CREATE STREAM orders (
2  order_id VARCHAR,
3  customer VARCHAR,
4  items ARRAY<STRUCT<item_id VARCHAR, name VARCHAR, quantity INT>>
5) WITH (
6  KAFKA_TOPIC='orders',
7  VALUE_FORMAT='JSON'
8);

The items column is not a flat value. It is an array where each element is itself a struct. That means nested access usually happens in two stages: select an array element, then read a field from that struct.

Access One Known Element by Index

If you only care about one item position, index into the array and then project the desired field.

SELECT items[1]->name AS second_item_name
FROM orders
EMIT CHANGES;

This pattern is useful when the schema guarantees that a certain position has a specific meaning. In most event streams, though, array positions are data-dependent, so direct indexing is more limited than it first appears.

Flatten the Array with EXPLODE

When you need to process each array element independently, flatten the array into one row per struct.

1SELECT order_id,
2       EXPLODE(items) AS item
3FROM orders
4EMIT CHANGES;

After that, the alias item behaves like a struct column whose fields can be projected normally.

1SELECT order_id,
2       item->item_id AS item_id,
3       item->name AS item_name,
4       item->quantity AS quantity
5FROM order_items
6EMIT CHANGES;

This is the pattern to use when later steps need filtering, aggregation, or joins at the item level.

Build an Intermediate Stream for Clarity

Many nested queries become much easier to maintain if you split them into stages.

1CREATE STREAM order_items AS
2SELECT order_id,
3       EXPLODE(items) AS item
4FROM orders
5EMIT CHANGES;

Now downstream queries can work with the flatter shape instead of repeating nested expressions everywhere.

1SELECT order_id,
2       item->name AS item_name,
3       item->quantity AS quantity
4FROM order_items
5WHERE item->quantity >= 2
6EMIT CHANGES;

This two-step approach is often easier to debug because you can inspect the intermediate stream on its own.

Filter and Aggregate After Flattening

Once the array elements have become rows, normal stream logic becomes easier.

1SELECT item->name AS item_name,
2       SUM(item->quantity) AS total_qty
3FROM order_items
4WINDOW TUMBLING (SIZE 5 MINUTES)
5GROUP BY item->name
6EMIT CHANGES;

Trying to aggregate directly inside a nested array shape is much harder to read and reason about. Flattening early is often the better modeling choice.

Debug Schema Problems First

Nested access failures are often schema problems rather than query problems. If a field cannot be read, verify:

• the stream schema matches the incoming payload
• the array really contains structs, not plain strings or maps
• the field names inside the struct are correct
• the chosen value format deserializes nested data as expected

If the schema and payload do not align, no amount of query tweaking will fix the result.

Common Pitfalls

A common mistake is trying to access items->name directly even though items is an array, not a struct. You need indexing or explosion first.

Another is overusing direct indexing in data where array length and order vary between records. That usually creates brittle queries.

Teams also tend to write one very dense query when a small intermediate stream would be much clearer and easier to debug.

Summary

• For ARRAY<STRUCT>, decide whether you need one element or one row per element.
• Use indexing when you truly want a specific position.
• Use EXPLODE when each struct should become its own output row.
• Create an intermediate flattened stream when downstream logic is easier on a row-wise shape.
• Check schema alignment first when nested field access fails.