Versions Compared

Old Version 4

changes.mady.by.user Swapnil Ingle

Saved on

compared with

New Version Current

changes.mady.by.user Vinayak Pawar

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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
languagejs
{
	"cpId": 1,
	"aql": "select Participant.ppid, SpecimenCollectionGroup.collectionDate, count(distinct Specimen.id) where Specimen.lineage = \"Aliquot\"",
	"outputIsoDateTime": true
}

Sample response:

...

Table of Contents

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.

ItemValue
HTTP MethodPOST
URI/rest/ng/query
Body


Code Block
{
  "cpId": <cpId>,
  "aql": <AQL>,
  "wideRowMode": <OFF|SHALLOW|DEEP>,
  "outputColumnExprs": <true|false>,
  "outputIsoDateTime": <true|false>,
  "timeoutInSeconds": <integer> 
}


ResponseJSON containing desired results

Parameters

ParameterDescription
cpIdCollection protocol ID. If specified, limits the results to specified CP records. Useful when AQL contains CP specific custom fields.
aqlValid 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. 

outputColumnExprsSpecifies 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.
outputIsoDateTimeSpecifies 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:

Code Block
languagejs
{
	"cpId": 1,
	"aql": "select Participant.ppid, SpecimenCollectionGroup.collectionDate, count(distinct Specimen.id) where Specimen.lineage = \"Aliquot\"",
	"outputIsoDateTime": true
}

Sample response

