$materialize operation

Materializing SQL-on-FHIR ViewDefinitions as database tables, views, or materialized views

This functionality is available in Aidbox versions 2508 and later.

SQL on FHIR provides the $materialize operation to transform a ViewDefinition into a concrete database object. This operation can create a table, view, or materialized view in the database based on the ViewDefinition structure.

General syntax

To call the $materialize operation, use the following request format:

POST /fhir/ViewDefinition/<resource-id>/$materialize
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": parameter name,
      "resource": resource value
    }
  ]
}

Parameters

  • type: materialization type.

    This parameter is required.

    Example:

    {
      "name": "type",
      "valueCode": "table"
    }

    Supported materialization types:

    • "table" - materialization of ViewDefinition as a table;

    • "view" - materialization of ViewDefinition as a view;

    • "materialized-view" - materialization of ViewDefinition as a materialized view.

Schema configuration

By default, Aidbox creates all materialized ViewDefinitions in the sof schema. This schema can be configured in Aidbox settings through the db.view-definition-schema setting.

Please note that changing this setting only affects new materializations and does not retroactively change already materialized database objects. We recommend using a dedicated schema for ViewDefinition materializations to prevent naming conflicts with other database objects.

Examples

For example, with the given saved ViewDefinition:

{
  "resourceType": "ViewDefinition",
  "id": "0448a9a8-6114-4a19-aa8e-fc5f60c4d714",
  "name": "patient_view",
  "status": "draft",
  "resource": "Patient",
  "description": "Patient flat view",
  "select": [{
    "column": [{
      "name": "id",
      "path": "id",
      "type": "id"
    }, {
      "name": "bod",
      "path": "birthDate",
      "type": "date"
    }, {
      "name": "gender",
      "path": "gender",
      "type": "code"
    }]
  }]
}

Materialization into a table can be performed in the REST console with the following request:

POST /fhir/ViewDefinition/0448a9a8-6114-4a19-aa8e-fc5f60c4d714/$materialize
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "type",
      "valueCode": "table"
    }
  ]
}

Alternatively, it can be materialized as a view:

POST /fhir/ViewDefinition/0448a9a8-6114-4a19-aa8e-fc5f60c4d714/$materialize
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "type",
      "valueCode": "view"
    }
  ]
}

Or a materialized view:

POST /fhir/ViewDefinition/0448a9a8-6114-4a19-aa8e-fc5f60c4d714/$materialize
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "type",
      "valueCode": "materialized-view"
    }
  ]
}

Using a Bundle

ViewDefinition creation and materialization can be performed with a Bundle, making it ideal for inclusion in an InitBundle for automated setup during Aidbox initialization:

POST /fhir/
Content-Type: application/json
Accept: application/json

{
  "resourceType": "Bundle",
  "type": "transaction",
  "id": "bundle-transaction",
  "entry": [{
    "request": {
      "method": "POST",
      "url": "ViewDefinition"
    },
    "resource": {
      "id": "custom_schema_view_id_bundle",
      "resourceType": "ViewDefinition",
      "resource": "Patient",
      "name": "custom_schema_view_name",
      "status": "draft",
      "select": [{
        "column": [{
          "name": "id",
          "path": "id"
        }, {
          "name": "gender",
          "path": "gender"
        }, {
          "name": "birthdate",
          "path": "birthDate"
        }]
      }]
    }
  }, {
    "request": {
      "method": "POST",
      "url": "ViewDefinition/custom_schema_view_id_bundle/$materialize"
    },
    "resource": {
      "resourceType": "Parameters",
      "parameter": [{
        "name": "type",
        "valueCode": "view"
      }]
    }
  }]
}

Output examples

Successful materialization:

{
  "resourceType": "Parameters",
  "parameter": [
    {
      "name": "viewName",
      "valueString": "sof.patient_view"
    },
    {
      "name": "viewType",
      "valueString": "table"
    },
    {
      "name": "viewSchema",
      "valueString": "sof"
    }
  ]
}

Unsuccessful materialization due to incorrect materialization type:

{
  "resourceType": "OperationOutcome",
  "id": "value",
  "text": {
    "status": "generated",
    "div": "Invalid materialization type"
  },
  "issue": [
    {
      "severity": "fatal",
      "code": "value",
      "diagnostics": "Invalid materialization type",
      "type": "materialized-table",
      "schema": "sof"
    }
  ]
}

Checking Materialized ViewDefinition in the DB Console

After materializing a ViewDefinition using any of the methods described above, you can verify its creation and query its data using the DB Console with the following SQL query:

SELECT * FROM sof.patient_view;

This query retrieves all records from the materialized view, table, or database view that was created in the 'sof' schema (or your configured schema).

Managing created views

Aidbox only creates or updates existing materialized ViewDefinition objects in the database. The refreshing of materialized views and deletion of any created database objects (tables, views, or materialized views) must be handled manually using PostgreSQL commands. Note that editing or deleting the original ViewDefinition resource in Aidbox does not automatically affect any database objects that were previously created from that definition.

Last updated

Was this helpful?