FHIRPath and SQL

[niquola]

FHIRPath offers path-based extraction of data from FHIR data and is used extensively within the FHIR specification itself. It is an excellent fit for its role, but is not sufficient for users looking for join, aggregation, or statistical operations over FHIR datasets. This proposal attempts to fill that need, but will follow conventions established by FHIRPath wherever applicable.

We tested very efficient implementation of FHIRPath in PostgreSQL, which has good fits with SQL.
If database is able to support FHIRPath (at least basic subset) - it can be very useful for SQL-on-FHIR.

Here is your sample using fhirpath:

SELECT fhirpath(resource, 'subject.reference'), 
       AVG(fhirpath(resource, 'value.quantity.value'))
FROM observation o
WHERE 
      fhirpath(resource, "code.where(system = 'http://loinc.org'') =  '2085-9' 
      fhirpath(resource,"effectiveDateTime') > '2017'
GROUP BY fhirpath(resource, 'subject.reference')

Using functional indexes - this query can be optimised very well