REST JSON serializer repeats the first element of a LIST(STRUCT) column

[sipemu]

Summary

When an endpoint's SQL returns a LIST(STRUCT(...)) column, the REST JSON
response repeats the first struct N times instead of emitting the N distinct
elements. A JSON-typed column (via to_json(...)) serializes correctly, so
the bug is specific to native LIST(STRUCT) serialization.

Repro

Endpoint SQL over a JSON file whose top-level object has an array of objects:

SELECT n_input, by_stage      -- by_stage : LIST(STRUCT(stage VARCHAR, reason VARCHAR, n_dropped BIGINT))
FROM read_json('report.json');

DuckDB returns the list correctly:

[{stage: prepare, n_dropped: 11945}, {stage: features, n_dropped: 57203}, {stage: postprocess, n_dropped: 238505}]

But the flapi REST response is:

"by_stage":[{"stage":"prepare","n_dropped":11945},
            {"stage":"prepare","n_dropped":11945},
            {"stage":"prepare","n_dropped":11945}]

— correct length (3), but every element is element[0].

Workaround

SELECT to_json(by_stage) AS by_stage → flapi emits a correct JSON array
(JSON-typed column path is fine). So the defect is in the LIST(STRUCT) →
JSON conversion in the REST serializer, not the query layer.

Env

flapi-io 26.6.14 (flapi 1.0), DuckDB 1.5.3, Linux. Happy to provide a minimal
config + sample json if useful.

[sipemu]

Update: this is broader than LIST(STRUCT) — a plain LIST(VARCHAR) column is also corrupted, and differently: the serialized array for each paged row contains the concatenation of every row's list in the page (e.g. 4 rows × a 3-element list → each row reports a 12-element flags array). So no native LIST(...) column round-trips through the REST serializer. to_json(col) remains a clean workaround for both list and struct/map columns (flapi serializes a JSON-typed column correctly). Suggests the defect is in how the per-row list child vector offset/length is read during JSON emission.

[jrosskopf]

Fixed in #90 (merged to main).

Root cause

The REST JSON serializer walked DuckDB's nested-type vectors without respecting per-row layout:

• convertVectorListToJson iterated the entire list child vector (duckdb_list_vector_get_size) instead of the current row's duckdb_list_entry {offset, length} slice. So every row emitted all rows' elements concatenated — exactly the LIST(VARCHAR) "12-element array" you saw in the follow-up.
• convertVectorStructToJson always read child element 0. For a LIST(STRUCT) the list loop then produced N copies of element[0] — the original report.

to_json(col) was unaffected because that goes through the JSON-typed string path, which is why it worked as a workaround.

Fix

• LIST → emit only the row's duckdb_list_entry offset/length slice.
• STRUCT → pass row_idx through to each child vector.
• ARRAY → new serializer using duckdb_array_type_array_size + duckdb_array_vector_get_child (fixed-size arrays don't carry list-entry metadata; they were previously aliased onto the LIST path).
• UNION → new serializer that reads the row's tag and emits only the active member as {member_name: value}, matching DuckDB's own to_json (previously aliased onto the STRUCT path, which leaked every candidate member).

All four now honor row validity, so NULL nested values serialize as null.

The last two (ARRAY, UNION) were latent variants of the same bug, surfaced during a codex review of the initial fix.

Verified

New [query_executor][list] regression tests cover single/multi-row LIST(STRUCT), LIST(VARCHAR), multi-row STRUCT, fixed-size ARRAY, multi-row UNION, and NULL list entries — red before the fix, green after. Full C++ unit suite: 643/643 passing.

Ships in release v26.06.25. Thanks for the detailed repro and the follow-up note pinpointing the child-vector offset/length — that made the diagnosis straightforward. 🙏

[sipemu]

Verified fixed in 26.6.25 — LIST(STRUCT) and LIST(VARCHAR) columns now serialize as correct JSON arrays (no more repeated-first-element / cross-row concatenation). Thanks!

One related gap remains: a native MAP column still serializes to null (e.g. map_from_entries(...) → "reasons": null). to_json(map) is a fine workaround, but if MAP serialization is in scope it'd be worth covering too. Happy to file separately if you prefer.

[jrosskopf]

Good catch — thanks for verifying, and yes, we missed MAP.

You're right about the cause: a DuckDB MAP is physically LIST(STRUCT(key, value)), but it was still aliased onto the struct serializer (the one path I didn't re-route in #90), so it fell through to null.

Fixing it in #91: native MAP columns now serialize as {key: value} to match to_json — empty map → {}, NULL map → null, and non-string keys are stringified the same way DuckDB does (e.g. integer 10 → "10"). Added regression tests for string keys, integer keys, empty, and null.

No need to file separately — I'll fold it into this issue and ship it in the next release. Will update here once it's out. 🙏

[jrosskopf]

Did a full audit of every DuckDB type against the REST JSON serializer (prompted by the MAP gap). Found and fixed more — including a crash. Summary:

Fixed

Type	Was	Now	PR
LIST(...)	repeated first element / cross-row concatenation	correct per-row array	#90
STRUCT(...)	repeated row 0	correct per-row object	#90
ARRAY(...)	aliased onto LIST path	correct per-row array	#90
UNION(...)	leaked all members	active member only, {name: value}	#90
MAP(...)	null	{key: value} (matches to_json)	#91
UUID	segfault (read as string ptr; it's a 128-bit int)	canonical 8-4-4-4-12 string	#92
HUGEINT/UHUGEINT	truncated + multi-row corruption (MAX → -1)	exact decimal string (lossless)	#92
BLOB	raw bytes → invalid JSON	DuckDB blob string (\xNN)	#92
BIT	garbage	"101010" string	#92

128-bit integers are emitted as strings so values beyond 2⁶³ survive (JSON numbers lose precision above 2⁵³).

Verified already correct

All standard scalars, DATE/TIME/TIMESTAMP(+_S/_MS/_NS/TZ), DECIMAL, ENUM, INTERVAL, JSON, and deeply nested combinations (MAP of LIST, STRUCT of MAP, LIST(STRUCT(...)), etc.).

Known limitation

VARINT/BIGNUM, GEOMETRY, and VARIANT still serialize as null — their internal/extension encodings aren't safely convertible at the vector level. to_json(col) remains a clean workaround for these. (Tracked as a follow-up.)

Coverage is backed by a [query_executor] regression suite (single/multi-row, nested, NULL, and edge cases) — 644 unit assertions green.

All of this ships in the next release (v26.06.27). Thanks again for pushing on this — the MAP report is what triggered the broader audit. 🙏