AidboxQuery

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!
Let's upload some sample data using Bulk Upsert:
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:
Custom Search

_explain

SearchQuery

Last modified 8mo ago

Copy link

Contents