Custom Search

Last updated 2 months ago

Intro
​FHIR search has a lot of capabilities. However, some cases can't be expressed in terms of it. For example, we want to get a patient resource with additional field 'encounters' which contains all encounters of that patient but it's not possible to implement 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
POST /
​
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
  request:
    method: POST
    url: "/Encounter"
​
- resource:
    id: enc2
    status: draft
    subject:
      resourceType: Patient
      id: patient1
  request:
    method: POST
    url: "/Encounter"
​
- resource:
    id: enc3
    status: draft
    subject:
      resourceType: Patient
      id: patient2
  request:
    method: POST
    url: "/Encounter"
# Status: 200
​
id: '281'
type: transaction-response
resourceType: Bundle
entry:
- resource:
    name:
    - given: [Max]
      family: Turikov
    id: patient1
    resourceType: Patient
    meta:
      lastUpdated: '2018-11-27T09:47:27.412Z'
      versionId: '281'
      tag:
      - {system: 'https://aidbox.app', code: created}
  status: 201
- resource:
    name:
    - given: [Alex]
      family: Antonov
    id: patient2
    resourceType: Patient
    meta:
      lastUpdated: '2018-11-27T09:47:27.412Z'
      versionId: '281'
      tag:
      - {system: 'https://aidbox.app', code: created}
  status: 201
- resource:
    status: draft
    subject: {id: patient1, resourceType: Patient}
    id: enc1
    resourceType: Encounter
    meta:
      lastUpdated: '2018-11-27T09:47:27.412Z'
      versionId: '281'
      tag:
      - {system: 'https://aidbox.app', code: created}
  status: 201
- resource:
    status: draft
    subject: {id: patient1, resourceType: Patient}
    id: enc2
    resourceType: Encounter
    meta:
      lastUpdated: '2018-11-27T09:47:27.412Z'
      versionId: '281'
      tag:
      - {system: 'https://aidbox.app', code: created}
  status: 201
- resource:
    status: draft
    subject: {id: patient2, resourceType: Patient}
    id: enc3
    resourceType: Encounter
    meta:
      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.
SQL
Aidbox uses PostgreSQL (super advanced open-source DBMS) which allows to express 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
SELECT
id, resource_type, resource
FROM 
patient;
id
resource_type
resource
patient1
Patient
{"name":[{"given":["Max"],"family":"Turikov"}]}
patient2
Patient
{"name":[{"given":["Alex"],"family":"Antonov"}]}
Next, we want to add patient id and resource_type into resource.
patients.sql
SELECT
id, resource_type, 
    jsonb_set( 
      jsonb_set(resource, '{id}', to_jsonb(id)),
      '{resourceType}', 
      to_jsonb(resource_type))
FROM 
patient;
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 (#aidbox channel).
Also, we can obtain a list of encounters for each patient:
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';
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 will be obtaining aggregated data by patients.
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;
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 don't have information about encounter id's.
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;
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 patient resource but didn't use it yet. Let's put encounters to patient resource and take only one patient by specified id.
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';
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 AidboxQuery resource:
Request
Response
POST /AidboxQuery
​
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}};
# 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-encounters
resourceType: AidboxQuery
meta:
  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 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
GET /$query/patient-with-encounters?patient-id=patient1
# 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
# ...
Request
Response
GET /$query/patient-with-encounters?patient-id=patient2
# 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
# ...
Hell ye! We got all needed data in 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 (#aidbox channel).

Compartments

Custom Resources

id	resource_type	resource
`patient1`	`Patient`	`{"name":[{"given":["Max"],"family":"Turikov"}]}`
`patient2`	`Patient`	`{"name":[{"given":["Alex"],"family":"Antonov"}]}`

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"}`

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"}}`

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"}}]`

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"}]`