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>
}
|
| 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 |
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:
{
"columnMetadata": [
{
"expr": "Participant.ppid",
"aggregate": false
},
{
"expr": "SpecimenCollectionGroup.collectionDate",
"aggregate": false
},
{
"expr": "count ( distinct Specimen.id )",
"aggregate": true
}
],
"columnLabels": [
"Participant# PPID",
"Visit# Visit Date",
"Column"
],
"columnTypes": [
"STRING",
"DATE",
"INTEGER"
],
"columnUrls": [
"#/object-state-params-resolver?stateName=participant-detail.overview&objectName=collection_protocol_registration&key=id&value={{$cprId}}",
null,
null
],
"rows": [
[
"OPSMN-0001-TCP",
"2015-06-04T00:00:00",
"184"
],
[
"OPSMN-0001-TCP",
"2016-06-30T11:30:00",
"25"
],
[
"OPSMN-0003-TCP",
"2015-05-05T00:00:00",
"107"
],
[
"OPSMN-0003-TCP",
"2015-05-19T00:00:00",
"17"
],
[
"OPSMN-0003-TCP",
"2015-07-24T00:00:00",
"4"
],
[
"OPSMN-0003-TCP",
"2015-08-17T00:00:00",
"5"
],
...
]
}
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 | Collection protocol ID. If specified, <Find out and add here> |
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'
Execute a saved-query from API
Request Details
| Item | Value |
|---|---|
| HTTP Method | POST |
| URI | /rest/ng/query/{id} |
| Body | {
"drivingForm":"Participant",
"runType":"Count",
"wideRowMode": "DEEP",
"startAt": 0,
"maxResults": 20
}
|
| Response | JSON containing the output of the saved queries |
Parameters
| Parameter | Description | Allowed Values |
|---|---|---|
runType | <Find out and add here> | |
drivingForm | <Find out and add here> | |
startAt | Used for paginating the results. If startAt=5, the output result will startAt from row number 5. | |
maxResults | Used for paginating the results. If maxResults=10, the output result will be maximum 10 rows | |
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 |