SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — May 19, 2023
May 19, 2023

Topics discussed:

  • Ryan turned months of discussion into an actual draft spec, filling gaps with his own interpretation where the group hadn't been clear. It names three layers: an annotation layer holding a lossless representation of FHIR, the view layer that is the group's real work, and an 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.
  • 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 — and 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: 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.