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.
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"}]}
]
}
]
ViewDefinitions use a minimal subset of FHIRPath to make implementation as simple as possible. Additionally, the specification introduces a few special functions:
Let’s walk through each function in detail.
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
}, {})
})
}
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;
})
})
}
{
"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)
})
})
}
{
"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}]
{
"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": 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.
The ViewDefinition can be executed by an engine to produce flat views from FHIR resources. There are two categories of engines:
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 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.
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.
Get in touch with us today!