The first call — can the columnar and JSON takes on SQL on FHIR merge? — Mar 3, 2023
Can two SQL-on-FHIR takes become one?
The opening question was whether the two existing approaches — a columnar/tabular representation and a JSON one — could merge. Ryan Brush, who took the first pass at this at Cerner, thought much of the work is shared: both need the same transformations to an SQL model, like pulling record IDs out so rows join cleanly, and that common list is worth writing down in one place.
What can't be shared is storage. A columnar model only pays off if the data is physically laid out that way, so it cannot be layered on top of a JSON one.
The columnar side is lossy, and the group accepts it
SQL has no arbitrarily deep recursive structures, so you must pick a recursion depth and decide which extensions to unbundle, and every codeable concept you spread out multiplies rows. The settlement was to treat the columnar tables as a materialised view over the real FHIR data — good performance, acknowledged information loss — and keep the JSON side lossless, because that is what users expect.
Several people had independently arrived at the same layering: rich FHIR at the base, flattened views on top, analysis on top of those.
Is there still a general project — or just database-specific views?
If the real value turns out to be use-case-specific tables, is there still a general project underneath, or does that just become a database-specific concern?
Josh also doubted in chat that a generically flattened FHIR is feasible at all.
I laughed and thought — are we building OMOP here?
Nobody argued for it. The seed of what became ViewDefinition was dropped almost in passing: Ryan mentioned they had been experimenting with ways to declare a flat view rather than hand-write it — FHIRPath expressions to say which columns you want, plus a filter to say which rows (only haemoglobin observations, say) — and there's your table.
Modelling an "SQL condition" as a logical model
Brian Kaney demoed a first pass at modelling an SQL condition as a logical model in FHIR Shorthand, producing real StructureDefinitions. Evan backed the direction — ignore HL7's own machinery — and noted StructureDefinitions come with toolchains that already generate C# or TypeScript classes.
This isn't a working group. It's just a group of guys who want to make FHIR work on databases.
It may be too early to bind to FHIR's structures before deciding which transformations and output structures the group actually wants.
What databases can actually do
Nikolai closed with research into what databases can really do. Basic JSON path — dot access and array indexing — is the common denominator across both JSON and columnar engines. Wildcards are rarer, and only Postgres does JSONPath filtering.
That frames the open choice: transform aggressively (index extensions by URL, turn arrays that are really maps into keyed objects) so plain dot access is enough everywhere, or transform minimally and lean on advanced database features.
The transforms should be runnable inside the database as a stored procedure. Every extra service between a bulk export and the schema is friction, and friction kills adoption.
Homework: bring your use cases, and pick one narrow transform so the next argument is about something concrete.