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 using Batch/Transaction. Copy the following snippet to the Aidbox.Cloud REST Console.

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"

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

We created 2 patients and 3 encounters that are linked to those patients.

SQL API

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:

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 the 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.

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 is 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 there's no information about the encounter id.

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 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

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 the AidboxQuery resource:

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}};

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'

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

GET /$query/patient-with-encounters?patient-id=patient1

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
# ...

Request

GET /$query/patient-with-encounters?patient-id=patient2

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
# ...

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.