Set up uniqueness in Resource
Suppose we want to store Patient resource only if patient with such email does not already exist in the database. Hence we need to make
Patient.telecom.value unique where Patient.telecom.system = 'email'.CREATE UNIQUE INDEX patient_email_unique_idx1
ON patient ((jsonb_path_query_first(resource, '$.telecom[*] ? (@.system == "email").value') #>> '{}'));
In this example jsonb_path_query_first PostgreSQL function will check first email in Patient.telecom array. This works if the Patient resource can have only one email.
If it can have two or more emails, the only solution we see is to create new
patient_email table, store emails into it via PostgreSQL triggers when inserting/updating in patient table and make Unique index for patient_email as in the example above.Last modified 19d ago