| description |
|---|
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 |
{% hint style="info" %}
If you want to use arbitrary SQL (e.g. LEFT JOIN), consider AidboxQuery.
{% endhint %}
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.
{% tabs %} {% tab title="Request (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"{% endtab %}
{% tab title="Response (Aidbox format)" %}
# 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'{% endtab %} {% endtabs %}
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:
{% tabs %} {% tab title="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}}{% endtab %} {% endtabs %}
Now we can call this query with /alpha/<resourceType>?query=<query-name>¶ms....:
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
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:
{% tabs %} {% tab title="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%'{% endtab %} {% endtabs %}
Your parameters and basic query can use join attribute to join related resources for search:
{% tabs %} {% tab title="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}}{% endtab %} {% endtabs %}
{% tabs %} {% tab title="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%'{% endtab %} {% endtabs %}
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
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
Example: use this search query
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
You can predefine included resources for SearchQuery with includes property:
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
Use the created query:
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
To include resources that refer resources from your query, you can add reverse: true attribute:
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
Execute the created query
{% tabs %} {% tab title="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{% endtab %} {% endtabs %}
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:
{% hint style="info" %} The following example is prepared to be executed in the DB Console {% endhint %}
{% tabs %} {% tab title="Aidbox DB Console Request" %}
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"]]'
){% endtab %}
{% tab title="Aidbox DB Console Response" %}
knife_extract
- '{"id": "patient2", "resourceType": "Patient"}'
{% endtab %} {% endtabs %}
Include query can be parametrised if you define include inside params. You can use where key to add additional filter on included resources.
{% tabs %} {% tab title="Aidbox format" %}
PUT /SearchQuery/cond-incl
resource: {id: 'Patient', resourceType: 'Entity'}
as: pt
query:
order-by: pt.id desc
params:
obs-cat:
type: string
includes:
obs:
reverse: true
path: ["patient"]
resource: {id: 'Observation', resourceType: 'Entity'}
where: "resource#>>'{category,0,coding,0,code}' = {{params.category}}"
---
GET /alpha/Patient?query=cond-incl&category=labs
# will add filtered include
GET /alpha/Patient?query=cond-incl
# will skip include
{% endtab %} {% endtabs %}
If you want to provide default include, define include with the same key on query level and in parameter. Parameter include will override the default in case parameter is provided in the request.
{% tabs %} {% tab title="Aidbox format" %}
PUT /SearchQuery/cond-incl
resource: {id: 'Patient', resourceType: 'Entity'}
as: pt
query:
order-by: pt.id desc
includes:
# default include with filter
obs:
reverse: true
path: ["patient"]
resource: {id: 'Observation', resourceType: 'Entity'}
where: "resource#>>'{category,0,coding,0,code}' = 'default"
params:
obs-cat:
type: string
# override default include
includes:
obs:
where: "resource#>>'{category,0,coding,0,code}' = {{params.category}}"
{% endtab %} {% endtabs %}
With the parameter _explain=analyze , you can inspect the execution plan of a search query:
{% tabs %} {% tab title="Aidbox fromat" %}
GET /alpha/Encounter?query=q-2&pt=joh&_explain=analyze
# 200
query: |-
EXPLAIN ANALYZE 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
LIMIT 100"
params: ['% joh%']
explain: |-
Limit (cost=1382.90..1382.97 rows=28 width=882) (actual time=4.274..4.274 rows=0 loops=1)
-> Sort (cost=1382.90..1382.97 rows=28 width=882) (actual time=4.272..4.272 rows=0 loops=1)
Sort Key: pt.id DESC
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=951.07..1382.23 rows=28 width=882) (actual time=4.247..4.248 rows=0 loops=1)
Hash Cond: ((enc.resource #>> '{subject,id}'::text[]) = pt.id)
-> Seq Scan on encounter enc (cost=0.00..421.60 rows=3460 width=839) (actual time=0.779..1.544 rows=3460 loops=1)
-> Hash (cost=950.95..950.95 rows=10 width=38) (actual time=1.375..1.375 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on patient pt (cost=0.00..950.95 rows=10 width=38) (actual time=1.370..1.371 rows=1 loops=1)
Filter: (immutable_wrap_ws(immutable_unaccent(immutable_array_to_string(knife_extract_text(resource, '[["name", "family"]]'::jsonb), ' '::text))) ~~* '% joh%'::text)
Rows Removed by Filter: 1
Planning Time: 9.345 ms
Execution Time: 4.564 ms
total-query: "EXPLAIN ANALYZE SELECT count(*)\nFROM \"encounter\" enc\nJOIN \"patient\" pt\n ON enc.resource#>>'{subject,id}' = pt.id\nWHERE /* pt */ aidbox_text_search(knife_extract_text(pt.resource, $$[[\"name\",\"family\"]]$$)) \nilike ?"
total-explain: |-
Aggregate (cost=1382.30..1382.31 rows=1 width=8) (actual time=3.257..3.257 rows=1 loops=1)
-> Hash Join (cost=951.07..1382.23 rows=28 width=0) (actual time=3.254..3.254 rows=0 loops=1)
Hash Cond: ((enc.resource #>> '{subject,id}'::text[]) = pt.id)
-> Seq Scan on encounter enc (cost=0.00..421.60 rows=3460 width=772) (actual time=0.286..0.910 rows=3460 loops=1)
-> Hash (cost=950.95..950.95 rows=10 width=5) (actual time=1.198..1.199 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on patient pt (cost=0.00..950.95 rows=10 width=5) (actual time=1.195..1.195 rows=1 loops=1)
Filter: (immutable_wrap_ws(immutable_unaccent(immutable_array_to_string(knife_extract_text(resource, '[["name", "family"]]'::jsonb), ' '::text))) ~~* '% joh%'::text)
Rows Removed by Filter: 1
Planning Time: 6.716 ms
Execution Time: 3.543 ms{% endtab %} {% endtabs %}
You can debug SearchQuery with multiple parameters combinations without saving resource by POST /SearchQuery/$debug. You can simulate requests with different parameters by tests attribute. Aidbox will return results and explanation for each test:
{% tabs %} {% tab title="Aidbox format" %}
POST /SearchQuery/$debug
# explain all queries
explain: true
# timeout for query in ms
timeout: 2000
# test with requests
tests:
# name of request
only-pid:
# params for request
params: {pid: 'patient1'}
only-ts:
params: {ts: '2019-01-01'}
both:
params: {pid: 'patient1', ts: 'ups'}
# SearchQuery defnition
query:
resource: {id: Patient, resourceType: Entity}
as: pt
params:
pid: {type: string, isRequired: true, where: 'pt.id = {{params.pid}}'}
ts: {type: date, where: 'pt.tis >= {{params.date}}'}
query: {order-by: pt.ts desc}
limit: 40
# 200
only-pid:
params:
pid: patient1
_timeout: 2000
result:
resourceType: Bundle
type: searchset
entry:
- 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'
query-timeout: 2000000
explain:
query: |-
EXPLAIN ANALYZE SELECT * FROM "patient" pt
WHERE /* pid */ pt.id = ?
ORDER BY pt.ts desc
LIMIT 40
params:
- patient1
explain: >-
Limit (cost=8.18..8.18 rows=1 width=124) (actual time=0.089..0.236 rows=1
loops=1)
-> Sort (cost=8.18..8.18 rows=1 width=124) (actual time=0.074..0.101
rows=1 loops=1)
Sort Key: ts DESC
Sort Method: quicksort Memory: 25kB
-> Index Scan using patient_pkey on patient pt (cost=0.15..8.17
rows=1 width=124) (actual time=0.037..0.053 rows=1 loops=1)
Index Cond: (id = 'patient1'::text)
Planning Time: 0.185 ms
Execution Time: 0.302 ms
only-ts:
status: error
params:
ts: '2019-01-01'
_timeout: 2000
errors:
- details: Parameter pid is required
both:
params:
pid: patient1
ts: ups
_timeout: 2000
result:
status: error
query:
- |-
SELECT pt.*
FROM "patient" pt
WHERE /* pid */ pt.id = ?
AND /* ts */ pt.tis >= ?
ORDER BY pt.ts desc
LIMIT 40
- patient1
- null
error: |-
ERROR: column pt.tis does not exist
Hint: Perhaps you meant to reference the column "pt.ts".
Position: 73{% endtab %} {% endtabs %}