SearchQuery
Managed SQL for Search API
With SearchQuery resource, you can define "managed" SQL for Search API with parameters, paging, sorting, and includes.
Parameter name | Description |
_count | A number of records returned per page |
_page | Controls pagination |
_total | The maximum number of results returned by a search result |
_timeout | Defines query timeout |
join | Allows you to join related resources for search |
order-by | Defines the ordering of the search results |
includes | Allows you to predefine included resources |
reverse | Includes resources that refer resources from your query |
_explain=analyze | Helps to inspect the execution plan of a search query |
We need some sample data to see the results of example queries. Let's create it.
Copy the following snippet to the Aidbox
REST Console.Request (Aidbox format)
Response (Aidbox format)
POST /
​
type: transaction
entry:
- resource:
id: pr-1
name:
- given: [Ted]
family: 'Scott'
request:
method: POST
url: "/Practitioner"
​
- resource:
id: pr-2
name:
- given: [Tommy]
family: 'Peterson'
request:
method: POST
url: "/Practitioner"
​
- resource:
id: org1
name: 'Test hospital1'
request:
method: POST
url: "/Organization"
​
- resource:
id: org2
name: 'Test hospital2'
request:
method: POST
url: "/Organization"
​
- resource:
id: patient1
name:
- given: [Max]
family: Johnson
gender: male
managingOrganization: {resourceType: Organization, id: org1, display: 'Test hospital1'}
birthDate: '1960-10-10'
request:
method: POST
url: "/Patient"
​
- resource:
id: patient2
name:
- given: [Alex]
family: Smith
gender: male
managingOrganization: {resourceType: Organization, id: org2, display: 'Test hospital2'}
birthDate: '1990-01-01'
request:
method: POST
url: "/Patient"
​
- resource:
id: enc1
status: planned
subject:
resourceType: Patient
id: patient1
class:
code: abc
request:
method: POST
url: "/Encounter"
​
- resource:
id: enc2
status: finished
subject:
resourceType: Patient
id: patient1
class:
code: abc
request:
method: POST
url: "/Encounter"
​
- resource:
id: enc3
status: planned
subject:
resourceType: Patient
id: patient2
class:
code: abc
request:
method: POST
url: "/Encounter"
​
- resource:
id: apt1
description: "Test appointment 1"
start: '2020-12-10T09:00:00Z'
end: '2020-12-10T11:00:00Z'
status: booked
participant: [{ actor: { resourceType: Patient, id: patient1}, status: accepted},{ actor: { resourceType: Practitioner, id: pr-1}, status: accepted}]
request:
method: POST
url: "/Appointment"
​
- resource:
id: apt2
description: "Test appointment 2"
start: '2021-04-10T09:00:00Z'
end: '2021-04-10T11:00:00Z'
status: booked
participant: [{ actor: { resourceType: Patient, id: patient2}, status: accepted}, { actor: { resourceType: Practitioner, id: pr-2}, status: accepted}]
request:
method: POST
url: "/Appointment"
# Status: 200
resourceType: Bundle
type: transaction-response
id: '244'
entry:
- resource:
name:
- given:
- Ted
family: Scott
id: pr-1
resourceType: Practitioner
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Practitioner/pr-1/_history/244
x-duration: 54
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
name:
- given:
- Tommy
family: Peterson
id: pr-2
resourceType: Practitioner
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Practitioner/pr-2/_history/244
x-duration: 14
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
name: Test hospital1
id: org1
resourceType: Organization
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Organization/org1/_history/244
x-duration: 16
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
name: Test hospital2
id: org2
resourceType: Organization
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Organization/org2/_history/244
x-duration: 11
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
name:
- given:
- Max
family: Johnson
gender: male
birthDate: '1960-10-10'
managingOrganization:
id: org1
display: Test hospital1
resourceType: Organization
id: patient1
resourceType: Patient
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Patient/patient1/_history/244
x-duration: 24
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
name:
- given:
- Alex
family: Smith
gender: male
birthDate: '1990-01-01'
managingOrganization:
id: org2
display: Test hospital2
resourceType: Organization
id: patient2
resourceType: Patient
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Patient/patient2/_history/244
x-duration: 11
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
status: planned
subject:
id: patient1
resourceType: Patient
id: enc1
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Encounter/enc1/_history/244
x-duration: 14
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
status: finished
subject:
id: patient1
resourceType: Patient
id: enc2
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Encounter/enc2/_history/244
x-duration: 9
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
status: planned
subject:
id: patient2
resourceType: Patient
id: enc3
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Encounter/enc3/_history/244
x-duration: 10
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
end: '2020-12-10T11:00:00.000Z'
start: '2020-12-10T09:00:00.000Z'
status: booked
description: Test appointment 1
participant:
- actor:
id: patient1
resourceType: Patient
status: accepted
- actor:
id: pr-1
resourceType: Practitioner
status: accepted
id: apt1
resourceType: Appointment
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Appointment/apt1/_history/244
x-duration: 20
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
- resource:
end: '2021-04-10T11:00:00.000Z'
start: '2021-04-10T09:00:00.000Z'
status: booked
description: Test appointment 2
participant:
- actor:
id: patient2
resourceType: Patient
status: accepted
- actor:
id: pr-2
resourceType: Practitioner
status: accepted
id: apt2
resourceType: Appointment
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
response:
etag: '244'
cache-control: no-cache
last-modified: 'Mon, 19 Apr 2021 12:18:14 GMT'
location: /Appointment/apt2/_history/244
x-duration: 21
x-request-id: c9481d21-a93e-4bbd-940e-d7221ad45110
status: '201'
We created 2 patients, 2 practitioners, 3 encounters, 2 appointments, 2 Managing organizations that are linked to each other.
Let's define the search query to search old patients by the partial match of the family name with the filtering by gender:
Aidbox format
PUT /SearchQuery/q-1
​
# attach this query to Patient resource type
resource: {id: 'Patient', resourceType: 'Entity'}
# give alias to patient table
as: pt
# enable total query
total: true
# basic query
query:
where: "(pt.resource->>'birthDate')::date < '1980-01-01'"
order-by: pt.id desc
params:
gender:
type: string
where: "pt.resource->>'gender' = {{params.gender}}"
family:
type: string
format: '% ?%'
where: |
aidbox_text_search(knife_extract_text(pt.resource, $$[["name","family"]]$$))
ilike {{params.family}}
Now we can call this query with
/alpha/<resourceType>?query=<query-name>¶ms....:Aidbox format
GET /alpha/Patient?query=q-1&_page=1&_count=3&_total=none
​
# 200
resourceType: Bundle
type: searchset
entry: [...]
query-sql: |
SELECT *
FROM "patient" pt
WHERE /* query */ (pt.resource->>'birthDate')::date < '1980-01-01'
ORDER BY pt.id desc
LIMIT 100
query-timeout: 60000
You can use count and page parameters for paging and control total query (if enabled) with total parameter. Use _timeout parameter to set query timeout.
If the parameter is provided, another query will be generated on the fly:
Aidbox format
GET /alpha/Patient?query=q-1&family=joh
​
# 200
​
resourceType: Bundle
type: searchset
entry: [...]
query-sql:
- |
SELECT *
FROM \"patient\" pt
WHERE /* query */ (pt.resource->>'birthDate')::date < '1980-01-01'
AND /* family */ aidbox_text_search(knife_extract_text(pt.resource, $$[[\"name\",\"family\"]]$$))
ilike ?\nORDER BY pt.id desc
LIMIT 100"
- '% joh%'
Your parameters and basic query can use join attribute to join related resources for search:
Aidbox format
PUT /SearchQuery/q-2
​
resource: {id: 'Encounter', resourceType: 'Entity'}
as: enc
query:
order-by: pt.id desc
params:
pt:
type: string
format: '% ?%'
join:
pt:
table: patient
by: "enc.resource#>>'{subject,id}' = pt.id"
where: |
aidbox_text_search(knife_extract_text(pt.resource, $$[["name","family"]]$$))
ilike {{params.pt}}
Aidbox format
GET /alpha/Encounter?query=q-2&pt=joh
​
# 200
resourceType: Bundle
type: searchset
entry: [...]
query-sql:
- |
SELECT *
FROM \"encounter\" enc
JOIN \"patient\" pt
ON enc.resource#>>'{subject,id}' = pt.id
WHERE /* pt */ aidbox_text_search(knife_extract_text(pt.resource, $$[[\"name\",\"family\"]]$$))
ilike ?
ORDER BY pt.id desc\nLIMIT 100"
- '% joh%'
Both
query and params support order-by. order-by in query has the least precedence. order-by in params are added in top-down order. e.g. order-by in first search parameter has the most precedence.Example: create search query
Aidbox format
PUT /SearchQuery/sq
​
as: ap
query:
order-by: "ap.resource->>'start' ASC"
resource:
id: 'Appointment'
resourceType: 'Entity'
params:
ord-dir:
type: string
format: '?'
order-by: |
CASE WHEN {{params.ord-dir}} = 'asc' THEN ap.resource->>'start' END ASC,
CASE WHEN {{params.ord-dir}} = 'desc' THEN ap.resource->>'start' END DESC
Example: use this search query
Aidbox format
GET /alpha/Appointment?query=sq&ord-dir=desc
​
#200
​
resourceType: Bundle
type: searchset
entry:
- resource:
start: '2021-04-02T16:02:50.996+03:00'
# omitted
- resource:
start: '2021-02-02T16:02:50.997+03:00'
# omitted
- resource:
start: '2020-02-02T16:02:50.997+03:00'
# omitted
# omitted
You can predefine included resources for SearchQuery with includes property:
Aidbox format
PUT /SearchQuery/inc
​
resourceType: SearchQuery
resource: {id: Encounter, resourceType: Entity}
as: enc
total: true
includes:
# name for include
subject:
# path to reference
path: [subject]
# ref to resource
resource: {id: Patient, resourceType: Entity}
# nested includes
includes:
organization:
path: [managingOrganization]
resource: {id: Organization, resourceType: Entity}
query: {order-by: enc.id}
limit: 40
Use the created query:
Aidbox format
GET /alpha/Encounter?query=inc
​
#200
​
resourceType: Bundle
type: searchset
entry:
- resource:
status: planned
subject:
id: patient1
resourceType: Patient
id: enc1
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
status: finished
subject:
id: patient1
resourceType: Patient
id: enc2
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
status: planned
subject:
id: patient2
resourceType: Patient
id: enc3
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
name:
- given:
- Max
family: Johnson
gender: male
birthDate: '1960-10-10'
managingOrganization:
id: org1
display: Test hospital1
resourceType: Organization
id: patient1
resourceType: Patient
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
name:
- given:
- Alex
family: Smith
gender: male
birthDate: '1990-01-01'
managingOrganization:
id: org2
display: Test hospital2
resourceType: Organization
id: patient2
resourceType: Patient
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
name: Test hospital1
id: org1
resourceType: Organization
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
name: Test hospital2
id: org2
resourceType: Organization
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
query-sql:
- |-
SELECT enc.*
FROM "encounter" enc
ORDER BY enc.id
LIMIT 40
query-timeout: 60000
total: 3
total-query:
- |-
SELECT count(*)
FROM "encounter" enc
To include resources that refer resources from your query, you can add reverse: true attribute:
Aidbox format
PUT /SearchQuery/revinc
​
resourceType: SearchQuery
resource: {id: Patient, resourceType: Entity}
as: pt
total: true
includes:
encounters:
# means that reference going from Encounter to patient
reverse: true
path: [subject]
resource: {id: Encounter, resourceType: Entity}
where: "resource->>'status' = 'finished'"
limit: 40
Execute the created query
Aidbox format
GET /alpha/Encounter?query=revinc
​
#200
​
resourceType: Bundle
type: searchset
entry:
- resource:
name:
- text: Alex
gender: male
address:
- city: New-York
telecom:
- value: fhir
birthDate: '1988-04-16'
id: b0cab43b-ba3e-4192-9ee6-851fb15ebc5f
resourceType: Patient
meta:
lastUpdated: '2021-04-16T14:01:51.973363Z'
createdAt: '2021-04-16T11:43:36.524830Z'
versionId: '143'
- resource:
name:
- given:
- Max
- family: Smith
gender: male
address:
- city: Hello
line:
- 123 Oxygen St
state: NY
district: World
postalCode: '3212'
telecom:
- use: home
- use: work
rank: 1
value: (32) 8934 1234
system: phone
birthDate: '1960-10-10'
id: 6e690b70-c55d-4efc-89d4-38257d37a774
resourceType: Patient
meta:
lastUpdated: '2021-04-19T09:35:48.183189Z'
createdAt: '2021-04-19T09:35:48.183189Z'
versionId: '163'
- resource:
name:
- given:
- Max
family: Johnson
gender: male
birthDate: '1960-10-10'
managingOrganization:
id: org1
display: Test hospital1
resourceType: Organization
id: patient1
resourceType: Patient
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
name:
- given:
- Alex
family: Smith
gender: male
birthDate: '1990-01-01'
managingOrganization:
id: org2
display: Test hospital2
resourceType: Organization
id: patient2
resourceType: Patient
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
- resource:
status: finished
subject:
id: patient1
resourceType: Patient
id: enc2
resourceType: Encounter
meta:
lastUpdated: '2021-04-19T12:18:14.183626Z'
createdAt: '2021-04-19T12:18:14.183626Z'
versionId: '244'
query-sql:
- |-
SELECT pt.*
FROM "patient" pt
LIMIT 40
query-timeout: 60000
total: 4
total-query:
- |-
SELECT count(*)
FROM "patient" pt
Path expression in includes is
json_knife extension path, it consists of strings, integers, and objects. If the item is path string, it means get key in object (arrays are implicitly flattened). If key is integer, it is interpreted as index in array. If key is object, it is pattern to filter values in array with inclusion semantic (like PostgreSQL JSONB operator @>). Here is an example of how to extract a patient (code: PART) from the appointment:
The following example is prepared to be executed in the DB Console
Aidbox DB Console Request
Aidbox DB Console Response
select knife_extract(
'{
"resourceType" : "Appointment",
"status" : "active",
"participant" : [ {
"type" : [ {
"text" : "Patient",
"coding" : [ {
"code" : "PART"
} ]
} ],
"actor" : {
"id" : "patient2",
"resourceType" : "Patient"
},
"status" : "active"
}, {
"type" : [ {
"text" : "Admit",
"coding" : [ {
"code" : "ADM"
} ]
} ],
"actor" : {
"id" : "pr-2",
"resourceType" : "Practitioner"
},
"status" : "active"
} ]
}',
'[["participant", {"type": [{"coding": [{"code": "PART"}]}]}, "actor"]]'
)
knife_extract
- '{"id": "patient2", "resourceType": "Patient"}'