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"
],
...
]
}