forEach lands as inner join, and three implementations converge — Jun 16, 2023

Postgres implementation surfaces an unanswered question

A Postgres implementation now saves a ViewDefinition as a FHIR resource and materialises it into a real database view you can then query.

Building it surfaced a question the spec hadn't answered: when you expand a nested element that isn't there, does the row disappear or survive with nulls? Both have uses — an inner join doubles as a filter, so you don't repeat the condition, but FHIR is full of optional elements you'd rather see as null than lose the whole patient over.

forEach as inner, and the outer-join keyword nobody could name

The leaning was inner join as the default for forEach, and two distinct keywords rather than one keyword with a modifier — fewer fields, and forEach already reads as inner to people.

Nobody could name the other one. unroll, expand, flatten, forEachOuter were all floated, none stuck, and the group settled for putting it to a vote on Zulip.

The related distinction that makes arrays coherent: forEach explicitly unrolls a row per item, while selecting address.city without one simply gives you an array of cities — different keywords, no conflict.

Three implementations converging by very different routes

That was taken as the real signal:

• Postgres — translates forEach into lateral joins
• BigQuery — emits UNNEST expressions joined inline
• Pathling — explodes everything into a cross join with a generated ID per repeating element and then filters down, because in Spark you want to leave the optimiser a clear path back to the source columns

Same semantics, unrecognisably different SQL.

Views deliberately do not join

One resource in, zero or many rows out. The group explored joins at this layer, found they got complicated and hard to port very quickly, and punted. Joins belong in the analytics layer, on top of the flat views, using whatever the user already has.

A second layer of view-on-view, DBT-style, was floated for later — along with the observation that a DBT community or a Tableau community could settle its own conventions on top without the spec mandating anything.

An idea worth its own thread — compile CQL down into views

Analyse a set of measures, notice that they only touch a small subset of FHIR, generate the flat views they need, then rewrite the CQL to point at the flattened columns and let SQL do the rest — no FHIR storage required, and the data could even come from a legacy schema.

The point back from the CQL side: CQL is already model-agnostic — most CQL in the world targets QDM, not FHIR — so you could declare a model for the flattened schema and write CQL against it directly.