What is a ViewDefinition, and how does it work?

The "SQL on FHIR v2" working group is close to its first release, scheduled for the end of summer 2024. The specification aims to build a bridge between FHIR data and modern databases and analytics ecosystems. The core idea is to introduce a standardized way to flatten FHIR resources into relational tables. We believe that a flat representation of healthcare data will make data engineers and analytical tools more efficient.

This flattening transformation is defining by a special resource type: ViewDefinition. Although there are no universal flat views for most FHIR resources, we believe many useful, use-case-specific views could exist. ViewDefinitions are CanonicalResources and can be published as part of Implementation Guides. With standard ANSI SQL queries, they can form the basis for interoperable analytics and reporting on FHIR. This post will help you understand how ViewDefinition works.

A ViewDefinition is an algorithm that describes the flattening transformation of FHIR resources, composed of combinations of a few functions.

‍

Use our free online ViewDefinition Builder to convert FHIR data stored in JSON representation into a tabular, flat format for convenient data analysis. Go to ViewDefinition Builder

‍

A ViewDefinition is represented as a FHIR Resource (JSON document) where the elements (keywords) correspond to functions:

{
    "resourceType": "ViewDefinition",
    "resource": "Patient",
    // (0)
    "where": [{filter: "active = true"}],
    // (5)
    "select": [
        {
          // (4)
          "column": [
             {"path": "getResourceKey()", "name": "id"},
             {"path": "identifier.where(system='ssn')", "name": "ssn"},
          ]
        },
        { 
          // (3)
          "unionAll": [
            {
              // (1)
              "forEach": "telecom.where(system='phone')",
              "column": [{"path": "value", "name": "phone"}] 
            },
            { 
              // (2)
              "forEach": "contact.telecom.where(system='phone')",
              "column": [{"path": "value",  "name": "phone"}] 
            }
       ]}       
    ]
}

This view produces a table of patient contacts, with each row representing a telecom entry.

Here is an example of the input and output for this ViewDefinition.

[
 {
    "resourceType": "Patient", 
    "id": "pt1", 
    "identifier": [{"system": "ssn", "value": "s1"}],
    "telecom":   [{"system": "phone", "value": "tt1"}],
    "contact": [
         {"telecom": [{"system": "phone", "value": "t12"}]},
         {"telecom": [{"system": "phone", "value": "t13"}]}
     ]
 },
 {
    "resourceType": "Patient", 
    "id": "pt2", 
    "identifier": [{"system": "ssn", "value": "s2"}],
    "telecom":   [{"system": "phone", "value": "t21"}],
    "contact": [
         {"telecom": [{"system": "phone", "value": "t22"}]},
         {"telecom": [{"system": "phone", "value": "t23"}]}
     ]
 }
]

Result

FHIRPath subset

ViewDefinitions use a minimal subset of FHIRPath to make implementation as simple as possible. Additionally, the specification introduces a few special functions:

Functions / Keywords

Let’s walk through each function in detail.

column

The 'column' function extracts elements into columns using FHIRPath expressions. The algorithm starts by receiving a list of {name, path} pairs. For each record in the given context, it evaluates the path expression to extract the desired elements. The resulting values are then added as columns to the output row.

{
    "column": [
        {"name": "id",          "path": "getResourceKey()"},
        {"name": "bod",         "path": "birthDate"},
        {"name": "first_name",  "path": "name.first().given.join(' ')"},
        {"name": "last_name",   "path": "name.first().family"},
        {"name": "ssn",         "path": "identifier.where(system='ssn').value.first()"},
        {"name": "phone",       "path": "telecom.where(system='phone').value.first()"},
    ]
}

Here is the naive JavaScript implementation:

function column(cols, rows) {
    return rows.map((row)=> {
        return cols.reduce((res, col ) => {
            res[col.name] = fhirpath(col.path, row)
            return res
        }, {})
    })
}

‍

where

The 'where' function retains only those records for which its FHIRPath expression returns true.

{
  "resourceType": "ViewDefinition",
  "resource": "Patient",
  "where": [
      {"filter": "meta.profile.where($this = 'myprofile').exists()"},
      {"filter": "active = 'true'"}
  ]
}

Basic JavaScript implementation:

function where(exprs, rows) {
    return rows.filter((row)=> {
        return exprs.every((expr)=>{
            return fhirpath(expr, row) == true;
        })
    })
}

‍

forEach & forEachOrNull

{
    "resourceType": "ViewDefinition",
    "resource": "Patient",
    "select": [{
      "forEach": "name",
      "column": [
        {"path": "given.join(' ')", "name": "first_name"},
        {"path": "family", "name": "last_name"}
      ]
    }]
}

function forEach(path, expr, rows) {
    return rows.flatMap((row)=> {
        return fhirpath(expr, row).map((item)=>{
            // evalKeyword will call column, select or other functions
            return evalKeyword(expr, item)
        })
    })
}

‍

select

