Google's FHIRPath-to-SQL work lands, and the goal shifts to describing flattened views — Mar 31, 2023
Two alternatives to the macro language
Why not target ANSI SQL 2016, which already standardises JSON access, and translate that instead of inventing a DSL?
Don't invent anything. Write to one base SQL dialect and translate to the others. OMOP does exactly this for its warehouse — SQL Server as the base, a translation engine for Oracle and friends. SQLGlot parses SQL into an AST and regenerates it in other dialects; you swap one join syntax for another without changing the shape of the query. The further you go into your own DSL the more tooling you maintain forever.
Nikolai's answer was empirical: the standard has been out for years and nobody implements it — only Postgres supports close to the full JSONPath spec, Oracle a little, everyone else has dot-and-index.
Unless you try you will not understand the details. Build it in the playground and bring a proposal — this is a hypothesis, not something I'm driving.
Ryan reframes — objectives, not implementations
The argument isn't converging because people are starting from different objectives and different values — ease of implementation against a thorough, portable, standards-based spec. The right answer depends on which you weigh more.
He offered his own objective plainly: a well-defined way to describe flattened views of FHIR data that are portable across many database implementations, standing on existing standards wherever possible. Which, full disclosure, pulls him towards FHIRPath, or at least a subset of it, because it is established and its semantics are already defined.
The data point that changed the room — Google's FHIRPath-to-SQL
Proof-of-concept work at Google translates a large set of FHIRPath expressions into SQL that ports across engines, running today on BigQuery and mostly on Apache Spark, emitting the right unnest or lateral view explode for each. It is genuinely more complicated — you parse FHIRPath into an AST and recursively translate that into SQL.
Is that complexity justified? That's where it gets back into what the values are.
The upside he pointed at: once your columns are described by FHIRPath expressions, you can point them at a pile of NDJSON and get a data frame. A demo was promised for the following week.
The goal itself was quietly moving
Ryan asked the group to make it an explicit decision rather than a drift:
- Old goal: find a SQL-on-FHIR representation that works in plain ANSI SQL
- New goal: find a standard way to describe flattened views of FHIR data
The base layer will always vary too much between engines to query portably.
Three options on the table
- A definition language of its own (FHIRPath, JSON Path, something CQL-like)
- A base SQL dialect plus translation
- A macro language, possibly FHIRPath-based, mixed into SQL
One participant was sceptical that any standard set of views could ever cover even 90% of use cases — better to standardise how a view is defined and let the useful views spread on their own.
A base schema needs to be published soon. Every company that gets FHIR and a warehouse invents its own schema, and guidance arriving late means there is nothing left to standardise.
Why any of this matters — run the measures where the data lives
Today you download the whole database every night, deserialise it, and execute somewhere else. Most of the time goes on the download and the deserialising. Even badly written in-database queries would be orders of magnitude faster, close to real time — and all the effort spent shrinking the data pulled down is solving the wrong problem.
His own caveat: real CQL is not the one-file demo measures everyone shows — interval arithmetic, date precision, continuous enrolment, measures with fourteen numerators. Without user-defined functions in the target database you may not get there at all.
The call closed on the first sketch of a view: take the logical model Brian had demoed on the first call, attach a FHIRPath expression to each column saying what to extract, and that is your base view — join and build the rest on top, leaving whether it is materialised as a table, a view or a materialised view as an implementation decision.