​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.
We need some sample data to see results of our queries. Let's create it.
Copy the following snippet to the Aidbox.Cloud REST Console
.
You can use the Copy button near the top right corner of a snippet to avoid copying trailing spaces.
POST /​type: transactionentry:- resource:id: patient1name:- given: [Max]family: Turikovrequest:method: POSTurl: "/Patient"​- resource:id: patient2name:- given: [Alex]family: Antonovrequest:method: POSTurl: "/Patient"​- resource:id: enc1status: draftsubject:resourceType: Patientid: patient1request:method: POSTurl: "/Encounter"​- resource:id: enc2status: draftsubject:resourceType: Patientid: patient1request:method: POSTurl: "/Encounter"​- resource:id: enc3status: draftsubject:resourceType: Patientid: patient2request:method: POSTurl: "/Encounter"
# Status: 200​id: '281'type: transaction-responseresourceType: Bundleentry:- resource:name:- given: [Max]family: Turikovid: patient1resourceType: Patientmeta:lastUpdated: '2018-11-27T09:47:27.412Z'versionId: '281'tag:- {system: 'https://aidbox.app', code: created}status: 201- resource:name:- given: [Alex]family: Antonovid: patient2resourceType: Patientmeta:lastUpdated: '2018-11-27T09:47:27.412Z'versionId: '281'tag:- {system: 'https://aidbox.app', code: created}status: 201- resource:status: draftsubject: {id: patient1, resourceType: Patient}id: enc1resourceType: Encountermeta:lastUpdated: '2018-11-27T09:47:27.412Z'versionId: '281'tag:- {system: 'https://aidbox.app', code: created}status: 201- resource:status: draftsubject: {id: patient1, resourceType: Patient}id: enc2resourceType: Encountermeta:lastUpdated: '2018-11-27T09:47:27.412Z'versionId: '281'tag:- {system: 'https://aidbox.app', code: created}status: 201- resource:status: draftsubject: {id: patient2, resourceType: Patient}id: enc3resourceType: Encountermeta:lastUpdated: '2018-11-27T09:47:27.412Z'versionId: '281'tag:- {system: 'https://aidbox.app', code: created}status: 201
We created 2 patients and 3 encounters that are linked to those patients.
Aidbox uses PostgreSQL (super advanced open-source DBMS), which allows expressing very complex queries. Let's try to implement our task in SQL queries.
First of all, let's try to obtain a list of patients. Access the DB Console
of our box and run the following code snippets:
patients.sqlSELECTid, resource_type, resourceFROMpatient;
id | resource_type | resource |
|
|
|
|
|
|
Next, we want to add the patient id and resource_type into resource.
patients.sqlSELECTid, resource_type,jsonb_set(jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}',to_jsonb(resource_type))FROMpatient;
id | resource_type | resource |
|
|
|
|
|
|
Also, we can obtain a list of encounters for each patient:
patients-encounters.sqlSELECTp.id AS patient_id,e.id AS encounter_id,e.resource AS encounterFROMpatient AS pJOIN encounter AS eON p.id = e.resource->'subject'->>'id';
patient_id | encounter_id | encounter |
|
|
|
|
|
|
|
|
|
Our next step is obtaining aggregated data by patients.
patient-encounters.sqlSELECTp.id AS patient_id,json_agg(e.resource::jsonb) AS encountersFROMpatient AS pJOIN encounter AS eON p.id = e.resource->'subject'->>'id'GROUP BY p.id;
patient_id | encounters |
|
|
|
|
Looks good but there's no information about encounter id's.
patients-encounters-with-ids.sqlSELECTp.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))AS resourceFROM patient) AS pJOIN (SELECT id,resource_type,jsonb_set(jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}',to_jsonb(resource_type))AS resourceFROM encounter) AS eON p.id = e.resource->'subject'->>'id'GROUP BY p.id;
patient_id | encounters |
|
|
|
|
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 specified id.
patients-with-encounters-and-ids.sqlSELECTp.id AS id,jsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) AS resourceFROM (SELECT id,resource_type,jsonb_set(jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}',to_jsonb(resource_type))AS resourceFROM patient) AS pJOIN (SELECT id,resource_type,jsonb_set(jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}',to_jsonb(resource_type))AS resourceFROM 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):
id | resource |
|
|
Now let's make the results of this query accessible via REST API. To do that, we need to create AidboxQuery
resource:
POST /AidboxQuery​id: patient-with-encountersparams:patient-id: {isRequired: true}query: |SELECTjsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) AS resourceFROM (SELECT id,resource_type,jsonb_set(jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}',to_jsonb(resource_type))AS resourceFROM patient) AS pJOIN (SELECT id,resource_type,jsonb_set(jsonb_set(resource, '{id}', to_jsonb(id)),'{resourceType}',to_jsonb(resource_type))AS resourceFROM encounter) AS eON p.id = e.resource->'subject'->>'id'GROUP BY p.id, p.resourceHAVING p.id = {{params.patient-id}};
# Status: 201​query: "SELECT\njsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb)\\ AS resource\nFROM (SELECT id, \n resource_type,\n jsonb_set(\n \\ jsonb_set(resource, '{id}', to_jsonb(id)),\n '{resourceType}', \n \\ to_jsonb(resource_type)) \n AS resource \n FROM patient) AS p\n\JOIN (SELECT id, \n resource_type, \n jsonb_set(\n jsonb_set(resource,\\ '{id}', to_jsonb(id)), \n '{resourceType}', \n to_jsonb(resource_type))\\ \n AS resource \n FROM encounter) AS e\nON p.id = e.resource->'subject'->>'id'\n\GROUP BY p.id, p.resource\nHAVING p.id = 'patient1';"params:patient-id: {isRequired: true}id: patient-with-encountersresourceType: AidboxQuerymeta:lastUpdated: '2018-11-27T13:30:45.670Z'versionId: '37'tag:- {system: 'https://aidbox.app', code: created}
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
# Status: 200​data:- resource:id: patient1name:- given: [Max]family: Turikovencounters:- id: enc1status: draftsubject: {id: patient1, resourceType: Patient}resourceType: Encounter- id: enc2status: draftsubject: {id: patient1, resourceType: Patient}resourceType: EncounterresourceType: Patient# ...
GET /$query/patient-with-encounters?patient-id=patient2
# Status: 200​data:- resource:id: patient2name:- given: [Alex]family: Antonovencounters:- id: enc3status: draftsubject: {id: patient2, resourceType: Patient}resourceType: EncounterresourceType: Patient# ...
We got all the needed data in the exact shape we wanted. Additional information about custom queries can be found in REST API $query documentation.