Executing Any AQL
Below API can be used to execute any AQL and obtain the desired results. AQL is custom query language built-in OpenSpecimen to allow power users perform complex queries on data without requiring to know anything about the OpenSpecimen data model.
| Item | Value |
|---|---|
| HTTP Method | POST |
| URI | /rest/ng/query |
| Body | {
"cpId": <cpId>,
"aql": <AQL>,
"wideRowMode": <OFF|SHALLOW|DEEP>,
"outputColumnExprs": <true|false>,
"outputIsoDateTime": <true|false>,
"timeoutInSeconds": <integer>
}
|
| Response | JSON containing desired results |
Parameters
| Parameter | Description |
|---|---|
| cpId | Collection protocol ID. If specified, limits the results to specified CP records. Useful when AQL contains CP specific custom fields. |
| aql | Valid AQL string that needs to be executed |
| wideRowMode | Specifies whether multi-valued attributes result in a single row or one row per value. Default value is OFF. Other permitted values are SHALLOW and DEEP. Try out to see what fits best for your use case. |
| outputColumnExprs | Specifies whether the column labels or AQL expression needs to be included in the query response. By default, user friendly column labels are included in query response. |
| outputIsoDateTime | Specifies how the date column values needs to be serialised in the query response. If true, then date/time values are serialised using ISO format: yyyy-MM-dd'T'HH:mm:ss. Otherwise, date/time values are serialised using the format specified in OS locale settings |
timeoutInSeconds | Specifies how long to wait for the query to complete before aborting it. When not specified, this field defaults to 55 seconds. This is a guard against the run away query that hog the system resources, eventually resulting in service disruption. To wait forever for the query to complete, use -1. This should be avoided unless you are aware about the consequences of allowing a runaway query. |
Example
Given below is an example query request to obtain count of aliquots by visit dates:
{
"cpId": 1,
"aql": "select Participant.ppid, SpecimenCollectionGroup.collectionDate, count(distinct Specimen.id) where Specimen.lineage = \"Aliquot\"",
"outputIsoDateTime": true
}
Sample response
Getting List of all saved-queries
Request Details
| Item | Value |
|---|---|
| HTTP Method | GET |
| URI | /rest/ng/saved-queries |
| Response | JSON containing detailed list of saved queries |
Parameters
| Parameter | Description |
|---|---|
| cpId | The cpId is used to filter the saved queries based on the CP. This is helpful in running the queries in background using scheduled jobs for initialisation of CP specific custom fields/forms. Note: This is the CP that you select in the top-left dropdown of the add/edit query wizard. |
searchString | Matches a substring in the title of the saved query. |
start | Used for paginating the results. If start=5, the output result will start from row number 5. |
max | Used for paginating the results. If max=10, the output result will be maximum 10 rows |
countReq | Boolean type parameter, if specified as 'true' output will show the total number of saved-queries. Otherwise it won't. |
Sample response
Get URL: 'rest/ng/saved-queries?countReq=true&start=0&max=10'
Executing Saved Query
Request Details
| Item | Value |
|---|---|
| HTTP Method | POST |
| URI | /rest/ng/query/{savedQueryId} |
| Body | {
"drivingForm":"Participant",
"wideRowMode": "DEEP",
"startAt": 0,
"maxResults": 20
}
|
| Response | JSON containing the output of the saved queries |
Parameters
Note: The savedQueryId placeholder should be replaced with the actual ID of the saved query.
| Parameter | Description | Allowed Values |
|---|---|---|
drivingForm | Driving form determines the search perspective. When left empty, it defaults to Participant. (For example when drivingForm is Participant, the root table is 'catissue_coll_prot_reg' which is then used to join with the other tables.Similarly when drivingForm is Specimen, the root table will be catissue_specimen, which will be joined with the other tables.) | Participant, Specimen |
startAt | Used for paginating the results. If startAt=5, the output result will startAt from row number 5. | Integer |
maxResults | Used for paginating the results. If maxResults=10, the output result will be maximum 10 rows | Integer |
wideRowMode | Specifies whether multi-valued attributes result in a single row or one row per value. Default value is OFF. Other permitted values are SHALLOW and DEEP. Try out to see what fits best for your use case. | DEEP, SHALLOW, OFF |