Counting a cohort before extracting it — every obvious way breaks — Sep 9, 2025
The gap that's been bothering John
Exporting views and running SQL over them is covered. What isn't is the step before: identifying the cohort you're about to extract, and finding out how many of them exist.
If you're planning a study on patients aged 18 to 65 with some diagnosis, you want to know whether a given site has the statistical power to answer your question before you ask anyone for data. Views carry filter expressions, but there's no way to count the subjects those filters select.
Why the obvious fix doesn't survive contact
A count mode on the existing operations. Views are single-resource, and a real cohort spans resources. And you'd have to decide what you're counting: not rows, because views un-nest all sorts of things.
For the diabetes example you don't want all patients and you don't want conditions, you want unique patients once joined to conditions, which is inherently a multi-view operation.
John's candidate answer — FHIRPath as a search parameter
Pathling has had this for a long time — literally a search parameter you put a FHIRPath expression into.
Part of the problem is that the search API is less expressive than FHIRPath, so mechanically extracting a set of search filters from a set of ViewDefinitions runs into edge cases. But if you can hand FHIRPath straight to search, inclusion and exclusion criteria go in directly, and search already does the cross-resource part via chaining and reverse chaining.
Precedent — Germany's Medical Informatics Initiative
Arjun pointed at it — does exactly this two-step across dozens of research sites: translate the research question into FHIR search, ask each site whether it has matching patients, and only then run a second, more controlled process to actually obtain data, because the privacy and data management constraints demand it.
The use case isn't only research — mandatory government reporting and population health land in the same shape.
Two things nobody had noticed
SQL on FHIR was never published to the FHIR package registry — quite possibly why IG Publisher users have been seeing an error about not finding the SQL on FHIR package.
There's already a SQL on FHIR implementation inside FHIR core — the Java library that both HAPI and the IG Publisher depend on. It validates any ViewDefinition placed in an IG, renders it as a proper page rather than a generic file, and powers a feature where an inline SQL query on any IG page runs over tables generated from the IG's own resources.
It was written at a point in time and nobody knows whether it still conforms; John wants a test harness inside FHIR core reporting up to the test page to find out.