SQL on FHIR WG Meetings

Writing views down — arrays or extra rows, JSON or StructureDefinition — Apr 21, 2023

Josh Mandel
Josh Mandel
Chief Architect for Microsoft Healthcare, Chief Architect for SMART Health IT
Apr 21, 2023

The unnest problem

The written-up proposal covered navigating into nested fields but not the reverse: getting a table of every observation component, one row each, out of one resource. Josh raised it and nobody had a syntax for it. It went on the list as something the design plainly needs — some form of unnest operation — with the shape left open.

What should a multi-valued expression return?

If an expression returns several values — every line of a multi-line address — what should happen?

  • Return an array of strings — most databases support natively
  • Use FHIRPath's own join to concatenate them into one string
  • Expand to multiple rows

The reservation about rows was surprise: silent expansion leads to bugs, and the point of standing on FHIRPath is that its semantics are already defined and shouldn't be quietly redefined.

How to serialise a view — StructureDefinition or JSON?

The longest argument was how to serialise a view.

For StructureDefinition: this is a FHIR project, StructureDefinition already models paths, cardinality, types, constraints and bindings, and bespoke JSON reinvents all of it.

For plain JSON: the problem being solved is working with FHIR data, not extending the FHIR spec, and plain JSON is easier to read, edit and tool.

It ended in a truce — model only what's needed in simple JSON now, revisit StructureDefinition or a dedicated view definition resource once the shape stops moving. The one concrete argument for a resource: a server could then list its views and expose an operation to export them.

Value sets — mixed feelings

A view of statin medications defined by a value set is genuinely useful, and if the spec ducks it every user writes the complicated value-set SQL themselves — which is an argument for including it. But it drags terminology into the system and makes generated queries heavy.

Left open, with the general heuristic that a feature earns its place if the value justifies the implementation cost.

FHIRPath subset — write it as an ANTLR grammar

Agreement that only a subset of FHIRPath should be supported, and that the subset should be written out as an ANTLR grammar so an unsupported function produces a clear error rather than a mystery.

The implementation note that mattered most: translating an expression requires knowing the cardinality at every step, which means consulting StructureDefinitions at compile time to decide whether to emit an unnest or a plain field access — the single biggest source of complexity in the translator.