From FHIR to tables

FHIR is nested. Analysis wants rows.

FHIR records are nested: objects inside objects, lists inside objects. Analysis tools want flat tables, with rows and columns. This lesson is about the bridge between the two.

From a graph to a table

The SQL on FHIR guide states the problem. FHIR is represented as a graph of resources, each with nested data elements. Most analytic and machine learning work needs that data prepared, using transformations and tabular projections from its original form. And there is no standard way to write and share those transformations.

• Today · A graph of resources · Each with nested elements. · layers
• Analysis needs · Tabular projections · Rows and columns. · chart
• Missing piece · A standard way · To write and share them. · link

Three parts of SQL on FHIR

The guide proposes three complementary parts. A view definition: a portable format for defining tabular views of FHIR data. Each one is tied to a single resource type, and uses FHIR path expressions to define columns, filters and unnesting. An SQL query: a shareable, parameterized query. And an HTTP API: standard operations for running views and queries. The guide is a draft.

• ViewDefinition · A portable tabular view · layers
• SQLQuery · A shareable SQL query · search
• HTTP API · Run views and queries · share

Parts of a record sit inside other parts

Here is a made up observation, as a card. Turn it over, and the data behind it is nested. The code sits inside a coding list, inside code. The value sits inside value quantity. And the patient is a reference. To get one flat row, a view has to reach into each of those places.

One nested record becomes one row

Here is a query that does by hand what a view definition does by design. It reaches into the nested record with paths: the id, the first code, the value, and the patient reference. The result is a flat row, with one column for each. The code system and the numbers are made up.

• SELECT doc ->> '$.id' AS id,
• doc ->> '$.code.coding[0].code'
• AS code,
• doc ->> '$.valueQuantity.value'
• AS value,
• doc ->> '$.subject.reference'
• AS patient
• FROM observations;

Nested record, flat table

Same facts, two shapes. The nested record is how FHIR exchanges data: rich, connected, and shaped for the resource. The flat table is how analysts work: one row for each thing, one column for each fact, ready for the queries you learned in this course.

Check yourself

Pause and think. What is a view definition in SQL on FHIR? The answer: a portable format for defining a tabular view of FHIR data.

• A portable format for defining a tabular view of FHIR data
• A new kind of patient record
• A way to delete FHIR resources
• A replacement for the FHIR specification

Keep these in mind

FHIR records are nested, and analysis wants rows. SQL on FHIR proposes a standard way to flatten them. A view definition is tied to one resource type. And the guide is still a draft.

• FHIR records are nested; analysis wants rows
• SQL on FHIR proposes a standard way to flatten them
• A ViewDefinition is tied to one resource type
• The guide is still a draft