Custom Search

Intro

​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. 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.
Request
Response
1
POST /
2
Accept: text/yaml
3
Content-Type: text/yaml
4
​
5
type: transaction
6
entry:
7
- resource:
8
id: patient1
9
name:
10
- given: [Max]
11
family: Turikov
12
request:
13
method: POST
14
url: "/Patient"
15
​
16
- resource:
17
id: patient2
18
name:
19
- given: [Alex]
20
family: Antonov
21
request:
22
method: POST
23
url: "/Patient"
24
​
25
- resource:
26
id: enc1
27
status: draft
28
subject:
29
resourceType: Patient
30
id: patient1
31
request:
32
method: POST
33
url: "/Encounter"
34
​
35
- resource:
36
id: enc2
37
status: draft
38
subject:
39
resourceType: Patient
40
id: patient1
41
request:
42
method: POST
43
url: "/Encounter"
44
​
45
- resource:
46
id: enc3
47
status: draft
48
subject:
49
resourceType: Patient
50
id: patient2
51
request:
52
method: POST
53
url: "/Encounter"
Copied!
1
# Status: 200
2
​
3
id: '281'
4
type: transaction-response
5
resourceType: Bundle
6
entry:
7
- resource:
8
name:
9
- given: [Max]
10
family: Turikov
11
id: patient1
12
resourceType: Patient
13
meta:
14
lastUpdated: '2018-11-27T09:47:27.412Z'
15
versionId: '281'
16
tag:
17
- {system: 'https://aidbox.app', code: created}
18
status: 201
19
- resource:
20
name:
21
- given: [Alex]
22
family: Antonov
23
id: patient2
24
resourceType: Patient
25
meta:
26
lastUpdated: '2018-11-27T09:47:27.412Z'
27
versionId: '281'
28
tag:
29
- {system: 'https://aidbox.app', code: created}
30
status: 201
31
- resource:
32
status: draft
33
subject: {id: patient1, resourceType: Patient}
34
id: enc1
35
resourceType: Encounter
36
meta:
37
lastUpdated: '2018-11-27T09:47:27.412Z'
38
versionId: '281'
39
tag:
40
- {system: 'https://aidbox.app', code: created}
41
status: 201
42
- resource:
43
status: draft
44
subject: {id: patient1, resourceType: Patient}
45
id: enc2
46
resourceType: Encounter
47
meta:
48
lastUpdated: '2018-11-27T09:47:27.412Z'
49
versionId: '281'
50
tag:
51
- {system: 'https://aidbox.app', code: created}
52
status: 201
53
- resource:
54
status: draft
55
subject: {id: patient2, resourceType: Patient}
56
id: enc3
57
resourceType: Encounter
58
meta:
59
lastUpdated: '2018-11-27T09:47:27.412Z'
60
versionId: '281'
61
tag:
62
- {system: 'https://aidbox.app', code: created}
63
status: 201
Copied!
We created 2 patients and 3 encounters that are linked to those patients.

