Fhirpath.pg
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%';
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 | Patient.active | |
address | Patient.address | |
address-city | Patient.address.city | |
address-country | Patient.address.country | |
address-postalcode | Patient.address.postalCode | |
address-state | Patient.address.state | |
address-use | Patient.address.use | |
animal-breed | Patient.animal.breed | |
animal-species | Patient.animal.species | |
birthdate | Patient.birthDate | |
death-date | Patient.deceased.as(DateTime) | |
deceased | Patient.deceased.exists() | |
email | Patient.telecom.where(system='email') | |
family | Patient.name.family | |
gender | Patient.gender | |
general-practitioner | ||
given | Patient.name.given | |
identifier | Patient.identifier | |
language | Patient.communication.language | |
link | ||
name | Patient.name | |
organization | ||
phone | Patient.telecom.where(system='phone') | |
phonetic | Patient.name | |
telecom | 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%'
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
Last modified 4yr ago