AidboxQuery
Turn SQL into REST endpoint
With the
AidboxQuery resource, you can turn your SQL query into REST Endpoint.1
PUT /AidboxQuery/<query-name>
2
​
3
params:
4
# define filter parameter
5
filter:
6
# make it required
7
isRequired: true
8
# it's type is string (can be integer, number, object, boolean)
9
type: string
10
# format is java format string, which will be applied to value
11
# this useful to get for example ilike query expr ilike '% value%'
12
# do not forget to escape % with one more %
13
format: '%% %s%%'
14
# you can set default value
15
default: 'ups'
16
count:
17
type: integer
18
default: 10
19
# sql query with parameters {{path.to.ctx.elements}}
20
query: 'SELECT * from patient where id ilike = {{params.filter}} limit {{params.count}}
21
# if count-query is present - it will be evaluated for total property in response
22
count-query: 'SELECT count(*) from patient where id ilike = {{params.filter}}
Copied!
Here is a self-debugging AidboxQuery to start with:
1
PUT /AidboxQuery/debug
2
​
3
params:
4
filter:
5
isRequired: true
6
type: string
7
format: '%% %s%%'
8
count:
9
type: integer
10
default: 10
11
data:
12
type: object
13
default: {resourceType: 'Nop'}
14
flag:
15
default: true
16
type: boolean
17
query: |
18
SELECT
19
{{params.filter}}::text as filter,
20
{{params.flag}} as flag,
21
{{params.data}}::jsonb as data,
22
{{params}}::jsonb as params,
23
{{params.count}} as count,
24
{{}} as ctx
25
count-query: |
26
SELECT {{params.count}}
27
​
28
GET /$query/debug?filter=ups&data=%7B%22a%22%3A%201%7D
29
^ url encoded {"a": 1}
Copied!
Example
For example, let's create a simple aggregation report for encounters parameterised by date. Create an
AidboxQuery resource:1
PUT /AidboxQuery/daily-report
2
​
3
params:
4
date:
5
isRequired: true
6
query: |
7
SELECT
8
resource->>'class' as class,
9
count(*) as count
10
FROM encounter
11
WHERE {{params.date}}
12
BETWEEN (resource#>>'{period,start}')::date
13
AND (resource#>>'{period,end}')::date
14
GROUP BY resource->>'class'
Copied!
1
PUT /
2
​
3
- status: draft
4
class: {code: IMP}
5
period: {start: "2013-06-08T10:57:34", end: "2013-06-08T12:00:00"}
6
resourceType: Encounter
7
id: enc-1
8
​
9
- status: draft
10
class: {code: IMP}
11
period: {start: "2013-06-08T11:00:05", end: "2013-06-08T11:30:00"}
12
resourceType: Encounter
13
id: enc-2
14
​
15
- status: draft
16
class: {code: AMB}
17
period: {start: "2013-06-08T10:21:01", end: "2013-06-08T11:42:11"}
18
resourceType: Encounter
19
id: enc-3
20
​
21
- status: draft
22
class: {code: IMP}
23
period: {start: "2013-06-07T09:02:01", end: "2013-06-07T15:10:09"}
24
resourceType: Encounter
25
id: enc-3
Copied!
After you created AidboxQuery, you can use it:
1
GET /$query/daily-report?date=2013-06-08
2
​
3
# Status: 200
4
​
5
data:
6
- {class: '{"code": "IMP"}', count: 2}
7
- {class: '{"code": "AMB"}', count: 1}
8
query: [...]
Copied!
PostgreSQL supports Special Date/Time inputs like now, today, tomorrow etc.
Design AidboxQuery
To design the aidbox query, you can use
POST /$query/$debug endpoint without the need to create an AidboxQuery resource:1
POST /$query/$debug
2
​
3
query:
4
# AidboxQuery resource content
5
query: 'SELECT {{params.id}} as params_id'
6
params:
7
id: {isRequired: true}
8
# test params
9
params:
10
id: 'ups'
11
12
---
13
# actual result
14
data:
15
- {params_id: ups}
16
# sql query
17
query: ['SELECT ? as params_id', ups]
18
# execution plan
19
plan: |-
20
Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.009..0.022 rows=1 loops=1)
21
Planning Time: 0.025 ms
22
Execution Time: 0.067 ms
23
# templating context
24
ctx:
25
remote-addr: 0:0:0:0:0:0:0:1
26
client: { ... }
27
params: {id: ups}
28
headers: {...}
29
uri: /$query/$debug
30
user: {...}
31
scheme: http
32
request-method: post
Copied!
Debug AidboxQuery
You can debug AidboxQuery with
_explain=true parameter:1
GET /$query/daily-report?date=2013-06-08&_explain=true
2
​
3
# Status: 200
4
​
5
plan: |-
6
HashAggregate (cost=27.27..27.97 rows=56 width=40) (actual time=0.443..0.459 rows=1 loops=1)
7
Group Key: (resource ->> 'class'::text)
8
-> Seq Scan on encounter (cost=0.00..26.96 rows=62 width=32) (actual time=0.398..0.420 rows=2 loops=1)
9
Filter: (('2013-06-08'::date >= ((resource #>> '{period,start}'::text[]))::date) AND ('2013-06-08'::date <= ((resource #>> '{period,end}'::text[]))::date))
10
Rows Removed by Filter: 1
11
Planning Time: 3.222 ms
12
Execution Time: 0.600 ms
13
ctx:
14
params: {date: '2013-06-08', _explain: 'true'}
15
resourceType: null
16
safe-paths:
17
- [resourceType]
18
query:
19
- "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'"
20
- '2013-06-08'
Copied!
Parameters in Query
Query can be parameterised by the 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
1
POST /AidboxQuery
2
​
3
query: 'select * from public.User where id = {{user.id}}'
4
id: user-info
5
resourceType: AidboxQuery
Copied!
Sample query will be:
Request
Response
1
GET /$query/user-info HTTP/1.1
2
Host: <YOUR-BOX>.aidbox.app
3
Authorization: Bearer <YOUR-ACCESS-TOKEN>
4
Accept: text/yaml
Copied!
1
# Status: 200
2
​
3
data:
4
- id: testuser
5
txid: 198
6
ts: '2018-10-16T13:30:03.036Z'
7
resource_type: User
8
status: updated
9
resource: {email: [email protected], password: $s0$f0801$72nz8sgiT91maOn8zzOppA==$PtBarKD+2TafNX+k7sBeejnvfl+N5o2VhAGA7y+JIRA=}
10
query: ['select * from public.User where id = ?', testuser]
11
​
Copied!
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 the access token provided. Check OAuth2.0 doc for additional information.
_query
There is another option for calling
AidboxQuery:1
GET /Patient?_query=get-by-id&rid=patient1
2
​
3
#or
4
​
5
GET /fhir/Patient?_query=get-by-id&rid=patient1
Copied!
The result will be represented as the Search Bundle. If you call it from
fhir/ base-url, resulting resources will be transformed to the FHIR compliant representation.​
The main difference is that such a query can use an additional variable available in context of
{{resourceType}}.1
POST /AidboxQuery
2
​
3
resourceType: AidboxQuery
4
query: 'select * from {{resourceType}} where id = {{params.rid}}'
5
params:
6
rid: {isRequired: true}
7
id: get-by-id
8
​
9
# resp
10
​
11
query: select * from {{resourceType}} where id = {{params.rid}}
12
params:
13
rid: {isRequired: true}
14
id: get-by-id
15
resourceType: AidboxQuery
16
meta:
17
lastUpdated: '2018-11-28T15:33:03.073Z'
18
versionId: '11'
19
tag:
20
- {system: 'https://aidbox.app', code: created}
Copied!
Example usage:
1
GET /Attribute?_query=get-by-id&rid=Encounter.status
2
​
3
# resp
4
​
5
data:
6
- id: Encounter.status
7
txid: 0
8
ts: '2018-11-07T10:10:41.051Z'
9
resource_type: Attribute
10
status: updated
11
resource:
12
resource: {id: Encounter, resourceType: Entity}
13
valueSet: {id: encounter-status, resourceType: ValueSet}
14
path: [status]
15
module: fhir-3.0.1
16
order: 10
17
source: code
18
type: {id: code, resourceType: Entity}
19
isSummary: true
20
resourceType: Attribute
21
description: planned | arrived | triaged | in-progress | onleave | finished | cancelled +
22
isModifier: true
23
isRequired: true
24
query: ['select * from Attribute where id = ?', Encounter.status]
Copied!
Request
1
GET /Attribute?_query=get-by-id&rid=Encounter.status
Copied!
Pay attention that only
{{resourceType}}can be used in place of table name, because this variable is secure and will be inserted directly into the query. Other variables will be escaped and can't be used in such parts of a query.See another tutorial:
Last modified 1yr ago
Copy link