Defining flat views with View Definitions
SQL on FHIR utilizes ViewDefinition resources to describe the structure of flat views. A simple ViewDefinition may look like this:
{
"name": "obs_view",
"resource": "Observation",
"status": "active",
"select": [{
"alias": "id",
"path": "id",
}, {
"alias": "pid",
"path": "subject.getId('Patient')"
}]
}
The view is described with its name, resource type, status and the declaration of the rows it contains. In this example, we define a flat view named
obs_view
for Observations, that will have 2 fields: id
, defined as the Observation's own id, and pid
, defined as the subject Patient's id.This example only scratches the surface. For more complex examples, see Query data from flat views. For an in-depth overview of a ViewDefinition's structure consult Reference.
To create a flat view of the resource, you have to define it with a special resource called ViewDefinition. You can do it with a View Definitions editor in Aidbox UI.

View Definitions editor
View definitions you've created can be viewed in a menu in the right part of the screen. There also are several samples to get you started. Note that samples are presented as View Definitions only and have no corresponding views in the database by default.
You can use the Run button or
Ctrl+Enter
to preview your view. To save a View Definition and materialize it as a view in the database, press Save. Delete button deletes both a View Definition and the corresponding view in the database.Note that a ViewDefinition resource used in Aidbox may difer from SQL on FHIR specification. To see how Aidbox's ViewDefinition is structured, consult the Reference page.
As ViewDefinition is a resource, it can be created via REST API. For example:
Request
Response
POST /ViewDefinition/
name: patient_view
resource: Patient
description: Patient flat view
status: draft
select:
- alias: id
path: id
- alias: bod
path: birthDate
- alias: gender
path: gender
name: patient_view
select:
- path: id
alias: id
- path: birthDate
alias: bod
- path: gender
alias: gender
status: draft
resource: Patient
description: Patient flat view
id: >-
30d1cf2a-6610-4887-9fc2-fbd425837d4e
resourceType: ViewDefinition
This step will create both a ViewDefinition resource and the corresponding flat view.
PUT
and DELETE
operations will also affect both ViewDefinitions and their flat views.By default views you define are materialized as views in
sof
schema. You can alter this behavior by adding an extension.extension:
- url: https://fhir.aidbox.app/fhir/Extension/view-definition
extension:
- url: materialization
value:
code: table
- url: schema
value:
string: public
In the example above, view will be materialized as a table in a schema called
public
. Available materialization options are table
, materialized-view
and view
.If you create views via Aidbox UI, you can use the shorthand parameters
_materialization
and _schema
instead, like this:{
"_materialization": "table",
"_schema": "public"
}
Note that these shorthands are not available for REST API.
Last modified 11d ago