SQL on FHIR
Haste Health can flatten FHIR resources into tabular rows using ViewDefinition, the SQL on FHIR implementation guide's resource for describing a "view" over FHIR data. A ViewDefinition is itself a FHIR resource — you create it like any other resource, then run it to get back CSV, JSON, or NDJSON rows instead of nested FHIR JSON.
{
"resourceType": "ViewDefinition",
"status": "draft",
"resource": "Patient",
"select": [
{
"column": [
{ "name": "id", "path": "id", "type": "http://hl7.org/fhirpath/System.String" },
{ "name": "birth_date", "path": "birthDate", "type": "date" }
]
},
{
"forEach": "name",
"column": [
{ "name": "given", "path": "given", "type": "string", "collection": true },
{ "name": "family", "path": "family", "type": "string" }
]
}
]
}
How It Works
Running a ViewDefinition is a two-step flow:
- Store the ViewDefinition (optional, but typical):
POSTit to/ViewDefinitionlike any other resource, or skip this and send it inline. - Run it via the
viewdefinition-runsystem operation. For each resource that matches, the engine evaluates everyselectblock's FHIRPath expressions and assembles a row.
For each select block in ViewDefinition.select, Haste Health:
- Establishes the row context: either the resource itself, or — if
forEach/forEachOrNull/repeatis set — the result of evaluating that FHIRPath expression against the resource. - Evaluates each
column.pathFHIRPath expression against that context and converts the result to the column's declaredtype. - Combines the results of every top-level
selectblock via a cartesian product — so a ViewDefinition with aname-scopedselectand atelecom-scopedselectproduces one row per (name × telecom) pair.
forEach produces one row per matched element (no rows if there are none); forEachOrNull produces one row per element, or a single row with null columns if there are none; repeat recursively walks each listed FHIRPath expression to any depth and unions the results into one row set — useful for self-referential structures like Questionnaire.item.
where clauses (ViewDefinition.where[].path) are evaluated per-resource before projection; a resource is included only if every clause evaluates to true. constant entries are available in any FHIRPath expression as %[name].
Column Types
column.type must be a FHIR primitive type (string, boolean, decimal, integer, date, dateTime, instant, time, code, id, uri, url, canonical, oid, uuid, base64Binary, markdown, unsignedInt, positiveInt) or a FHIRPath system type (http://hl7.org/fhirpath/System.String, .Boolean, .Integer, .Decimal, .Date, .DateTime, .Time). Composite/complex types (CodeableConcept, Coding, HumanName, Reference, etc.) cannot be used directly as a column type — project the specific primitive field you need instead (e.g. name.family rather than name).
The declared type must match the runtime type of the FHIRPath result exactly — there's no implicit widening (an integer value against a decimal-typed column will error). Mark a column "collection": true if its path can return more than one value; a multi-value result on a non-collection column is a hard error.
Running a View
The run operation is invoked as a system-level custom operation, $viewdefinition-run, accepting a Parameters resource and returning a Binary containing the encoded output.
- CLI
- TypeScript
- cURL
haste-health api invoke-system viewdefinition-run --file params.json
Where params.json contains:
{
"resourceType": "Parameters",
"parameter": [
{ "name": "viewReference", "valueReference": { "reference": "ViewDefinition/123" } },
{ "name": "_format", "valueCode": "csv" },
{ "name": "_since", "valueInstant": "1980-01-01T00:00:00Z" }
]
}
import { AsynchronousClient } from '@haste-health/client';
import { R4 } from '@haste-health/fhir-types/versions';
import { ViewDefinitionRun } from '@haste-health/generated-ops/r4';
const client: AsynchronousClient<{}> = /* ... */;
const binary = await client.invoke_system(
ViewDefinitionRun.Op,
{},
R4,
{
viewResource: myViewDefinition,
_format: 'ndjson',
_since: '1980-01-01T00:00:00Z',
},
);
curl -X POST "https://api.haste.health/w/[tenant]/[project]/api/v1/fhir/\$viewdefinition-run" \
-H "Content-Type: application/fhir+json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
-d '{
"resourceType": "Parameters",
"parameter": [
{ "name": "viewReference", "valueReference": { "reference": "ViewDefinition/123" } },
{ "name": "_format", "valueCode": "csv" },
{ "name": "_since", "valueInstant": "1980-01-01T00:00:00Z" }
]
}'
The response is a Binary resource whose data field is the base64-encoded output — decode it client-side before parsing as CSV/JSON/NDJSON.
Parameters
| Parameter | Description |
|---|---|
viewResource | An inline ViewDefinition to run, instead of a stored one. |
viewReference | A Reference to a stored ViewDefinition (e.g. ViewDefinition/123). |
resource | Run against these resources directly instead of the project's data. patient, group and _since don't filter them. |
patient | Only resources in this Patient's compartment. A Patient the project doesn't have is refused with 400. |
group | Only resources in the compartments of the Group's Patient members. Repeat it for several Groups. With patient too, the run covers only that Patient, and only if it is a member. |
_since | Only resources changed after this instant. |
_limit | The most rows to return, up to the row limit below. |
header | Whether CSV output starts with a header row (default true). |
_format | Output format: ndjson (default), csv or json. |
source | Not supported. A run that sends it is refused with 400. |
Exactly one of viewResource / viewReference is required, except at instance level: POST /ViewDefinition/[id]/$viewdefinition-run runs that stored view and takes neither. Without resource, the view runs over the project's current resources of the ViewDefinition.resource type.
Limits
A run answers with the whole table in one response, so it is bounded. A run that would return more than 10,000 rows (without _limit), or read more than 50,000 resources (earlier versions included), is refused with 422 and a too-costly OperationOutcome rather than answered with part of the table. Narrow it with _limit, patient, group or _since. The limits are the server settings sql_on_fhir.max_rows and sql_on_fhir.max_resources (see Configuration).
Output Shapes
- CSV: standard comma-separated rows with a header line. Multi-value (
collection: true) columns are joined with;. - JSON: a single JSON array of row objects, e.g.
[{ "id": "123", "given": ["John"], "family": "Doe" }]. Scalar columns are the bare value; collection columns are arrays. - NDJSON: the same row objects, one per line, newline-delimited.
Admin App UI
ViewDefinitions live under Projections in the console sidebar's
Operate section (/r/ViewDefinition). Creating or editing one swaps in a
dedicated ViewDefinition editor (instead of the generic Form/JSON tabs),
with three tabs:
- ViewDefinition — a CodeMirror JSON editor for the resource body, plus an output-format selector (CSV/JSON/NDJSON) and Run/Reset buttons.
- SQL Runner Results — a paginated table (configurable page size) rendering whatever the run produced.
- Raw Output — the decoded response as plain text, useful for copying out CSV/NDJSON directly.
Run executes the ViewDefinition inline (via viewResource, not viewReference) against your live project data, so you can iterate on a draft before saving it. The admin UI passes _since: "1980-01-01T00:00:00Z", so the run covers all of the project's data.
Limitations
The engine covers the core of the SQL on FHIR spec, but several parts aren't implemented yet:
- Nested
selectandunionAllare not evaluated.ViewDefinition.select[].select(sub-selects for one-to-many denormalization within a select block) andViewDefinition.select[].unionAll(combining multiple selection structures into one row set) are valid per the resource's schema but are silently ignored by the run engine — no error is raised, and no columns are produced from them. Only top-levelselectentries withcolumn/forEach/forEachOrNull/repeatare processed. sourceis not supported. Reading from an external source (such as a bucket of bulk export files) isn't implemented, so a run that sendssourceis refused.- Only the Patient compartment.
groupmembers other than Patients are left out, and a view over a resource type outside the Patient compartment can't be limited bypatientorgroup. - No export for large tables. A run past the limits is refused; the asynchronous
$viewdefinition-exportoperation isn't implemented yet. - Only
csv,json, andndjsonoutput formats are implemented. The_formatvalue set also allowsfhirandparquet; requesting either returns anot-supportedOperationOutcome. - CSV columns are derived from the first row. If different rows would otherwise produce different column sets (e.g. via conditional branches), only the first row's keys are used as headers for the whole file. An empty result set produces no header row at all.
- Terminology bindings aren't evaluated. Column values are converted as raw primitives; no
ValueSetmembership or code validation happens as part of a view run.