FHIR search has a lot of capabilities, but in some cases, there aren't enough. For example, we want to get a patient resource with an additional field 'encounters' that contains all encounters of that patient, but it's not possible to implement it with FHIR search API. Aidbox has a solution for such complex tasks, and this tutorial is about how to solve this kind of problem.
Prepare Data
We need some sample data to see results of our queries. Let's create it using Batch/Transaction.
Copy the following snippet to the Aidbox.Cloud REST Console.
Curious how to work with JSON in PostgreSQL? Join our community chat.
Also, we can obtain a list of encounters for each patient:
patients-encounters.sql
SELECTp.id AS patient_id,e.id AS encounter_id,e.resource AS encounter FROMpatient AS pJOIN encounter AS eON p.id = e.resource->'subject'->>'id';
Our next step is obtaining aggregated data by patients.
patient-encounters.sql
SELECTp.id AS patient_id,json_agg(e.resource::jsonb) AS encounters FROMpatient AS pJOIN encounter AS eON p.id = e.resource->'subject'->>'id'GROUP BY p.id;
Looks good but there's no information about the encounter id.
patients-encounters-with-ids.sql
SELECTp.id AS patient_id,json_agg(e.resource::jsonb) AS encountersFROM (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}', to_jsonb(resource_type)) ASresourceFROM patient) AS pJOIN (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)), '{resourceType}', to_jsonb(resource_type)) ASresourceFROM encounter) AS eON p.id = e.resource->'subject'->>'id'GROUP BY p.id;
Additionally, we added resourceType and id to the patient resource but didn't use it yet. Let's put encounters to the patient resource and take only one patient by the specified id.
patients-with-encounters-and-ids.sql
SELECTp.id AS id,jsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) ASresourceFROM (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}', to_jsonb(resource_type)) ASresourceFROM patient) AS pJOIN (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)), '{resourceType}', to_jsonb(resource_type)) ASresourceFROM encounter) AS eON p.id = e.resource->'subject'->>'id'GROUP BY p.id, p.resourceHAVING p.id ='patient1';
The result should look like the following table (but without pretty printing):
Now let's make the results of this query accessible via REST API. To do that, we need to create the AidboxQuery resource:
POST /AidboxQueryAccept:text/yamlContent-Type:text/yamlid:patient-with-encountersparams:patient-id: {isRequired:true}query:| SELECT jsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) AS resource FROM (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)), '{resourceType}', to_jsonb(resource_type)) AS resource FROM patient) AS p JOIN (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)), '{resourceType}', to_jsonb(resource_type)) AS resource FROM encounter) AS e ON p.id = e.resource->'subject'->>'id' GROUP BY p.id, p.resource HAVING p.id = {{params.patient-id}};
# Status: 201query:>- SELECT jsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) AS resource FROM (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)), '{resourceType}', to_jsonb(resource_type)) AS resource FROM patient) AS p JOIN (SELECT id, resource_type, jsonb_set( jsonb_set(resource, '{id}', to_jsonb(id)), '{resourceType}', to_jsonb(resource_type)) AS resource FROM encounter) AS e ON p.id = e.resource->'subject'->>'id' GROUP BY p.id, p.resource HAVING p.id = {{params.patient-id}};params:patient-id:isRequired:trueid:>- patient-with-encountersresourceType:AidboxQuerymeta:lastUpdated:'2022-07-08T12:15:55.520339Z'createdAt:'2022-07-08T12:15:55.520339Z'versionId:'21'
Pay attention to the end of the query: we used {{params.patient-id}} which takes the value from the request and passes it to the query securely (using PostgreSQL PREPARE statement). This means that the user of our custom search can change some parameters of the query and get different results.
Let's try it in action!
GET /$query/patient-with-encounters?patient-id=patient1