With the AidboxQuery resource, you can turn your SQL query into REST Endpoint.
PUT /AidboxQuery/<query-name>params:# define filter parameterfilter:# make it requiredisRequired: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 valuedefault:'ups'count:type:integerdefault: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 responsecount-query: 'SELECT count(*) from patient where id ilike = {{params.filter}}# not required. enable links in response, see the section belowenable-links:false# not required. `query` or `execute`. see belowtype:query
Here is a self-debugging AidboxQuery to start with:
PUT /AidboxQuery/debugparams:filter:isRequired:truetype:stringformat:'%% %s%%'count:type:integerdefault:10data:type:objectdefault: {resourceType:'Nop'}flag:default:truetype:booleanquery:| SELECT {{params.filter}}::text as filter, {{params.flag}} as flag, {{params.data}}::jsonb as data, {{params}}::jsonb as params, {{params.count}} as count, {{}} as ctxcount-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-reportparams:date:isRequired:truequery:| 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'
GET /$query/daily-report?date=2013-06-08# Status: 200data:- {class:'{"code": "IMP"}',count:2}- {class:'{"code": "AMB"}',count:1}query: [...]
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/$debugquery:# AidboxQuery resource contentquery:'SELECT {{params.id}} as params_id'params:id: {isRequired:true}# test paramsparams:id:'ups'---# actual resultdata:- {params_id:ups}# sql queryquery: ['SELECT ? as params_id',ups]# execution planplan:|- 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 contextctx:remote-addr:0:0:0:0:0:0:0:1client: { ... }params: {id:ups}headers: {...}uri:/$query/$debuguser: {...}scheme:httprequest-method:post
Debug AidboxQuery
You can debug AidboxQuery with _explain=true parameter:
GET /$query/daily-report?date=2013-06-08&_explain=true# Status: 200plan:|- 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 msctx:params: {date:'2013-06-08',_explain:'true'}resourceType:nullsafe-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 /AidboxQueryquery:'select * from public.User where id = {{user.id}}'id:user-inforesourceType:AidboxQuery
Sample query will be:
GET /$query/user-info HTTP/1.1Host:<YOUR-BOX>.aidbox.appAuthorization:Bearer <YOUR-ACCESS-TOKEN>Accept:text/yaml
# Status: 200data:- id:testusertxid:198ts:'2018-10-16T13:30:03.036Z'resource_type:Userstatus:updatedresource: {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/q1query:| 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:trueparams:patient:type:stringisRequired:truedefault:"pt1"_count:type:integerdefault:100_page:type:integerdefault: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
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/truncatequery:'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 /AidboxQueryresourceType:AidboxQueryquery:'select * from {{resourceType}} where id = {{params.rid}}'params:rid: {isRequired:true}id:get-by-id# respquery:select * from {{resourceType}} where id = {{params.rid}}params:rid: {isRequired:true}id:get-by-idresourceType:AidboxQuerymeta: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# respdata:- id:Encounter.statustxid:0ts:'2018-11-07T10:10:41.051Z'resource_type:Attributestatus:updatedresource:resource: {id:Encounter,resourceType:Entity}valueSet: {id:encounter-status,resourceType:ValueSet}path: [status]module:fhir-3.0.1order:10source:codetype: {id:code,resourceType:Entity}isSummary:trueresourceType:Attributedescription:planned | arrived | triaged | in-progress | onleave | finished | cancelled +isModifier:trueisRequired:truequery: ['select * from Attribute where id = ?',Encounter.status]
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}}".