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 
   SELECT id as id, jsonb_array_elements(resource->'name') AS nm 
   FROM patient
) _ 
WHERE nm->>'use' = 'official' and nm->>'family' ilike '%Chalmers%' 

Here is the version with fhirpath:

SELECT id, resource->'name' 
 FROM patient
  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:

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 
   $$identifier.where(system = 'ssn').value.exists()$$

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

INSERT INTO patient (resource) 
    "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) 
   "identifier": [{"system": "ssn", "value": "777777"}]
-- INSERT 0 1

Last updated