Fhirpath.pg

Why fhirpath?

To search though jsonb resources in database sometimes you need to access deeply nested attributes by specific criteria. For example to search patient by official name you have to do something like this:

SELECT id, nm 
FROM (
   SELECT id as id, jsonb_array_elements(resource->'name') AS nm 
   FROM patient
) _ 
WHERE nm->>'use' = 'official' and nm->>'family' ilike '%Chalmers%' 
LIMIT 10;

Here is the version with fhirpath:

SELECT id, resource->'name' 
 FROM patient
 WHERE 
  fhirpath(resource,$$name.where(use='official').family$$)->>0  ilike 'Chal%';

Implement FHIR search with fhirpath

Having fhirpath in your database also is very handy to implement FHIR Search API. In FHIR SearchParameters are described by fhirpath expressions. For example for Patient we have following search parameters:

Name	Type	Expression
active	​token​	Patient.active
address	​string​	Patient.address
address-city	​string​	Patient.address.city
address-country	​string​	Patient.address.country
address-postalcode	​string​	Patient.address.postalCode
address-state	​string​	Patient.address.state
address-use	​token​	Patient.address.use
animal-breed	​token​	Patient.animal.breed
animal-species	​token​	Patient.animal.species
birthdate	​date​	Patient.birthDate
death-date	​date​	Patient.deceased.as(DateTime)
deceased	​token​	Patient.deceased.exists()
email	​token​	Patient.telecom.where(system='email')
family	​string​	Patient.name.family
gender	​token​	Patient.gender
general-practitioner	​reference​	Patient.generalPractitioner (Practitioner, Organization)
given	​string​	Patient.name.given
identifier	​token​	Patient.identifier
language	​token​	Patient.communication.language
link	​reference​	Patient.link.other (Patient, RelatedPerson)
name	​string​	Patient.name
organization	​reference​	Patient.managingOrganization (Organization)
phone	​token​	Patient.telecom.where(system='phone')
phonetic	​string​	Patient.name
telecom	​token​	Patient.telecom

With FHIR search there is one tricky moment - most of expressions return complex types like HumanName or Identifier, which is not easy to search by SQL. So fhirpath provides specialised function to output search friendly results:

SELECT fhirsearch_string(resource, $$Patient.name.where(user='official')$$, 'HumanName')
-- '^John$ ^Doe$ ^Johny$'
​
-- so to search for startWith we can do
SELECT * from patient
WHERE fhirsearch_string(...) ilike '%^Joh%'

Invariants with fhirpath.pg

With fhirpath we can create invariants as PostgreSQL Constraints. Let's add a check that all patients have SSN number:

-- clear patient table if you have somthing invalid in
truncate patient;
​
ALTER TABLE patient 
ADD CONSTRAINT patient_ssn 
CHECK (
(
  fhirpath(
   resource, 
   $$identifier.where(system = 'ssn').value.exists()$$
  )->>0
 )::boolean
);

Now when you will try to add patient without SSN - you will see an ERROR:

INSERT INTO patient (resource) 
VALUES ($$
  {
    "resourceType":"Patient",
    "id":"example", 
    "identifier": [{"value": "777777"}]}
$$);
-- ERROR:  new row for relation "patient" violates check constraint "patient_ssn"
--DETAIL:  Failing row contains ({"id": "example", "identifier": [{"value": "777777"}], "resource...).
​
-- but this will work
INSERT INTO patient (resource) 
VALUES ($$
  {
   "resourceType":"Patient",
   "id":"example", 
   "identifier": [{"system": "ssn", "value": "777777"}]
   }
$$);
-- INSERT 0 1