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 using Batch/Transaction.
Copy the following snippet to the Aidbox.Cloud REST Console.
{% tabs %} {% tab title="Request" %}
POST /
Accept: text/yaml
Content-Type: text/yaml
type: transaction
entry:
- resource:
id: patient1
name:
- given: [Max]
family: Turikov
request:
method: POST
url: "/Patient"
- resource:
id: patient2
name:
- given: [Alex]
family: Antonov
request:
method: POST
url: "/Patient"
- resource:
id: enc1
status: draft
subject:
resourceType: Patient
id: patient1
class:
code: enc1
request:
method: POST
url: "/Encounter"
- resource:
id: enc2
status: draft
subject:
resourceType: Patient
id: patient1
class:
code: enc2
request:
method: POST
url: "/Encounter"
- resource:
id: enc3
status: draft
subject:
resourceType: Patient
id: patient2
class:
code: enc3
request:
method: POST
url: "/Encounter"{% endtab %}
{% tab title="Response" %}
type: transaction-response
resourceType: Bundle
entry:
- resource:
name:
- given:
- Max
family: Turikov
id: >-
patient1
resourceType: Patient
meta:
lastUpdated: '2023-03-27T11:05:32.269055Z'
createdAt: '2023-03-27T11:05:32.269055Z'
versionId: '3'
response:
etag: '3'
location: /Patient/patient1/_history/3
status: '201'
lastModified: '2023-03-27T11:05:32.269055Z'
- resource:
name:
- given:
- Alex
family: Antonov
id: >-
patient2
resourceType: Patient
meta:
lastUpdated: '2023-03-27T11:05:32.269055Z'
createdAt: '2023-03-27T11:05:32.269055Z'
versionId: '4'
response:
etag: '4'
location: /Patient/patient2/_history/4
status: '201'
lastModified: '2023-03-27T11:05:32.269055Z'
- resource:
class:
code: enc1
status: draft
subject:
id: >-
patient1
resourceType: Patient
id: >-
enc1
resourceType: Encounter
meta:
lastUpdated: '2023-03-27T11:05:32.269055Z'
createdAt: '2023-03-27T11:05:32.269055Z'
versionId: '5'
response:
etag: '5'
location: /Encounter/enc1/_history/5
status: '201'
lastModified: '2023-03-27T11:05:32.269055Z'
- resource:
class:
code: enc2
status: draft
subject:
id: >-
patient1
resourceType: Patient
id: >-
enc2
resourceType: Encounter
meta:
lastUpdated: '2023-03-27T11:05:32.269055Z'
createdAt: '2023-03-27T11:05:32.269055Z'
versionId: '6'
response:
etag: '6'
location: /Encounter/enc2/_history/6
status: '201'
lastModified: '2023-03-27T11:05:32.269055Z'
- resource:
class:
code: enc3
status: draft
subject:
id: >-
patient2
resourceType: Patient
id: >-
enc3
resourceType: Encounter
meta:
lastUpdated: '2023-03-27T11:05:32.269055Z'
createdAt: '2023-03-27T11:05:32.269055Z'
versionId: '7'
response:
etag: '7'
location: /Encounter/enc3/_history/7
status: '201'
lastModified: '2023-03-27T11:05:32.269055Z'
id: >-
8{% endtab %} {% endtabs %}
We created 2 patients and 3 encounters that are linked to those patients.
Aidbox uses PostgreSQL (open-source DBMS), which allows expressing very complex queries. Let's try to implement our task in SQL queries.
Let's get a list of patients. Access the DB Console of our box and run the following code snippets:
.png)
{% code title="patients.sql" %}
SELECT
id, resource_type, resource
FROM
patient;{% endcode %}
| id | resource_type | resource |
|---|---|---|
patient1 |
Patient |
{"name":[{"given":["Max"],"family":"Turikov"}]} |
patient2 |
Patient |
{"name":[{"given":["Alex"],"family":"Antonov"}]} |
Next, we want to add the patient id and resource_type into resource.
{% code title="patients.sql" %}
SELECT
id, resource_type,
jsonb_set(
jsonb_set(resource, '{id}', to_jsonb(id)),
'{resourceType}',
to_jsonb(resource_type))
FROM
patient;{% endcode %}
| id | resource_type | resource |
|---|---|---|
patient1 |
Patient |
{"id":"patient1","name":[{"given":["Max"],"family":"Turikov"}],"resource_type":"Patient"} |
patient2 |
Patient |
{"id":"patient2","name":[{"given":["Alex"],"family":"Antonov"}],"resource_type":"Patient"} |
{% hint style="info" %} Curious how to work with JSON in PostgreSQL? Join our community chat. {% endhint %}
Also, we can obtain a list of encounters for each patient:
{% code title="patients-encounters.sql" %}
SELECT
p.id AS patient_id,
e.id AS encounter_id,
e.resource AS encounter
FROM
patient AS p
JOIN encounter AS e
ON p.id = e.resource->'subject'->>'id';{% endcode %}
| patient_id | encounter_id | encounter |
|---|---|---|
patient1 |
enc1 |
{"status":"draft","subject":{"id":"patient1","resourceType":"Patient"}} |
patient1 |
enc2 |
{"status":"draft","subject":{"id":"patient1","resourceType":"Patient"}} |
patient2 |
enc3 |
{"status":"draft","subject":{"id":"patient2","resourceType":"Patient"}} |
Our next step is obtaining aggregated data by patients.
{% code title="patient-encounters.sql" %}
SELECT
p.id AS patient_id,
json_agg(e.resource::jsonb) AS encounters
FROM
patient AS p
JOIN encounter AS e
ON p.id = e.resource->'subject'->>'id'
GROUP BY p.id;{% endcode %}
| patient_id | encounters |
|---|---|
patient1 |
[{"status":"draft","subject":{"id":"patient1","resourceType":"Patient"}}, {"status":"draft","subject":{"id":"patient1","resourceType":"Patient"}}] |
patient2 |
[{"status":"draft","subject":{"id":"patient2","resourceType":"Patient"}}] |
Looks good but there's no information about the encounter id.
{% code title="patients-encounters-with-ids.sql" %}
SELECT
p.id AS patient_id,
json_agg(e.resource::jsonb) AS encounters
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;{% endcode %}
| patient_id | encounters |
|---|---|
patient1 |
[{"id":"enc1","status":"draft","subject":{"id":"patient1","resourceType":"Patient"},"resource_type":"Encounter"},{"id":"enc2","status":"draft","subject":{"id":"patient1","resourceType":"Patient"},"resource_type":"Encounter"}] |
patient2 |
[{"id":"enc3","status":"draft","subject":{"id":"patient2","resourceType":"Patient"},"resource_type":"Encounter"}] |
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.
{% code title="patients-with-encounters-and-ids.sql" %}
SELECT
p.id AS id,
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 = 'patient1';{% endcode %}
The result should look like the following table (but without pretty printing):
| id | resource |
|---|---|
patient1 |
|
Now let's make the results of this query accessible via REST API. To do that, we need to create the AidboxQuery resource:
{% tabs %} {% tab title="Request" %}
POST /AidboxQuery
Accept: text/yaml
Content-Type: text/yaml
id: patient-with-encounters
params:
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}};{% endtab %}
{% tab title="Response" %}
# Status: 201
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}};
params:
patient-id:
isRequired: true
id: >-
patient-with-encounters
resourceType: AidboxQuery
meta:
lastUpdated: '2022-07-08T12:15:55.520339Z'
createdAt: '2022-07-08T12:15:55.520339Z'
versionId: '21'{% endtab %} {% endtabs %}
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!
{% tabs %} {% tab title="Request" %}
GET /$query/patient-with-encounters?patient-id=patient1
{% endtab %}
{% tab title="Response" %}
# Status: 200
data:
- resource:
id: patient1
name:
- given: [Max]
family: Turikov
encounters:
- id: enc1
status: draft
subject: {id: patient1, resourceType: Patient}
resourceType: Encounter
- id: enc2
status: draft
subject: {id: patient1, resourceType: Patient}
resourceType: Encounter
resourceType: Patient
# ...{% endtab %} {% endtabs %}
{% tabs %} {% tab title="Request" %}
GET /$query/patient-with-encounters?patient-id=patient2
{% endtab %}
{% tab title="Response" %}
# Status: 200
data:
- resource:
id: patient2
name:
- given: [Alex]
family: Antonov
encounters:
- id: enc3
status: draft
subject: {id: patient2, resourceType: Patient}
resourceType: Encounter
resourceType: Patient
# ...{% endtab %} {% endtabs %}
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.
{% hint style="info" %} Want to know more about Aidbox, FHIR, and custom search? Join our community chat. {% endhint %}