HOWTO Build a health database and FHIR API Server in 15 mins using open source

riksmithies · 25 June 2019 19:16

Someone may, but in general this is difficult for the reasons mentioned here:

(free registration required.)

It says “there is no standard DDL like this because everyone makes different decisions about how they want their tables laid out. Most of the resources have 3-4 levels of nested structure, though there may be more (and some are recursive). You have to decide this for yourself”

There is also an initiative called SQL-on-FHIR that looks at this issue. I don’t know how far it has got:

github.com

FHIR/sql-on-fhir/blob/master/sql-on-fhir.md

# Simplified SQL Projection of FHIR Resources
An SQL-based projection of FHIR resources would open up large, portable datasets to a number of analytic tools. For example, here is a query over FHIR data that computes the average HDL levels for the full system:

```sql
SELECT subject.reference,
       AVG(value.quantity.value) avg_hdl
FROM observation o,
     UNNEST(o.code.coding) c
WHERE c.system = 'http://loinc.org' AND
      c.code = '2085-9' AND
      o.effective.datetime > '2017'
GROUP BY subject.reference
```

Importantly, this approach preserves the nested structures and arrays of FHIR resources using ANSI SQL standards. The results of these queries can then be used as arbitrary tables for further analysis in R or other tools.

Example queries using this pattern can be found in the [examples](examples/) directory of this project.

## Logical Representation for SQL Users
This proposal focuses on a logical representation of FHIR as seen from an SQL user. This may either be a direct reflection of a physical data store, or a dynamic view over a different physical representation, depending on the design of the underlying system. This allows a logical view to best fit the intuitions of a user querying the data while preserving potential physical optimization underneath.

This file has been truncated. show original

But many FHIR implementations either don’t use FHIR natively (FHIR as a facade onto your own non-FHIR RDBMS) or they use NoSQL storage, so there are no tables and no DDL.

Rik