Custom Search

With AidboxQuery resource, you can turn your SQL query into REST Endpoint.

PUT /AidboxQuery/<query-name>
params:
# define filter parameter
filter:
# make it required
isRequired: true
# it's type is string (can be integer, number, object, boolean)
type: string
# format is java format string, which will be applied to value
# this useful to get for example ilike query expr ilike '% value%'
# do not forget to escape % with one more %
format: '%% %s%%'
# you can set default value
default: 'ups'
count:
type: integer
default: 10
# sql query with parameters {{path.to.ctx.elements}}
query: 'SELECT * from patient where id ilike = {{params.filter}} limit {{params.count}}
# if count-query is present - it will be evaluated for total property in response
count-query: 'SELECT count(*) from patient where id ilike = {{params.filter}}

Here is self-debugging AidboxQuery to start play with:

PUT /AidboxQuery/debug
params:
filter:
isRequired: true
type: string
format: '%% %s%%'
count:
type: integer
default: 10
data:
type: object
default: {resourceType: 'Nop'}
flag:
default: true
type: boolean
query: |
SELECT
{{params.filter}}::text as filter,
{{params.flag}} as flag,
{{params.data}}::jsonb as data,
{{params}}::jsonb as params,
{{params.count}} as count,
{{}} as ctx
count-query: |
SELECT {{params.count}}
GET /$query/debug?filter=ups&data=%7B%22a%22%3A%201%7D
^ url encoded {"a": 1}

Example

For example, let's create a simple aggregation report for encounters parameterised by date. Create an AidboxQuery resource:

PUT /AidboxQuery/daily-report
params:
date:
isRequired: true
query: |
SELECT
resource->>'class' as class,
count(*) as count
FROM encounter
WHERE {{params.date}}
BETWEEN (resource#>>'{period,start}')::date
AND (resource#>>'{period,end}')::date
GROUP BY resource->>'class'

Let's upload some sample data using Bulk Upsert:

PUT /
- status: draft
class: {code: IMP}
period: {start: "2013-06-08T10:57:34", end: "2013-06-08T12:00:00"}
resourceType: Encounter
id: enc-1
- status: draft
class: {code: IMP}
period: {start: "2013-06-08T11:00:05", end: "2013-06-08T11:30:00"}
resourceType: Encounter
id: enc-2
- status: draft
class: {code: AMB}
period: {start: "2013-06-08T10:21:01", end: "2013-06-08T11:42:11"}
resourceType: Encounter
id: enc-3
- status: draft
class: {code: IMP}
period: {start: "2013-06-07T09:02:01", end: "2013-06-07T15:10:09"}
resourceType: Encounter
id: enc-3

After you created AidboxQuery, you can use it:

GET /$query/daily-report?date=2013-06-08
# Status: 200
data:
- {class: '{"code": "IMP"}', count: 2}
- {class: '{"code": "AMB"}', count: 1}
query: [...]

PostgreSQL supports Special Date/Time inputs like now, today, tomorrow etc.

Debug AidboxQuery

You can debug AidboxQuery with _explain=true parameter:

GET /$query/daily-report?date=2013-06-08&_explain=true
# Status: 200
plan: |-
HashAggregate (cost=27.27..27.97 rows=56 width=40) (actual time=0.443..0.459 rows=1 loops=1)
Group Key: (resource ->> 'class'::text)
-> Seq Scan on encounter (cost=0.00..26.96 rows=62 width=32) (actual time=0.398..0.420 rows=2 loops=1)
Filter: (('2013-06-08'::date >= ((resource #>> '{period,start}'::text[]))::date) AND ('2013-06-08'::date <= ((resource #>> '{period,end}'::text[]))::date))
Rows Removed by Filter: 1
Planning Time: 3.222 ms
Execution Time: 0.600 ms
ctx:
params: {date: '2013-06-08', _explain: 'true'}
resourceType: null
safe-paths:
- [resourceType]
query:
- "SELECT \n resource->>'class' as class, \n count(*) as count\nFROM encounter \nWHERE ?\nBETWEEN (resource#>>'{period,start}')::date \nAND (resource#>>'{period,end}')::date\nGROUP BY resource->>'class'"
- '2013-06-08'

Parameters in Query

Query can be parameterised by special template language {{path.to.parameter}}

All parameters passed in query string will be available under {{params.PARAMETER-NAME}}

Also, {{user.id}} will be available, for example user-info custom query can be implemented like this:

request
POST /AidboxQuery
query: 'select * from public.User where id = {{user.id}}'
id: user-info
resourceType: AidboxQuery

Sample query will be:

Request
Response
GET /$query/user-info HTTP/1.1
Host: <YOUR-BOX>.aidbox.app
Authorization: Bearer <YOUR-ACCESS-TOKEN>
Accept: text/yaml
# Status: 200
data:
- id: testuser
txid: 198
ts: '2018-10-16T13:30:03.036Z'
resource_type: User
status: updated
resource: {email: testmail@mail.com, password: $s0$f0801$72nz8sgiT91maOn8zzOppA==$PtBarKD+2TafNX+k7sBeejnvfl+N5o2VhAGA7y+JIRA=}
query: ['select * from public.User where id = ?', testuser]

It's not possible to call such AidboxQuery from REST Console, because in REST console there are no user claims. It can be done only by request with access token provided. Check OAuth2.0 doc for additional information.

_query

There is another option for calling AidboxQuery:

GET /Patient?_query=get-by-id&rid=patient1
#or
GET /fhir/Patient?_query=get-by-id&rid=patient1

Result will be represented as Search Bundle. If you call it from fhir/ base-url - resulting resources will be transformed to FHIR compliant representation.

Main difference is that such query can use additional variable available in context of {{resourceType}}.

POST /AidboxQuery
resourceType: AidboxQuery
query: 'select * from {{resourceType}} where id = {{params.rid}}'
params:
rid: {isRequired: true}
id: get-by-id
# resp
query: select * from {{resourceType}} where id = {{params.rid}}
params:
rid: {isRequired: true}
id: get-by-id
resourceType: AidboxQuery
meta:
lastUpdated: '2018-11-28T15:33:03.073Z'
versionId: '11'
tag:
- {system: 'https://aidbox.app', code: created}

Example usage:

GET /Attribute?_query=get-by-id&rid=Encounter.status
# resp
data:
- id: Encounter.status
txid: 0
ts: '2018-11-07T10:10:41.051Z'
resource_type: Attribute
status: updated
resource:
resource: {id: Encounter, resourceType: Entity}
valueSet: {id: encounter-status, resourceType: ValueSet}
path: [status]
module: fhir-3.0.1
order: 10
source: code
type: {id: code, resourceType: Entity}
isSummary: true
resourceType: Attribute
description: planned | arrived | triaged | in-progress | onleave | finished | cancelled +
isModifier: true
isRequired: true
query: ['select * from Attribute where id = ?', Encounter.status]
Request
GET /Attribute?_query=get-by-id&rid=Encounter.status

Pay attention that only{{resourceType}}can be used in place of table name, because this variable is secure and will be inserted directly into query. Other variables will be escaped and can't be used in such parts of query.

See also our tutorial: