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.
...
| Code Block |
|---|
{
"cpId": <cpId>,
"aql": <AQL>,
"wideRowMode": <OFF|SHALLOW|DEEP>,
"outputColumnExprs": <true|false>,
"outputIsoDateTime": <true|false>
} |
...
Parameters
...
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.
...
Example
Given below is an example query request to obtain count of aliquots by visit dates:
| Code Block | ||
|---|---|---|
| ||
{
"cpId": 1,
"aql": "select Participant.ppid, SpecimenCollectionGroup.collectionDate, count(distinct Specimen.id) where Specimen.lineage = \"Aliquot\"",
"outputIsoDateTime": true
} |
Sample response:
...
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 |
| ||
| 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:
| Code Block | ||
|---|---|---|
| ||
{
"cpId": 1,
"aql": "select Participant.ppid, SpecimenCollectionGroup.collectionDate, count(distinct Specimen.id) where Specimen.lineage = \"Aliquot\"",
"outputIsoDateTime": true
} |
Sample response:
| Code Block |
|---|
{
"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'
| Code Block | ||||
|---|---|---|---|---|
| ||||
{
"count": 23,
"queries": [
{
"id": 23,
"title": "Show details of tissue specimen having frozen event",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273075000
},
{
"id": 22,
"title": "Show list of tissue specimens frozen within 15 mins of received time",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273075000
},
{
"id": 21,
"title": "Show specimens stored in a Container",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273075000
},
{
"id": 20,
"title": "Show list of Specimens based on SCG Label",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273075000
},
{
"id": 19,
"title": "Show list of specimens based on participant demographics",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273074000
},
{
"id": 18,
"title": "Show list of Specimens based on participant info (PPID or MRN)",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273074000
},
{
"id": 17,
"title": "Show specimen information based on Specimen Label",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273074000
},
{
"id": 16,
"title": "Specimen kit report query",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273074000
},
{
"id": 15,
"title": "Specimen Catalog",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273073000
},
{
"id": 14,
"title": "Show list of specimens based on specimen details",
"createdBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedBy": {
"id": 1,
"type": null,
"firstName": "System",
"lastName": null,
"loginName": null,
"domain": null,
"emailAddress": null,
"instituteName": null,
"primarySite": null,
"admin": null,
"instituteAdmin": null,
"manageForms": null,
"cpCount": 0,
"creationDate": null,
"activityStatus": null
},
"lastModifiedOn": 1575273073000
}
]
} |
Execute a saved-query from API
Request Details
| Item | Value | |||||
|---|---|---|---|---|---|---|
| HTTP Method | POST | |||||
| URI | /rest/ng/query/{id} | |||||
| Body |
| |||||
| 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 |
Sample response
| Code Block | ||||
|---|---|---|---|---|
| ||||
{ "columnMetadata": [ { "expr": "CollectionProtocol.Title", "aggregate": false }, { "expr": "Participant.ppid", "aggregate": false }, { "expr": "Participant.gender", "aggregate": false }, { "expr": "Participant.medicalRecord.medicalRecordNumber", "aggregate": false }, { "expr": "Participant.medicalRecord.mrnSiteName", "aggregate": false }, { "expr": "Participant.race", "aggregate": false }, { "expr": "SpecimenCollectionGroup.name", "aggregate": false }, { "expr": "SpecimenCollectionGroup.clinicalDiagnosis", "aggregate": false }, { "expr": "SpecimenCollectionGroup.site", "aggregate": false }, { "expr": "Specimen.label", "aggregate": false }, { "expr": "Specimen.extensions.SpecimenCollectionEvent.time", "aggregate": false }, { "expr": "Specimen.class", "aggregate": false }, { "expr": "Specimen.type", "aggregate": false }, { "expr": "Specimen.specimenPosition.containerName", "aggregate": false }, { "expr": "Specimen.specimenPosition.positionDimensionOneString", "aggregate": false }, { "expr": "Specimen.specimenPosition.positionDimensionTwoString", "aggregate": false }, { "expr": "Specimen.pathologicalStatus", "aggregate": false }, { "expr": "Specimen.tissueSite", "aggregate": false }, { "expr": "Specimen.availableQty", "aggregate": false } ], "columnLabels": [ "Collection Protocol# Title", "Participant# PPID", "Participant# Gender", "Participant# MRN", "Participant# MRN Site", "Participant# Race", "Visit# Name", "Visit# Clinical Diagnosis (Deprecated)", "Visit# Visit Site", "Specimen# Specimen Label", "Specimen# Collection Event# Date and Time", "Specimen# Class", "Specimen# Type", "Specimen# Container Name", "Specimen# Container Column", "Specimen# Container Row", "Specimen# Pathological Status", "Specimen# Anatomic Site", "Specimen# Available Quantity" ], "columnTypes": [ "STRING", "STRING", "STRING", "STRING", "STRING", "STRING", "STRING", "STRING", "STRING", "STRING", "DATE", "STRING", "aggregate": falseSTRING", "STRING", }, "STRING", { "STRING", "expr": "SpecimenCollectionGroup.collectionDateSTRING", "STRING", "aggregate": false "FLOAT" }], "columnUrls": [ { "#/object-state-params-resolver?stateName=cp-detail.overview&objectName=collection_protocol&key=title&value={{$value}}", "expr": "count ( distinct Specimen.id )#/object-state-params-resolver?stateName=participant-detail.overview&objectName=collection_protocol_registration&key=id&value={{$cprId}}", "aggregate": true } ]null, "columnLabels": [ "Participant# PPID"null, "Visit# Visit Date#/object-state-params-resolver?stateName=site-detail.overview&objectName=site&key=name&value={{$value}}", "Column"null, ], "columnTypes": ["#/object-state-params-resolver?stateName=visit-detail.overview&objectName=visit&key=name&value={{$value}}", "STRING"null, "DATE", "INTEGER" ]#/object-state-params-resolver?stateName=site-detail.overview&objectName=site&key=name&value={{$value}}", "columnUrls": [ "#/object-state-params-resolver?stateName=participantspecimen-detail.overview&objectName=collection_protocol_registrationspecimen&key=id&value={{$cprId$specimenId}}", null, null, null, ]"#/object-state-params-resolver?stateName=container-detail.locations&objectName=storage_container&key=name&value={{$value}}", "rows": [ null, null, [ null, "OPSMN-0001-TCP"null, null "2015-06-04T00:00:00", ], "184rows": [ ], [ "OPSMN-0001-TCP", "2016-06-30T11:30:00", $$cp_reg_1$$", "25" null, ], null, [ "OPSMN-0003-TCP"null, "2015-05-05T00:00:00"null, "107$$cp_visit_1$$", ], null, [ "OPSMN-0003-TCP"Pune Site", "2015-05-19T00:00:000000004", "17" 02-12-2019 16:03", ], ["Fluid", "OPSMN-0003-TCPBuffy Coat", "2015-07-24T00:00:00test_box1", "4", ], "1", [ "OPSMN-0003-TCP"Not Specified", "2015-08-17T00:00:00Not Specified", "56.00" ] ], "columnIndices": null, ... ] "dbRowsCount": 1 } |