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

Topics discussed:

  • The authors of FQL — the FHIR Query Language, from Firely — walked the group through it, rebuilding the blood pressure example the group had been using. It reads like SQL but runs over FHIR resources rather than tables: from Observation, a where, then selects that nest in brackets as you go deeper. Nothing is flattened unless you ask: a plain select of name.given hands you back an array, join turns it into rows, and fetch follows a reference into the linked resource and pulls columns out of it.
  • The structural takeaway was the important one. FQL's nested selects remove the need for a separate variables section — the group's design had one block declaring variables and another declaring columns, and FQL folds them into a single hierarchy where a select scopes everything inside it. Ryan said the semantics looked more predictable than the whenMany mechanism the group had sketched, and volunteered to translate the existing examples into a vars-free shape so they could be compared side by side.
  • The limits of alignment were stated plainly by FQL's own authors: because it allows a FHIRPath expression anywhere, it needs the whole resource in memory and cannot be fully transpiled to SQL. That is exactly the difference in ambition — the working group wants a subset small enough to compile into one query. The idea ran both ways: a view definition could compile down into FQL, or serve as a constrained AST that FQL compiles into. Meanwhile a Postgres implementation was translating FHIRPath into JSONPath and reaching for lateral joins to handle variables.
  • Why is filters a list rather than one big expression? Not for power — you can and them together and get the same thing. It's convenience: appending conjunctions one at a time while narrowing something down interactively, and attaching a description to each. Openly acknowledged as a judgement call rather than a semantic argument.
  • On dates, the leaning was a type hint per column rather than FHIRPath's toDate, partly because toDate can still hand back just a year, and partly because type hints generalise — you also want to say "give me a decimal type so there's no precision loss". Alongside it, earliest-instant and latest-instant functions, so a lab result that only has a date can be compared against an encounter that has a full timestamp without either being fudged. The argument for putting this in the view layer rather than the annotation layer: you don't always control the data source you're reading.