# Nest the matching rows in a column

Join / keyed

A variant of [Join tables on a key](https://meridian.online/operations/join-tables-on-a-key).

Join on equal keys and put each row's matches from the other table into one nested-table column.

Page: <https://meridian.online/operations/join-tables-on-a-key/nest-the-matching-rows-in-a-column>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

nest the matching rows in a column

```sql
CREATE TEMP TABLE l AS SELECT * FROM (VALUES
  (1, 'a', 'l1'), (2, 'a', 'l2'), (3, 'b', 'l3'), (4, NULL, 'l4'), (5, 'z', 'l5')) t(id, k, lv);
CREATE TEMP TABLE r AS SELECT * FROM (VALUES
  ('a', 'r1'), ('a', 'r2'), ('b', 'r3'), (NULL, 'r4'), ('y', 'r5')) t(k, rv);

-- one row for each left row; its matches are a list of structs in one column, empty where nothing matches
CREATE TEMP TABLE result AS
SELECT l.id, l.k, l.lv, coalesce(list(r ORDER BY r.rv) FILTER (WHERE r.k IS NOT NULL), []) AS matches
FROM l LEFT JOIN r ON l.k = r.k GROUP BY l.id, l.k, l.lv;
SELECT * FROM result ORDER BY id;
SELECT CASE WHEN ((SELECT count(*) = 5 AND list(len(matches) ORDER BY id) = [2, 2, 1, 0, 0] FROM result)
  AND (SELECT matches FROM result WHERE id = 1) = [{'k': 'a', 'rv': 'r1'}, {'k': 'a', 'rv': 'r2'}]
  AND (SELECT matches FROM result WHERE id = 4) = []
  AND (SELECT typeof(matches) FROM result LIMIT 1) = 'STRUCT(k VARCHAR, rv VARCHAR)[]') THEN 'as expected' ELSE error('the result should keep one row for each of the 5 left rows, with 2, 2, 1, 0 and 0 matches in a list of structs, and the NULL-key row should match nothing') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- Power Query M: [`Table.AddJoinColumn`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#transformation) (read 2026-09-27)
- Power Query M: [`Table.NestedJoin`](https://learn.microsoft.com/en-us/powerquery-m/table-functions#transformation) (read 2026-09-27)