The first draft spec, and why two unnests next to each other broke everyone — May 19, 2023

The first draft — three layers

Ryan turned months of discussion into an actual draft spec, filling gaps with his own interpretation where the group hadn't been clear.

Three layers:

• Annotation layer — a lossless representation of FHIR
• View layer — the group's real work
• Analytics layer — where users bring whatever tooling they like

The request back was examples — ideally shaped like tests, with NDJSON in and CSV out, so implementers can run them as a suite rather than read them as prose.

The unnesting rules broke immediately

John wrote test cases against a Spark implementation and the unnesting rules broke immediately.

• Unnesting name and then name.given is uncontroversial: one row per given name
• Unnesting name and maritalStatus.coding, which have nothing to do with each other, cross-joined in his implementation and is supposed to be an error in the spec

He couldn't work out where to put the cross marker to get the behaviour he wanted, which is a fair sign the design isn't right yet.

Josh's rule made it consistent

Josh Mandel

Chief Architect for Microsoft Healthcare, Chief Architect for SMART Health IT

An unnest must start from a previously declared variable, so a bare expression that happens to return an array is an error rather than a silent cross join.

He was upfront that internally consistent and good ergonomics are different things. The alternative floated was nesting the definitions so sub-expressions go deeper into the tree, with the join condition implied by where you are.

Should cross joins exist at all?

The use case offered — which pairs of components co-occur in an observation — came with an honest admission of fishing for one.

The argument for cutting them: build two views and cross join them in SQL, which SQL is good at.

The argument for keeping them but making them explicit: a view is written once and used hundreds of times, so a few more keystrokes to say yes, I meant to cross join is cheap — and the alternative is somebody getting a surprise.

Where do you enforce that a column is flat?

Not in the view. A view may return a complex type — genuinely useful when you're poking at data interactively — and it's the runner's job to refuse it: a CSV runner errors, a runner that can render nested structures doesn't.

Putting a mode on the view would mean maintaining two definitions of the same table for the two runners.