AidboxQuery
Turn SQL into REST endpoint
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}
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'
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-4
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.
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
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'
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:
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: [email protected], 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.
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
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
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}
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 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}}".See another tutorial:
Last modified 3mo ago