With the 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}}
# not required. enable links in response, see the section below
enable-links: false
# not required. `query` or `execute`. see below
type: query
Here is a self-debugging AidboxQuery to start 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'
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:
POST /$query/$debug
query:
# AidboxQuery resource content
query: 'SELECT {{params.id}} as params_id'
params:
id: {isRequired: true}
# test params
params:
id: 'ups'
---
# actual result
data:
- {params_id: ups}
# sql query
query: ['SELECT ? as params_id', ups]
# execution plan
plan: |-
Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.009..0.022 rows=1 loops=1)
Planning Time: 0.025 ms
Execution Time: 0.067 ms
# templating context
ctx:
remote-addr: 0:0:0:0:0:0:0:1
client: { ... }
params: {id: ups}
headers: {...}
uri: /$query/$debug
user: {...}
scheme: http
request-method: post
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 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
POST /AidboxQuery
query: 'select * from public.User where id = {{user.id}}'
id: user-info
resourceType: AidboxQuery
Sample query will be:
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 the access token provided. Check OAuth2.0 doc for additional information.
Return links
You can use enable-links parameter to include links in the response. Here is simple example how to use paging with AidboxQuery and include links.
PUT /AidboxQuery/q1
query: |
SELECT
sr.*
FROM ServiceRequest sr
WHERE sr.Resource #>> '{subject,id}' = {{params.patient}}
LIMIT {{params._count}} OFFSET {{params._page}}
count-query: |
SELECT count(*) FROM ServiceRequest sr
WHERE sr.Resource #>> '{subject,id}' = {{params.patient}}
enable-links: true
params:
patient:
type: string
isRequired: true
default: "pt1"
_count:
type: integer
default: 100
_page:
type: integer
default: 1
AidboxQuery expects that parameters _count and _page (exactly such names) are defined, otherwise links won't be attached.
After sending GET /$query/q1?patient=pt1&_count=1&_page=2 we will get
data:
- id: sr3
txid: 914
cts: '2022-09-19T14:16:56.752021Z'
ts: '2022-09-19T14:16:56.752021Z'
resource_type: ServiceRequest
status: created
resource:
a:
id: >-
org2
resourceType: Organization
intent: plan
status: final
subject:
id: >-
pt1
resourceType: Patient
query:
- |-
SELECT
sr.*
FROM ServiceRequest sr
WHERE sr.Resource #>> '{subject,id}' = ?
LIMIT ? OFFSET ?
- pt1
- 1
- 2
total: 3
link:
- relation: first
url: [base]/$query/q1?patient=pt1&_count=1&_page=1
- relation: self
url: [base]/$query/q1?patient=pt1&_count=1&_page=2
- relation: next
url: [base]/$query/q1?patient=pt1&_count=1&_page=3
- relation: previous
url: [base]/$query/q1?patient=pt1&_count=1&_page=1
- relation: last
url: [base]/$query/q1?patient=pt1&_count=1&_page=3
Query types
AidboxQuery has type field, which can be either query or execute. Default type is query. This means that SELECT statement in query parameter is expected. If you want to make SQL query with execute statements e.g. TRUNCATE, use execute type.
PUT /AidboxQuery/truncate
query: 'TRUNCATE {{resourceType}}; TRUNCATE {{resourceType}}_history'
type: execute
_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
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}}.
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}
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 the query. Other variables will be escaped and can't be used in such parts of a query.
If resourceType name can be interpreted as SQL keyword (e.g. Group), then resourceType should be quated: "{{resourceType}}".
