SQL on FHIR - Tabular views of FHIR data using FHIRPath

Abstract

Challenges exist with the adoption of Fast Healthcare Interoperability Resources (FHIR) within analytics, including the difficulty in transforming complex data structures, and performance issues when querying large datasets in their native JSON or XML formats. In 2023, an international working group began work on a solution to this problem that would be easier to implement than existing approaches. Over the course of 18 months, the group authored a new specification and validated it through the development and testing of multiple independent implementations. The outcome of this work is a standard, implementation-agnostic method for defining views that produce tabular data from FHIR resources. SQL on FHIR view definitions can be written to cover common use cases and can be executed across a variety of technology platforms. We evaluate the feasibility of this approach by replicating findings from an existing study across multiple view runner and database implementations, demonstrating portability and consistency.

Fig. 1. Architectural layers.

Overview of the conceptual architecture for SQL on FHIR. The data layer represents FHIR data sources, including FHIR servers, NDJSON files, and FHIR data held within databases. The view layer uses view definitions to define table-like views of the data, and uses view runners to execute these definitions. The analytics layer consumes the tabular data produced by the views as part of analysis tasks. This diagram is reproduced with permission from the SQL on FHIR Working Group.

Fig. 2. View definition profiles.

Profiles defined as part of the SQL on FHIR view definition specification. The base profile has minimal requirements for quick, on-demand use. The shareable profile adds rules for identifying resources, versioning, and limits FHIRPath usage to work better across different systems. The tabular profile further restricts column data types to simple values only, making the data compatible with formats like CSV and systems that cannot handle complex data.

Fig. 3. Snapshot of MIMIC-IV on FHIR dataset used in evaluation.

Size and composition of the dataset used for testing. The dataset contains 299,712 patient records, 929,499 clinical encounters, and 461,098,908 observations from 2008 to 2019, all following the FHIR R4 standard. This provides sufficient data to test how well the SQL on FHIR approach works for preparing large clinical datasets for analysis.

Fig. 4. View definitions and compositional queries used in evaluation.

The raw FHIR resources were transformed into a set of intermediate views, based on demographics, encounter details and different types of observations. These views were then combined using SQL queries to produce the final set of input variables required for the analysis.

Fig. 5. Evaluation of SQL on FHIR views.

Approach used to evaluate the use of SQL on FHIR views for replicating the findings of the original study. The view definitions were executed using two different view runners (Pathling and Aidbox), producing two sets of intermediate views. Then the same SQL queries were executed against both sets of views, producing two sets of input variables. Finally, the same analysis pipeline was executed against both sets of input variables, producing two sets of results. The results were then compared to each other and to the original results.