SQL on FHIR WG Meetings
SQL on FHIR WG Meeting — March 3, 2023
Mar 3, 2023

Topics discussed:

  • 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 everyone accepted that: 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.
  • Josh Mandel asked the awkward question — 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? He also doubted in chat that a generically flattened FHIR is feasible at all; Ryan said he laughed and thought "are we building OMOP here". Nobody argued for it, and 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.
  • 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 "this isn't a working group, this is just a group of guys who want to make FHIR work on databases" — and noted StructureDefinitions come with toolchains that already generate C# or TypeScript classes. Dan Gottlieb's counter: it may be too early to bind to FHIR's structures before deciding which transformations and output structures the group actually wants.
  • 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. Evan added a constraint — the transforms should be runnable inside the database as a stored procedure, because 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.