SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — July 14, 2023
Jul 14, 2023

Topics discussed:

  • Almost any meaningful FHIR query needs value sets, so everyone agreed they need first-class support. The opening proposal was a value-set keyword in from, so you stop unioning a dozen observations one code at a time. Then it emerged that FHIRPath already has a memberOf function that does the job, and the question became whether that is the right way to express it rather than whether to support it at all.
  • A demo showed memberOf compiling down to nothing more exotic than a where code in (...) against a flat table of expanded value sets — value set URI, version, system, code, one row per concept. Query optimisers narrow it down well enough, though the implementation will vary wildly by tech stack.
  • That raised whether the expansion table's schema should be standardised. Someone asked the obvious question: isn't that below the abstraction barrier, since all the spec really needs is memberOf? Fundamentally yes — but several implementers would benefit from agreeing one shape, so it would be a 'may', never a 'must'. The nicer idea was to express that flattening as a ViewDefinition over a FHIR ValueSet: dogfooding, and a real test of the spec.
  • The group deliberately scoped this to simple 'is this code in this set' checks and pushed subsumption and is-a hierarchies to a later phase. OMOP's terminology tables came up as a precedent worth a look — four or five tables, publicly documented, non-proprietary, and published ready to load rather than assembled by hand.
  • Dan argued that where can currently appear in three places — a where key, a .where() inside from, and inside a select column — and that having to look in two places to work out what a query returns is a trap. A where on one column is the murkiest: if it filters nothing out, what happens to the other columns on that row? The pushback was that you cannot ban .where() from FHIRPath when the existing draft already leans on it, and filtering right where you pull a value out (the first home phone number) is genuinely less verbose than wrapping it in another forEach. Left open pending concrete examples.