SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — November 14, 2023
Nov 14, 2023

Topics discussed:

  • Recursively nested data — questionnaire item.item, CodeSystem concept trees, PlanDefinition, part-of chains across Location and Organization — has no obvious home in a ViewDefinition. Four options were on the table: allow the full FHIRPath union operator; add parameters to repeat; do it with the existing unionAll structure and accept copying your column definitions between branches; or invent a dedicated structure for unioning FHIRPath statements. A fifth was floated late: make path repeating and union those. Nothing was decided — the conclusion was that more work on SQL generation has to come first, because it is guesswork to say what will survive translation until someone has tried.
  • A worthwhile correction: people write the | pipe because it is prettier syntax, without thinking about which operation it is. FHIRPath's union eliminates duplicates, which drags in full equality semantics; combine is what most of these cases actually want. Depth is the real constraint for SQL generation — one implementation's code generation recurses fine, it just has to know how deep to go before it emits SQL, and arbitrarily deep is fundamentally in conflict with a fixed table structure.
  • Three intent levels were proposed for declaring what a view needs: features labelled must-support only, all of FHIRPath except resolve, or all of FHIRPath including resolve. The objection was that the gap between level one and level two is enormous, and an 'all except this list' form might age better. It was also pointed out that memberOf reaches outside the resource just like resolve does, so it belongs in the same bucket.
  • The resolve use case had nothing to do with analytics. Clinical worklists — the list of things a nurse or radiographer might do next — refresh constantly on hundreds of screens and can cross 100,000 resources to build. What you actually want is a way for a client to tell a server, interoperably, which cross-resource joins to pre-optimise and index, and a ViewDefinition is exactly the right shape for that — except for the resolve part. The counter was that resolve only goes one way, so links pointing back at the subject need something else; Pathling built a reverse-resolve function and, having built it, was not convinced it was the right answer. The direction people leaned was something more generic outside FHIRPath — an abstract join, or a ViewDefinition referring to other ViewDefinitions.
  • Dan argued the where key is stuck at the root while everything else moved down into select, so filtering a single branch of a union means shoving the condition into a path expression instead. The counter was that root-level where earns its place precisely because it applies to all the sub-selects — push it down and you repeat yourself. Everyone was fine with it as pure syntactic sugar (an array of wheres just ANDs together) and nervous the moment subtle semantic differences appear. A separate observation: people are simulating a CASE statement with a union plus filters, and an explicit case or if would communicate intent better — a union may return several rows, a case has exactly one branch that wins.