Understand how to create an Aidbox Search resource and use it
Before you begin
See
Set up the local Aidbox instance using the getting started
The problem
Let's say we want to search by email (in Patient.telecom field), domain case-insensitively. FHIR Search API can't handle it because the domain is part of the email value (), and FHIR does not offer a way to extract it.
The solution
The solution is to use SQL to extract it. We use the Search resource, an Aidbox-specific resource, to create a search parameter using SQL.
Next, we can play with SQL (use ChatGPT) to get the desired behavior. Let's use split_part and lower SQL functions to search by domain:
SELECT
id,
resource,
EXISTS (
SELECT 1 FROM jsonb_array_elements(p.resource -> 'telecom') AS email
WHERE email ->> 'system' = 'email'
AND split_part(lower(email ->> 'value'), '@', 2) = lower('somecompany.com')
)
FROM patient p
In DB Console, we get results:
Then we can rewrite it like this:
SELECT *
FROM patient p
WHERE
EXISTS (
SELECT 1 FROM jsonb_array_elements(p.resource -> 'telecom') AS email
WHERE email ->> 'system' = 'email'
AND split_part(lower(email ->> 'value'), '@', 2) = lower('somecompany.com'))
And create Search resource using Search.where expression (note how we use {{table}} and {{param}} placeholders):
PUT /Search/Patient.email-domain
content-type: text/yaml
accept: text/yaml
resourceType: Search
id: Patient.email-domain
name: email-domain
resource:
id: Patient
resourceType: Entity
where: EXISTS ( SELECT 1 FROM jsonb_array_elements({{table}}.resource -> 'telecom') AS email WHERE email ->> 'system' = 'email' AND split_part(lower(email ->> 'value'), '@', 2) = lower({{param}}::text))
Then check search:
GET /fhir/Patient?email-domain=somecompany.com
This returned both patients. See the SQL:
GET /fhir/Patient?email-domain=somecompany.com&_explain=1
query:
- >-
SELECT "patient".* FROM "patient" WHERE (EXISTS ( SELECT 1 FROM
jsonb_array_elements("patient".resource -> 'telecom') AS email WHERE email
->> 'system' = 'email' AND split_part(lower(email ->> 'value'), '@', 2) =
lower(?::text))) LIMIT ? OFFSET ?
- somecompany.com
- 100
- 0
That's it
We've created a Search resource that solves the problem that FHIR Search cannot solve.
See more:
Other examples
Encounter.subject performance optimization
PUT /Search/Encounter.subject
content-type: text/yaml
accept: text/yaml
resourceType: Search
id: Encounter.subject
name: subject
resource:
id: Encounter
resourceType: Entity
where: "resource #>> '{ subject, id }' = ANY ({{param}}::text[])"
multi: array
The good thing about Search resource is that it can be used just like SearchParameter (except, we cannot use modifiers) and can be combined with actual SearchParameters, e.g. GET /fhir/Patient?email-domain=somecompany.com&name=john. The downside is that, using Search resource, we only change WHERE and ORDER BY expressions, not the whole SQL. Sometimes it is inconvenient, and you should use instead.
