A playground for one query across databases, and the macro-as-new-dialect argument — Mar 24, 2023
The playground — one query, several databases
Pick a database, write one query, watch the macro expand into Postgres or into DuckDB. The macro set is deliberately tiny — a path expression covering a small subset of JSONPath (field names and indexes), age in years, and unnest, which becomes jsonb_array_elements on Postgres — implemented in a few lines of Clojure.
It also answered a question about whether Parquet and JSON were now a fork in the road: the same query ran over Postgres binary JSON and over DuckDB's Parquet, on the argument that columnar structured data is a subset of JSON, roughly the way static typing is a subset of dynamic.
The objection — is this just a new dialect of SQL?
I am slightly concerned that we're inventing a new dialect of SQL. The project is meant to make FHIR work on SQL, not to invent a portable SQL for every database — that is a different project. And it breaks the promise the schema is supposed to make: if someone writes Postgres queries behind a Power BI report and wants to say "this works on any SQL-on-FHIR schema", it won't be true when everything has to be written in a meta language and run through a translator first.
Also one more component in your stack — and a JVM if the reference implementation is in Clojure.
Nikolai's defence — common denominator, not dialect
It isn't a dialect but a common denominator. The macros reduce features rather than add them, inventing no new semantics. Every engine has some unnest, some element access, some date-time handling — you normalise those few things and translate.
Prior art was asked for — Apache Drill does attempt cross-database SQL, but it is a full parser and executor, orders of magnitude more work. DBT gets by with plain Jinja string templating.
Where it landed: macros are for generating views and for toolchains, not something end users should write queries in — with solid reference implementations, and someone offered to build a second one independently.
The other direction — structure instead of text
Evan floated representing the query as a FHIR resource broken into a structured document, the way ELM works for CQL, and had built something like it before — a logical query object with a translation layer per database.
The counter: if the macro set stays small, that beats inventing a query AST and mapping everything into and out of it for every engine.
Someone asked outright whether the path expressions should just be FHIRPath. The answer was wariness — FHIRPath has a lot of features and translating them is exactly where it would fall over, so bring the subset you actually need and it can be checked against the databases.
At a minimum, document the macro's formalities. Ideally a grammar and tests.
The first concrete corner case — variable-precision dates
It deeply matters whether a date of service is 1980, January 1980, or 1 January 1980 — those mean subtly different things, and if you can't tell that a value was only specified to year precision, most quality measurement is impossible.
Evan had started a rough CQL-to-SQL converter over the demo's Parquet files: "born after 1 January 1980" is easy, "born after 1980" forces a choice between views that expose date parts as their own columns and meta queries that reach for each database's own precision-extraction functions.
A separate caution: HL7 projects tend to turn things into keywords when they should be compositions of simpler units, and age — really just a date diff — is a candidate for exactly that mistake.