Skip to main content

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:

  1. Store the ViewDefinition (optional, but typical): POST it to /ViewDefinition like any other resource, or skip this and send it inline.
  2. Run it via the viewdefinition-run system operation. For each resource that matches, the engine evaluates every select block'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/repeat is set — the result of evaluating that FHIRPath expression against the resource.
  • Evaluates each column.path FHIRPath expression against that context and converts the result to the column's declared type.
  • Combines the results of every top-level select block via a cartesian product — so a ViewDefinition with a name-scoped select and a telecom-scoped select produces 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.

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" }
]
}

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​

ParameterDescription
viewResourceAn inline ViewDefinition to run, instead of a stored one.
viewReferenceA Reference to a stored ViewDefinition (e.g. ViewDefinition/123).
resourceRun against these resources directly instead of the project's data. patient, group and _since don't filter them.
patientOnly resources in this Patient's compartment. A Patient the project doesn't have is refused with 400.
groupOnly 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.
_sinceOnly resources changed after this instant.
_limitThe most rows to return, up to the row limit below.
headerWhether CSV output starts with a header row (default true).
_formatOutput format: ndjson (default), csv or json.
sourceNot 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 select and unionAll are not evaluated. ViewDefinition.select[].select (sub-selects for one-to-many denormalization within a select block) and ViewDefinition.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-level select entries with column/forEach/forEachOrNull/repeat are processed.
  • source is not supported. Reading from an external source (such as a bucket of bulk export files) isn't implemented, so a run that sends source is refused.
  • Only the Patient compartment. group members other than Patients are left out, and a view over a resource type outside the Patient compartment can't be limited by patient or group.
  • No export for large tables. A run past the limits is refused; the asynchronous $viewdefinition-export operation isn't implemented yet.
  • Only csv, json, and ndjson output formats are implemented. The _format value set also allows fhir and parquet; requesting either returns a not-supported OperationOutcome.
  • 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 ValueSet membership or code validation happens as part of a view run.