Running SQL on FHIR views

The Pathling library leverages the SQL on FHIR specification to provide a way to project FHIR data into easy-to-use tabular forms.

Once you have transformed your FHIR data into tabular views, you can choose to keep it in a Spark dataframe and continue to work with in Apache Spark, or export it to Python or R dataframes or a variety of different file formats for use in the tool of your choice.

Python

from pathling import PathlingContext

pc = PathlingContext.create()
data = pc.read.ndjson("/some/file/location")

result = data.view(
    resource="Patient",
    select=[
        {"column": [{"path": "getResourceKey()", "name": "patient_id"}]},
        {
            "forEach": "address",
            "column": [
                {"path": "line.join('\\n')", "name": "street"},
                {"path": "use", "name": "use"},
                {"path": "city", "name": "city"},
                {"path": "postalCode", "name": "zip"},
            ],
        },
    ],
)

display(result)

R

library(pathling)

pc <- pathling_connect()
data <- pc %>% pathling_read_ndjson("/some/file/location")

result <- data %>% ds_view(
        resource = "Patient",
        select = list(
                list(
                        column = list(
                                list(path = "getResourceKey()", name = "patient_id")
                        )
                ),
                list(
                        forEach = "address",
                        column = list(
                                list(path = "line.join('\\n')", name = "street"),
                                list(path = "use", name = "use"),
                                list(path = "city", name = "city"),
                                list(path = "postalCode", name = "zip")
                        )
                )
        )
)

result %>% show()

Java

package au.csiro.pathling.examples;

import static au.csiro.pathling.views.FhirView.column;
import static au.csiro.pathling.views.FhirView.columns;
import static au.csiro.pathling.views.FhirView.forEach;

import au.csiro.pathling.library.PathlingContext;
import au.csiro.pathling.library.io.source.NdjsonSource;
import au.csiro.pathling.views.FhirView;
import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;

class MyApp {

    public static void main(String[] args) {
        PathlingContext pc = PathlingContext.create();
        NdjsonSource data = pc.read()
                .ndjson("/some/file/location");

        FhirView view = FhirView.ofResource("Observation")
                .select(
                        columns(
                                column("patient_id", "getResourceKey()")
                        ),
                        forEach("code.coding",
                                column("code_system", "system"),
                                column("code_code", "code"),
                                column("code_display", "display")
                        )
                ).build();

        Dataset<Row> result = data.view(view).execute();

        result.show();
    }
}

Scala

import au.csiro.pathling.library.PathlingContext
import au.csiro.pathling.library.io.source.NdjsonSource
import au.csiro.pathling.views.FhirView
import au.csiro.pathling.views.FhirView._
import org.apache.spark.sql.{Dataset, Row, SparkSession}

val pc = PathlingContext.create()
val data: NdjsonSource = pc.read()
        .ndjson("/some/file/location")

val view: FhirView = FhirView.ofResource("Observation")
        .select(
            columns(
                column("patient_id", "getResourceKey()")
            ),
            forEach("code.coding",
                column("code_system", "system"),
                column("code_code", "code"),
                column("code_display", "display")
            ),
        ).build()

val result: Dataset[Row] = data.view(view).execute()

result.show()

The result of this query would look something like this:

patient_id	street	use	city	zip
1	398 Kautzer Walk Suite 62	home	Barnstable	02675
1	186 Nitzsche Forge	work	Revere	02151
2	1087 Quitzon Club	home	Plymouth	NULL
3	442 Bruen Arcade	home	Nantucket	NULL
4	858 Miller Junction Apt 61	work	Brockton	02301

To join the rows of one view to the rows of another, use the getResourceKey() and getReferenceKey() functions. See SQL on FHIR functions for the keys that they produce and the references that they support.

Recursive elements, such as item within a Questionnaire, are only encoded to the configured maximum nesting level. Paths that navigate deeper than this behave as if the data were absent: a forEach produces no rows, a forEachOrNull produces a single row of nulls, and a column is null, or an empty array if it is a collection column. See Navigation beyond the encoded depth for how this applies to FHIRPath expressions in general.

For a more comprehensive example demonstrating SQL on FHIR queries with multiple views, complex transformations and joins, see the SQL on FHIR example.