Links
Comment on page

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