SQL

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:
DB Console
patients.sql
1
SELECT
2
id, resource_type, resource
3
FROM
4
patient;
Copied!
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.
patients.sql
1
SELECT
2
id, resource_type,
3
jsonb_set(
4
jsonb_set(resource, '{id}', to_jsonb(id)),
5
'{resourceType}',
6
to_jsonb(resource_type))
7
FROM
8
patient;
Copied!
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"}
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
1
SELECT
2
p.id AS patient_id,
3
e.id AS encounter_id,
4
e.resource AS encounter
5
FROM
6
patient AS p
7
JOIN encounter AS e
8
ON p.id = e.resource->'subject'->>'id';
Copied!
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.
patient-encounters.sql
1
SELECT
2
p.id AS patient_id,
3
json_agg(e.resource::jsonb) AS encounters
4
FROM
5
patient AS p
6
JOIN encounter AS e
7
ON p.id = e.resource->'subject'->>'id'
8
GROUP BY p.id;
Copied!
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.
patients-encounters-with-ids.sql
1
SELECT
2
p.id AS patient_id,
3
json_agg(e.resource::jsonb) AS encounters
4
FROM (SELECT id,
5
resource_type,
6
jsonb_set(
7
jsonb_set(resource, '{id}', to_jsonb(id)),
8
'{resourceType}',
9
to_jsonb(resource_type))
10
AS resource
11
FROM patient) AS p
12
JOIN (SELECT id,
13
resource_type,
14
jsonb_set(
15
jsonb_set(resource, '{id}', to_jsonb(id)),
16
'{resourceType}',
17
to_jsonb(resource_type))
18
AS resource
19
FROM encounter) AS e
20
ON p.id = e.resource->'subject'->>'id'
21
GROUP BY p.id;
Copied!
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.
patients-with-encounters-and-ids.sql
1
SELECT
2
p.id AS id,
3
jsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) AS resource
4
FROM (SELECT id,
5
resource_type,
6
jsonb_set(
7
jsonb_set(resource, '{id}', to_jsonb(id)),
8
'{resourceType}',
9
to_jsonb(resource_type))
10
AS resource
11
FROM patient) AS p
12
JOIN (SELECT id,
13
resource_type,
14
jsonb_set(
15
jsonb_set(resource, '{id}', to_jsonb(id)),
16
'{resourceType}',
17
to_jsonb(resource_type))
18
AS resource
19
FROM encounter) AS e
20
ON p.id = e.resource->'subject'->>'id'
21
GROUP BY p.id, p.resource
22
HAVING p.id = 'patient1';
Copied!
The result should look like the following table (but without pretty printing):
id
resource
patient1
{"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"}
Now let's make the results of this query accessible via REST API. To do that, we need to create the AidboxQuery resource:
Request
Response
1
POST /AidboxQuery
2
Accept: text/yaml
3
Content-Type: text/yaml
4
​
5
id: patient-with-encounters
6
params:
7
patient-id: {isRequired: true}
8
query: |
9
SELECT
10
jsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb) AS resource
11
FROM (SELECT id,
12
resource_type,
13
jsonb_set(
14
jsonb_set(resource, '{id}', to_jsonb(id)),
15
'{resourceType}',
16
to_jsonb(resource_type))
17
AS resource
18
FROM patient) AS p
19
JOIN (SELECT id,
20
resource_type,
21
jsonb_set(
22
jsonb_set(resource, '{id}', to_jsonb(id)),
23
'{resourceType}',
24
to_jsonb(resource_type))
25
AS resource
26
FROM encounter) AS e
27
ON p.id = e.resource->'subject'->>'id'
28
GROUP BY p.id, p.resource
29
HAVING p.id = {{params.patient-id}};
Copied!
1
# Status: 201
2
​
3
query: "SELECT\njsonb_set(p.resource, '{encounters}', json_agg(e.resource::jsonb)::jsonb)\
4
\ AS resource\nFROM (SELECT id, \n resource_type,\n jsonb_set(\n \
5
\ jsonb_set(resource, '{id}', to_jsonb(id)),\n '{resourceType}', \n \
6
\ to_jsonb(resource_type)) \n AS resource \n FROM patient) AS p\n\
7
JOIN (SELECT id, \n resource_type, \n jsonb_set(\n jsonb_set(resource,\
8
\ '{id}', to_jsonb(id)), \n '{resourceType}', \n to_jsonb(resource_type))\
9
\ \n AS resource \n FROM encounter) AS e\nON p.id = e.resource->'subject'->>'id'\n\
10
GROUP BY p.id, p.resource\nHAVING p.id = 'patient1';"
11
params:
12
patient-id: {isRequired: true}
13
id: patient-with-encounters
14
resourceType: AidboxQuery
15
meta:
16
lastUpdated: '2018-11-27T13:30:45.670Z'
17
versionId: '37'
18
tag:
19
- {system: 'https://aidbox.app', code: created}
Copied!
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!
Request
Response
1
GET /$query/patient-with-encounters?patient-id=patient1
Copied!
1
# Status: 200
2
​
3
data:
4
- resource:
5
id: patient1
6
name:
7
- given: [Max]
8
family: Turikov
9
encounters:
10
- id: enc1
11
status: draft
12
subject: {id: patient1, resourceType: Patient}
13
resourceType: Encounter
14
- id: enc2
15
status: draft
16
subject: {id: patient1, resourceType: Patient}
17
resourceType: Encounter
18
resourceType: Patient
19
# ...
Copied!
Request
Response
1
GET /$query/patient-with-encounters?patient-id=patient2
Copied!
1
# Status: 200
2
​
3
data:
4
- resource:
5
id: patient2
6
name:
7
- given: [Alex]
8
family: Antonov
9
encounters:
10
- id: enc3
11
status: draft
12
subject: {id: patient2, resourceType: Patient}
13
resourceType: Encounter
14
resourceType: Patient
15
# ...
Copied!
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.
Want to know more about Aidbox, FHIR, and custom search? Join our community chat.
Copy link