Topics discussed:

• Josh walked through his compiler end to end: parse the FHIRPath with fhirpath.js into an intermediate representation, then emit SQL as a chain of table expressions and select star from the last one. Patients explode into names, names explode into given names, a table of the constant gets built so every value can be compared against it, matches come back as zeros and ones, and then it groups back up by the name each row came from. The honest headline was that a very short ViewDefinition produced a full screen of SQL, and that it has never been run on more than two patients.
• The performance objection landed immediately: those table-wide GROUP BYs are a function of how many resources you have, and at millions of resources they get expensive. Someone reported a colleague had been pushed to write the SQL with no group-bys at all and managed it, but it was painful. Josh's response was that he would genuinely like to know how — it was not obvious to him, and he suspected there was prior art he had not found.
• Nikolai's recommendation was concrete: not CTEs, a series of lateral joins. In Postgres, CTEs are eager, so at real scale you hit performance problems; a lateral join is evaluated per row and stays local, so the engine effectively iterates rows, expands and joins as it goes. Aidbox translates FHIRPath into Postgres jsonpath, which lets simple field access sit directly in the select instead of earning its own join, and uses explicit lateral joins for forEach. Nested selects become additional lateral joins in a flat linear list rather than nesting. The gotcha is that the jsonpath query functions behave slightly differently from each other, and picking the wrong one bites you on arrays.
• Two other approaches were shown. One prototype targets DuckDB and stays as flat as it can, only dropping into a subquery for first so it can do a LIMIT 1 — and not even then if it can use DuckDB's own syntax in the select. And on BigQuery, group-bys were avoided by leaning on the engine: since a FHIRPath expression is recursive and SQL nests recursively, they emitted deeply nested inner selects all the way down, no CTEs and no joins, keeping everything local. It made the SQL almost entirely unreadable by a human and ran efficiently.
• Pathling's refactor was the sharpest finding: they used to use explode plus joins, and removed all the joins because joins are poison in Spark — every one may redistribute data between workers. Now they hold a collection representation as far along as possible, building up a complicated select clause of functions that operate on collections, and only explode at the tail end when the user actually asked for more than one row per resource. Exploding early is what costs you: you end up with two representations and more steps in the query plan. Union needed a custom cross-product function written against the Catalyst API. Two ideas came out of the comparison — that every project converges on some intermediate representation, and finding a common one might let them plug into more engines; and Apache Calcite as a way to hand off the last half mile to a pluggable optimiser, which matters more if ViewDefinitions ever get an idiomatic SQL syntax instead of JSON.