Talk to an expert Log in/Sign up

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:

{
  "resourceType": "ViewDefinition",
  "select": [
    {
      "column": [
        {
          "path": "getResourceKey()",
          "name": "id"
        },
        {
          "path": "gender",
          "name": "gender"
        }
      ]
    },
    {
      "column": [
        {
          "path": "given.join(' ')",
          "name": "given_name",
          "description": "A single given name field with all names joined together."
        },
        {
          "path": "family",
          "name": "family_name"
        }
      ],
      "forEach": "name.where(use = 'official').first()"
    }
  ],
  "name": "patient_demographics",
  "status": "draft",
  "resource": "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.

Creating flat views

With Aidbox UI

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 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.

With REST API

As ViewDefinition is a resource, it can be created via REST API. For example:

POST /ViewDefinition/

name: patient_view
resource: Patient
description: Patient flat view
status: draft
select:
- column:
  - name: id
    path: id
  - name: bod
    path: birthDate
  - name: gender
    path: gender

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.

Additional options

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 updated