{
    "resourceType": "ViewDefinition",
    "resource": "Patient",
    "select": [
     {
         "column": [
            {"path": "getResourceKey()", "name": "id"}
         ]
     },
     {
        "forEach": "name",
        "column": [
          {"path": "given.join(' ')", "name": "first_name"},
          {"path": "family", "name": "last_name"}
        ]
     }
    ]
}

Naive implementation is:

function select(rows1, rows2){
    return rows1.flatMap((r1)=> {
        return rows2.map((r2)=>{
            // merge r1 and r2
            return { ...r1, ...r2 }
        })
    })
}

select([{a: 1}, {a: 2}], [{b: 1}, {b: 2}])
//=>
[{a: 1, b: 1}, 
 {a: 1, b: 2},
 {a: 2, b: 1},
 {a: 2, b: 2}]

‍

unionAll

{
    "resourceType": "ViewDefinition",
    "resource": "Patient",
    "select": [
        {
          "column": [
             {"path": "getResourceKey()", "name": "id"}
          ]
        },
        { 
          "unionAll": [
            {
              "forEach": "telecom.where(system='phone')",
              "column": [{"path": "value", "name": "phone"}] 
            },
            { 
              "forEach": "contact.telecom.where(system='phone')",
              "column": [{"path": "value",  "name": "phone"}] 
            }
       ]}       
    ]
}

The implementation is just a simple concatenation:

function unionAll(rowSets){
    return rowSet.flatMap((rows)=> { return rows})
}

unionAll([1,2,3], [3,4,5])
//=>
[1,2,3,3,4,5]

‍

• forEach(OrNull)
• select
• unionAll
• column

{
    "forEach":   FOREACH,
    "column":    [COLUMNS], // got into select
    "unionALL":  [UNIONS],  // got into select
    "select":    [SELECTS]
}
//=>
{     
    "forEach": FOREACH
    "select": [
       {"column":   [COLUMNS]},
       {"unionAll": [UNIONS]},
       SELECTS...
    ]
}

Check out the reference implementation.

ViewDefinition Engines

The ViewDefinition can be executed by an engine to produce flat views from FHIR resources. There are two categories of engines:

• In-memory engines: These engines consume resources, flatten them, and output results into a stream, a file, or a table. You can imagine an ETL pipeline transforming FHIR Bulk export NDJSON files into Parquet files.

• In-database engines: These engines translate ViewDefinition into a SQL query over a FHIR-native database. In this case, the view can be a real database view. In-database engines can be much more efficient than in-memory engines in terms of speed and storage resources but are more complex for implementers.

There is an official list of implementations available at https://fhir.github.io/sql-on-fhir-v2/#impls. Most implementations are in-memory engines. Aidbox (PostgreSQL) and Pathling (Spark SQL) are in-database engines.

Aidbox (in-database engine)

Aidbox is a FHIR Server and Database for FHIR-native systems with out-of-the-box support for SQL on FHIR. Aidbox transpiles a ViewDefinition into a PostgreSQL SQL query, which can be run "as is" or used to create a database view.

For example, this ViewDefinition

{
  "resource": "Patient",
  "select": [
    {
      "column": [
        {
          "name": "id",
          "path": "getResourceKey()"
        }
      ]
    },
    {
      "forEach": "name",
      "select": [
        {
          "column": [
            {
              "name": "family",
              "path": "family"
            },
            {
              "name": "given",
              "path": "given.join(' ')"
            }
          ]
        }
      ]
    }
  ]
}

will be transpiled into:

SELECT
  cast(id AS text) as "id",
  cast(
    jsonb_path_query_first(q1_1, '$  . family') #>> '{}' AS text
  ) as "family",
  coalesce(
    array_to_string(
      (
        SELECT
          array_agg(x)
        FROM     jsonb_array_elements_text(jsonb_path_query_array(q1_1, '$  . given  [*]')) as x
      ),
      ' '
    ),
    ''
  ) as "given"
FROM
  "patient" as r
  JOIN LATERAL jsonb_path_query(r.resource, '$  . name  [*]') q1_1 
  ON true
LIMIT 100

You can run Aidbox locally or in cloud Sandbox in minutes - https://www.health-samurai.io/aidbox#run.

You can visually build and debug ViewDefiniton with FHIRPath autocompletes using our ViewDefinition Builder.

ViewDefinition in FHIR is a powerful tool that allows for flexible data representation management by creating customizable views based on various conditions and parameters. This is particularly useful when you need to tailor the display of information to meet specific user or system requirements. By using ViewDefinition, you can significantly simplify and expedite the data integration process while ensuring data integrity and accessibility.

‍
To practically explore the capabilities of ViewDefinition, you can install the free version of Aidbox. It allows you to test all features without limitations, providing an ideal environment for development and experimentation.

‍

Demo the ELT implementation for PostgreSQL using Aidbox, open-source ViewDefinition Builder, and Grafana.

Join the Working Group

If you want to ask any questions or contribute to SQL on FHIR, join us in the chat at chat.fhir.org. For personal questions, feel free to ask me on LinkedIn.

‍