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.

Uploading Sample Data

Working with Aidbox from .NET

Last modified 9mo ago

Copy link

Contents

Intro

Prepare Data

SQL