Code Block
collapsetrue
{
    "columnMetadata": [
        "STRING",{
        "DATE",    "expr": "Participant.ppid",
   "INTEGER"     ],     "columnUrlsaggregate": [false
        "#/object-state-params-resolver?stateName=participant-detail.overview&objectName=collection_protocol_registration&key=id&value={{$cprId}}",
        null,{
        null     ]"expr": "SpecimenCollectionGroup.collectionDate",
    "rows":  [      "aggregate": false
        [},
        {
   "OPSMN-0001-TCP",         "expr": "count (  "2015-06-04T00:00:00distinct Specimen.id )",
            "184aggregate": true
       ], }
    ],
  [  "columnLabels": [
         "OPSMN-0001-TCP"Participant# PPID",
        "Visit# Visit   "2016-06-30T11:30:00",
Date",
           "25Column"
   
    ],
        [
   "columnTypes": [
        "OPSMN-0003-TCPSTRING",
            "2015-05-05T00:00:00"DATE",

           "107INTEGER"
        ],
       "columnUrls": [
 
          "OPSMN-0003-TCP"#/object-state-params-resolver?stateName=participant-detail.overview&objectName=collection_protocol_registration&key=id&value={{$cprId}}",
        null,
   "2015-05-19T00:00:00",     null
    ],
    "17rows":  [       ],
        [
            "OPSMN-00030001-TCP",
            "2015-0706-24T0004T00:00:00",
            "4184"
        ],
        [
            "OPSMN-00030001-TCP",
            "20152016-0806-17T0030T11:0030:00",
            "525"
        ],
        ...
	]
}

Getting List of all saved-queries

Request Details

...

Parameters

...

searchString

...

start

...

Used for paginating the results. If start=5, the output result will start from row number 5.

...

max

...

countReq

...

Sample response

Get URL: 'rest/ng/saved-queries?countReq=true&start=0&max=10'

Code Block
languagejs
collapsetrue
{
    "count": 23,
    "queries": [[
            "OPSMN-0003-TCP",
            "2015-05-05T00:00:00",
            "107"
        ],
        [
             {"OPSMN-0003-TCP",
             "id": 23"2015-05-19T00:00:00",
            "title17":
 "Show details of tissue specimen having frozen event"],
        [
   "createdBy": {         "OPSMN-0003-TCP",
            "id": 1,2015-07-24T00:00:00",
            "4"
       "type": null],
        [
       "firstName": "System",    "OPSMN-0003-TCP",
            "lastName": null2015-08-17T00:00:00",
                "loginName5":
null,                 "domain": null],
        ...
       "emailAddress": null,
                "instituteName": null,
                "primarySite": null,
           	]
}

Getting List of all saved-queries

Request Details

ItemValue
HTTP MethodGET
URI/rest/ng/saved-queries
ResponseJSON containing detailed list of saved queries 

Parameters

ParameterDescription
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'

Code Block
languagejs
collapsetrue
{
    "count": 23,
    "adminqueries": null,[
        {
            "instituteAdminid": null23,
                "manageForms": null,
                "cpCount": 0,
                "creationDate": null,
                "activityStatus": null
            }"title": "Show details of tissue specimen having frozen event",
            "lastModifiedBycreatedBy": {
                "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
            },
            "lastModifiedOnlastModifiedBy": 1575273075000{
            },    "id": 1,
   {             "idtype": 22null,
            "title    "firstName": "Show list of tissue specimens frozen within 15 mins of received time","System",
                    "createdBylastName": {null,
                "idloginName": 1null,
                "typedomain": null,
                "firstNameemailAddress": "System"null,
                "lastNameinstituteName": null,
                "loginNameprimarySite": null,
                "domainadmin": null,
                "emailAddressinstituteAdmin": null,
                "instituteNamemanageForms": null,
                "primarySitecpCount": null0,
                "admincreationDate": null,
                "instituteAdminactivityStatus": null,
            },
   "manageForms": null,        "lastModifiedOn": 1575273075000
       "cpCount": 0},
        {
            "creationDateid": null22,

               "activityStatustitle": null"Show list of tissue specimens frozen within 15 mins of received time",
  },
            "lastModifiedBycreatedBy": {
                "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": 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
            },
            "lastModifiedBylastModifiedOn": {1575273075000
        },
       "id": 1, {
            "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
            },
            "lastModifiedOnlastModifiedBy": {
1575273075000         },       "id": 1,
{                "idtype": 20null,
                "titlefirstName": "ShowSystem",
list of Specimens based on SCG Label",             "createdBylastName": {null,
                "idloginName": 1null,
                "typedomain": null,
                "firstNameemailAddress": "System"null,
                "lastNameinstituteName": null,
                "loginNameprimarySite": null,
                "domainadmin": null,
                "emailAddressinstituteAdmin": null,
                "instituteNamemanageForms": null,
                "primarySitecpCount": null0,
                "admincreationDate": null,
                "instituteAdminactivityStatus": null,
            },
   "manageForms": null,        "lastModifiedOn": 1575273075000
       "cpCount": 0},
        {
            "creationDateid": null20,
                "activityStatustitle": null"Show list of Specimens based on        }SCG Label",
            "lastModifiedBycreatedBy": {
                "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
            },
            "lastModifiedOnlastModifiedBy": 1575273075000{
        },         {
            "id": 19,
            "title": "Show list of specimens based on participant demographics",
            "createdBy": {
                "id": 1"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
            },
            "lastModifiedBylastModifiedOn": {1575273075000
        },
       "id": 1, {
            "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
            },
            "lastModifiedOnlastModifiedBy": 1575273074000{
        },         {"id": 1,
                "idtype": 18null,
                "titlefirstName": "Show list of Specimens based on participant info (PPID or MRN)",
"System",
                "createdBylastName": {null,
                "idloginName": 1null,
                "typedomain": null,
                "firstNameemailAddress": "System"null,
                "lastNameinstituteName": null,
                "loginNameprimarySite": null,
                "domainadmin": null,
                "emailAddressinstituteAdmin": null,
                "instituteNamemanageForms": null,
                "primarySitecpCount": null0,
                "admincreationDate": null,
                "instituteAdminactivityStatus": null,
            },
   "manageForms": null,        "lastModifiedOn": 1575273074000
       "cpCount": 0},
        {
            "creationDateid": null18,
   
            "activityStatustitle": null"Show list of Specimens based on participant info (PPID     }or MRN)",
            "lastModifiedBycreatedBy": {
                "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": 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
            },
            "lastModifiedBylastModifiedOn": {1575273074000
        },
       "id": 1,
    {
            "typeid": null17,
                "firstNametitle": "System",
   Show specimen information based on Specimen Label",
            "lastNamecreatedBy": null,{
                "loginNameid": null1,
                "domaintype": null,
                "emailAddressfirstName": null"System",
                "instituteNamelastName": null,
                "primarySiteloginName": null,
                "admindomain": null,
                "instituteAdminemailAddress": null,
                "manageFormsinstituteName": null,
                "cpCountprimarySite": 0null,
                "creationDateadmin": null,
                "activityStatusinstituteAdmin": null,
                "manageForms": }null,
                "lastModifiedOncpCount": 15752730740000,
        },         {"creationDate": null,
            "id": 16,   "activityStatus": null
        "title": "Specimen kit report query"},
            "createdBylastModifiedBy": {
                "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
            },
            "lastModifiedBylastModifiedOn": {1575273074000
        },
       " {
            "id": 116,
   
            "typetitle": null,"Specimen kit report  query",
            "firstNamecreatedBy": "System",{
                "lastNameid": null1,
                "loginNametype": null,
                "domainfirstName": null"System",
                "emailAddresslastName": null,
                "instituteNameloginName": null,
                "primarySitedomain": null,
                "adminemailAddress": null,
                "instituteAdmininstituteName": null,
                "manageFormsprimarySite": null,
                "cpCountadmin": 0null,
                "creationDateinstituteAdmin": null,
                "activityStatusmanageForms": null,
            },    "cpCount": 0,
       "lastModifiedOn": 1575273074000        "creationDate": }null,
    
   {             "idactivityStatus": 15,null
            "title": "Specimen Catalog"},
            "createdBylastModifiedBy": {
                "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
            },
            "lastModifiedBylastModifiedOn": 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
            },
            "lastModifiedOnlastModifiedBy": 1575273073000{
           },     "id": 1,
   {             "idtype": 14null,
                "titlefirstName": "System"Show,
list of specimens based on specimen details"             "lastName": null,
                "loginName": null,
                "createdBydomain": {null,
                "idemailAddress": 1null,
                "typeinstituteName": null,
                "firstNameprimarySite": null,
                "Systemadmin": null,
                "lastNameinstituteAdmin": null,
                "loginNamemanageForms": null,
                "domaincpCount": 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
        }
    ]
}

Executing Saved Query

Request Details

ItemValue
HTTP MethodPOST
URI/rest/ng/query/{savedQueryId}
Body


Code Block
languagejs
{
	"drivingForm":"Participant",
	"wideRowMode": "DEEP",
	"startAt": 0,
	"maxResults": 20
}


ResponseJSON containing the output of the saved queries

Parameters

Note: The savedQueryId placeholder should be replaced with the actual ID of the saved query.


ParameterDescriptionAllowed 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 rowsInteger

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
languagejs
collapsetrue
{
    "columnMetadata": [
      "emailAddress": null,    {
            "instituteNameexpr": null"CollectionProtocol.Title",

               "primarySiteaggregate": null,false
        },
       "admin": null, {
                "instituteAdminexpr": null,
   "Participant.ppid",
            "manageFormsaggregate": null,false
        },
       "cpCount": 0,
    {
            "creationDateexpr": null"Participant.gender",
                "activityStatusaggregate": null
  false
         },
            "lastModifiedBy": {

               "idexpr": 1,
   "Participant.medicalRecord.medicalRecordNumber",
            "typeaggregate": null,false
          },
     "firstName": "System",    {
            "lastNameexpr": null,
"Participant.medicalRecord.mrnSiteName",
               "loginNameaggregate": null,false
        },
       "domain": null,
 {
               "emailAddressexpr": null"Participant.race",
                "instituteNameaggregate": null,false
        },
       "primarySite": null,
 {
               "adminexpr": null"SpecimenCollectionGroup.name",
                "instituteAdminaggregate": null,false
            },
   "manageForms": null,    {
            "cpCountexpr": 0"SpecimenCollectionGroup.clinicalDiagnosis",
                "creationDateaggregate": false
null,        },
        "activityStatus":{
null             }"expr": "SpecimenCollectionGroup.site",
            "lastModifiedOnaggregate": 1575273073000false
        },
    ]
}

Execute a saved-query from API

Request Details

...

Code Block
languagejs
{
	"drivingForm":"Participant",
	"runType":"Count",
	"wideRowMode": "DEEP",
	"startAt": 0,
	"maxResults": 20
}

...

Parameters

...

runType

...

drivingForm

...

startAt

...

Used for paginating the results. If startAt=5, the output result will startAt from row number 5.

...

maxResults

...

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.

...

Sample response

Code Block
languagejs
collapsetrue
{
    "columnMetadata": [    {
            "expr": "Specimen.label",
            "aggregate": false
        },
        {
            "expr": "Specimen.extensions.SpecimenCollectionEvent.time",
            "aggregate": false
        },
        {
            "expr": "CollectionProtocolSpecimen.Titleclass",
            "aggregate": false
        },
        {
            "expr": "ParticipantSpecimen.ppidtype",
            "aggregate": false
        },
        {
            "expr": "ParticipantSpecimen.specimenPosition.gendercontainerName",
            "aggregate": false
        },
        {
            "expr": "ParticipantSpecimen.medicalRecordspecimenPosition.medicalRecordNumberpositionDimensionOneString",
            "aggregate": false
        },
        {
            "expr": "ParticipantSpecimen.medicalRecordspecimenPosition.mrnSiteNamepositionDimensionTwoString",
            "aggregate": false
        },
        {
            "expr": "ParticipantSpecimen.racepathologicalStatus",
            "aggregate": false
        },
        {
            "expr": "SpecimenCollectionGroupSpecimen.nametissueSite",
            "aggregate": false
        },
        {
            "expr": "SpecimenCollectionGroupSpecimen.clinicalDiagnosisavailableQty",
            "aggregate": false
        }
    ],
    "columnLabels": [
  {      "Collection Protocol# Title",
    "expr": "SpecimenCollectionGroup.site",   "Participant# PPID",
        "aggregateParticipant# Gender":,
false        "Participant# }MRN",
        {"Participant# MRN   Site",
        "expr":Participant# Race"Specimen.label",
        "Visit# Name",
  "aggregate": false      "Visit# Clinical Diagnosis }(Deprecated)",
        {"Visit# Visit   Site",
        "expr":Specimen# "Specimen.extensions.SpecimenCollectionEvent.timeSpecimen Label",
        "Specimen# Collection Event# Date and Time"aggregate":,
false        "Specimen# }Class",
        {"Specimen# Type",
        "Specimen#   "expr": "Specimen.classContainer Name",
        "Specimen# Container   "aggregate": falseColumn",
        "Specimen# Container }Row",
        {"Specimen# Pathological Status",
        "Specimen# Anatomic "expr": "Specimen.type"Site",
            "aggregate": false
   "Specimen# Available Quantity"
    }],
    "columnTypes": [
  {      "STRING",
      "expr":  "Specimen.specimenPosition.containerNameSTRING",
        "STRING",
   "aggregate": false    "STRING",
    },    "STRING",
    {    "STRING",
        "expr": "Specimen.specimenPosition.positionDimensionOneStringSTRING",
        "STRING",
   "aggregate": false    "STRING",
    },    "STRING",
    {    "DATE",
        "expr": "Specimen.specimenPosition.positionDimensionTwoString",
 STRING",
          "aggregateSTRING":,
false         }"STRING",
        {
    "STRING",
       "expr": "Specimen.pathologicalStatusSTRING",
            "aggregateSTRING":,
false         }"STRING",
        {"FLOAT"
    ],
       "exprcolumnUrls": "Specimen.tissueSite",[
        "#/object-state-params-resolver?stateName=cp-detail.overview&objectName=collection_protocol&key=title&value={{$value}}",
   "aggregate": false         }"#/object-state-params-resolver?stateName=participant-detail.overview&objectName=collection_protocol_registration&key=id&value={{$cprId}}",
        {null,
            "expr": "Specimen.availableQty"null,
            "aggregate": false"#/object-state-params-resolver?stateName=site-detail.overview&objectName=site&key=name&value={{$value}}",
         }
    ],
null,
   "columnLabels": [         "Collection Protocol# Title",
        "Participant# PPID"#/object-state-params-resolver?stateName=visit-detail.overview&objectName=visit&key=name&value={{$value}}",
        "Participant# Gender"null,
        "Participant# MRN#/object-state-params-resolver?stateName=site-detail.overview&objectName=site&key=name&value={{$value}}",
        "Participant# MRN Site#/object-state-params-resolver?stateName=specimen-detail.overview&objectName=specimen&key=id&value={{$specimenId}}",
        "Participant# Race"null,
        "Visit# Name"null,
        "Visit# Clinical Diagnosis (Deprecated)"null,
        "Visit# Visit Site",#/object-state-params-resolver?stateName=container-detail.locations&objectName=storage_container&key=name&value={{$value}}",
        null,
 "Specimen# Specimen Label",     null,
   "Specimen# Collection Event# Date and Time"null,
        "Specimen# Class"null,
        "Specimen#null
Type",    ],
    "rows"Specimen#: [
Container Name",       [
 "Specimen# Container Column",         "Specimen#TCP",
Container Row",         "Specimen# Pathological Status "$$cp_reg_1$$",
          "Specimen# Anatomic Site"null,
        "Specimen# Available Quantity"    null,
     ],     "columnTypes": [ null,
       "STRING",     null,
   "STRING",         "STRING$$cp_visit_1$$",
        "STRING",    null,
    "STRING",         "STRINGPune Site",
            "STRING0000004",
        "STRING",    "02-12-2019 16:03",
   "STRING",         "STRINGFluid",
        "DATE",    "Buffy Coat",
   "STRING",         "STRINGtest_box1",
            "STRING4",
            "STRING",1",
            "STRINGNot Specified",
        "STRING",    "Not Specified",
   "STRING",         "FLOAT6.00"
    ],    ]
"columnUrls": [    ],
    "#/object-state-params-resolver?stateName=cp-detail.overview&objectName=collection_protocol&key=title&value={{$value}}",
   columnIndices": null,
    "#/object-state-params-resolver?stateName=participant-detail.overview&objectName=collection_protocol_registration&key=id&value={{$cprId}}",
        null,
        null,
        "#/object-state-params-resolver?stateName=site-detail.overview&objectName=site&key=name&value={{$value}}",
        null,
        "#/object-state-params-resolver?stateName=visit-detail.overview&objectName=visit&key=name&value={{$value}}",
        null,
        "#/object-state-params-resolver?stateName=site-detail.overview&objectName=site&key=name&value={{$value}}",
        "#/object-state-params-resolver?stateName=specimen-detail.overview&objectName=specimen&key=id&value={{$specimenId}}",
        null,
        null,
        null,
        "#/object-state-params-resolver?stateName=container-detail.locations&objectName=storage_container&key=name&value={{$value}}",
        null,
        null,
        null,
        null,
        null
    ],
    "rows": [
        [
            "TCP",
            "$$cp_reg_1$$",
            null,
            null,
            null,
            null,
            "$$cp_visit_1$$",
            null,
            "Pune Site",
            "0000004",
            "02-12-2019 16:03",
            "Fluid",
            "Buffy Coat",
            "test_box1",
            "4",
            "1",
            "Not Specified",
            "Not Specified",
            "6.00"
        ]
    ],
    "columnIndices": null,
    "dbRowsCount": 1
}dbRowsCount": 1
}


Exporting Query Data

Request

MethodPOST
URLrest/ng/query/export
Body


Code Block
languagejs
{
  "aql": "select Participant.ppid, Participant.regDate, Participant.gender, Participant.race, Specimen.label, Specimen.type where  Participant.participantId exists",
  "wideRowMode": "DEEP"
}


Response


Code Block
languagejs
{
  "dataFile": "query_0_61e59bc2-9350-47db-9af8-a1e7a3b65bc2",
  "completed": false
}



Parameters

aqlValid AQL query whose output needs to be exported to a CSV file
dataFileHandle or file ID to use for downloading the exported CSV file
completedBoolean indicating whether the exported CSV file can be downloaded right away

Download Exported Data File

Request

MethodGET
URLrest/ng/query/export?fileId=<dataFile>
Response

Scenario 1: When the exported data file is not ready


HTTP 400 with the following error message in the response body


Code Block
languagejs
[
  {
    "code":"QUERY_EXPORT_DATA_IN_PROGRESS",
    "message":"The query result data export is in progress. Retry downloading the file after some time."
  }
]


Scenario 2: When the exported data file is ready

HTTP 200 with the ZIP binary stream in the response body

Parameters

fileIdThe handle or file ID returned by the export API

Query Export in Action

Initiate Export

Image Added

Download Exported File - Attempt 1 of N

Image Added

Download Exported File

Image Added


FAQ

Let’s assume we've the following data:

  • A specimen record with label L

  • Specimen L has 2 biohazards - H1, H2

  • Specimen L has 2 frozen events F1, F2

  • Output includes specimen label (L), biohazards, and one or more fields of frozen event.

  1. What does OFF, SHALLOW and DEEP do? What are the differences between them?

    Let's assume we've a specimen record L with biohazards H1 and H2. Also, let's assume the specimen L has 2 frozen events F1 and F2.Using above, it is easy to explain the various wide row modes:

    1. wideRowMode = OFF. There will be one row for each combination of biohazard and frozen event. That is - 

      (L, H1, F1), (L, H1, F2), (L, H2, F1), (L, H2, F2)

    2. wideRowMode = SHALLOW. There will be one row for each frozen event. That is - 

      (L, H1, H2, F1), (L, H1, H2, F2)

    3. wideRowMode = DEEP. There will be one row for each specimen irrespective of the number of biohazards and frozen events. That is - 

      (L, H1, H2, F1, F2)

  2. Why is it so hard to get the same results from the same query in both the API and in OpenSpecimen?

    The UI uses either SHALLOW or DEEP. It does not use OFF.

    When the “Enable Wide Rows” checkbox is ticked, the wideRowMode=DEEP. When it is unticked, wideRowMode=SHALLOW.