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