Expert guidance for implementing SQL on FHIR v2 ViewDefinitions and operations to create portable, tabular projections of FHIR data. Use this skill when the user asks to create ViewDefinitions, flatten FHIR resources into tables, write FHIRPath expressions for data extraction, implement forEach/forEachOrNull/repeat patterns for unnesting, create where clauses for filtering, use constants in view definitions, combine data with unionAll, execute ViewDefinitions with $run or $export operations, or implement SQL on FHIR server capabilities. Trigger keywords include "ViewDefinition", "SQL on FHIR", "flatten FHIR", "tabular FHIR", "FHIR to SQL", "FHIR analytics", "FHIRPath columns", "unnest FHIR", "$viewdefinition-run", "$export", "view runner", "repeat", "recursive", "QuestionnaireResponse".
SQL on FHIR v2 defines portable, tabular projections of FHIR resources using FHIRPath expressions. ViewDefinitions transform hierarchical FHIR data into flat tables for analytics.
A ViewDefinition projects exactly one FHIR resource type into rows and columns. It contains:
resource: The FHIR resource type (Patient, Observation, etc.)select: Column definitions and row iteration logicwhere: Optional filtering criteriaconstant: Reusable values for expressionsBasic structure:
{
"resourceType": "ViewDefinition",
"resource": "Patient",
"status": "active",
"select": [
{
"column": [
{ "name": "id", "path": "id" },
{ "name": "gender", "path": "gender" },
{ "name": "birth_date", "path": "birthDate" }
]
}
]
}
For complete element reference, see references/view-definition-structure.md.
Each column has:
name: Database-friendly identifier (pattern: ^[A-Za-z][A-Za-z0-9_]*$)path: FHIRPath expression extracting the valuetype (optional): FHIR primitive type URIcollection (optional): Set true if column may contain arraysdescription (optional): Human-readable explanation{
"column": [
{
"name": "family_name",
"path": "name.first().family",
"type": "string",
"description": "Patient's primary family name"
}
]
}
Use forEach to create one row per element in a collection. Without forEach, one row per resource is created.
Example: One row per patient name:
{
"resource": "Patient",
"select": [
{ "column": [{ "name": "id", "path": "id" }] },
{
"forEach": "name",
"column": [
{ "name": "family", "path": "family" },
{ "name": "given", "path": "given.first()" }
]
}
]
}
forEachOrNull: Same as forEach but keeps a row with nulls when the collection is empty.
Nested forEach: Create cross-products by nesting:
{
"forEach": "contact",
"select": [
{
"column": [
{
"name": "contact_phone",
"path": "telecom.where(system='phone').value"
}
]
},
{
"forEach": "name.given",
"column": [{ "name": "given_name", "path": "$this" }]
}
]
}
Use repeat to recursively traverse nested structures to any depth. This is essential for resources with arbitrary nesting like QuestionnaireResponse items.
Constraint: Only one of forEach, forEachOrNull, or repeat may be specified per select.
{
"resource": "QuestionnaireResponse",
"select": [
{ "column": [{ "name": "response_id", "path": "id" }] },
{
"repeat": ["item", "answer.item"],
"column": [
{ "name": "link_id", "path": "linkId" },
{
"name": "answer_text",
"path": "answer.value.ofType(string).first()"
}
]
}
]
}
The view runner:
repeat arrayThis produces a flat table with all items regardless of nesting depth.
Filter resources using FHIRPath expressions that must evaluate to true:
{
"resource": "Patient",
"where": [
{"path": "active = true"},
{"path": "name.exists()"}
],
"select": [...]
}
Multiple where clauses are ANDed together.
Define reusable values referenced via %name syntax:
{
"constant": [{ "name": "use_type", "valueString": "official" }],
"select": [
{
"forEach": "name.where(use = %use_type)",
"column": [{ "name": "official_name", "path": "family" }]
}
]
}
Supported constant types: valueString, valueInteger, valueBoolean, valueDecimal, valueDate, valueDateTime, valueCode.
Combine multiple selection paths with matching column schemas:
{
"select": [
{ "column": [{ "name": "id", "path": "id" }] },
{
"unionAll": [
{
"forEach": "telecom",
"column": [
{ "name": "contact", "path": "value" },
{ "name": "system", "path": "system" }
]
},
{
"forEach": "contact.telecom",
"column": [
{ "name": "contact", "path": "value" },
{ "name": "system", "path": "system" }
]
}
]
}
]
}
ViewDefinitions use a minimal FHIRPath subset. Key functions:
| Function | Description |
|---|---|
first() | First element of collection |
exists() | True if collection has elements |
empty() | True if collection is empty |
where(expr) | Filter collection by condition |
ofType(type) | Filter to specific FHIR type |
extension(url) | Get extension by URL |
join(sep) | Join collection into string |
getResourceKey() | Resource ID (indirect access) |
getReferenceKey(type?) | Extract ID from reference |
Path navigation:
name.familyname[0].family$this: Current context elementFor complete FHIRPath reference, see references/fhirpath-subset.md.
Extract extension values using the extension() function:
{
"column": [
{
"name": "birth_sex",
"path": "extension('http://hl7.org/fhir/us/core/StructureDefinition/us-core-birthsex').value.ofType(code).first()"
}
]
}
Nested extensions:
{
"path": "extension('http://hl7.org/fhir/us/core/StructureDefinition/us-core-race').extension('ombCategory').value.ofType(Coding).code.first()"
}
ShareableViewDefinition: For portable definitions. Requires:
url: Canonical identifiername: Database-safe identifierfhirVersion: Target FHIR version(s)type: Explicit types on all columnsTabularViewDefinition: For scalar/CSV output. Enforces:
collection: true columnsViewDefinitions intentionally exclude:
SQL on FHIR defines two operations for executing ViewDefinitions:
Synchronous execution returning results immediately.
GET [base]/ViewDefinition/[id]/$run?_format=csv
POST [base]/ViewDefinition/$run
Key parameters:
viewReference or viewResource: The ViewDefinition to execute_format: Output format (json, ndjson, csv, parquet)patient, group: Filter by patient/group_limit: Maximum rowsAsynchronous bulk export for large datasets.
POST [base]/ViewDefinition/$export
Uses async pattern:
Prefer: respond-async → 202 Accepted + status URLKey parameters:
view: One or more ViewDefinitions to export_format: Output formatpatient, group, _since: Filtering optionsFor complete operation details, parameters, and examples, see references/operations.md.
For comprehensive examples including Patient demographics, Condition flattening, blood pressure extraction, and complex unnesting patterns, see references/examples.md.