Introduction

A two-step process is required to export query data as CSV using APIs.

Run query export API: This API initiates the export process. It allows you to specify query AQL and other parameters. The query is run in the background and might take a few seconds or minutes to run.

(Steps to get a query AQL: https://openspecimen.atlassian.net/l/cp/DpHj1PNV)

Download CSV: This API enables you to retrieve the CSV file. Since the query might still be running, you will have to run this in a loop.

See the example code below.

Exporting Query Data

Method	POST
URL	`http[s]://<server_url>/rest/ng/query/export`
Body	`{ "drivingForm": "<DRIVING_FORM>", "cpId": <CP_ID>, "aql": "<AQL>", "wideRowMode": "<DEEP/SHALLOW/OFF>" }`
Response	`{ "dataFile": "query_0_61e59bc2-9350-47db-9af8-a1e7a3b65bc2", "completed": false }`

Parameters

Attribute	Description	Allowed values

Attribute	Description	Allowed values
drivingForm	The search perspective is determined by the driving form. If left empty, it defaults to "Participant." (For instance, when the driving form is set to "Participant," the root table becomes 'catissue_coll_prot_reg,' which is then joined with other tables. Similarly, if the driving form is set to "Specimen," the root table will be 'catissue_specimen,' which will also be joined with other tables.)	Participant, Specimen
cpId	When exporting for a single CP, include its CPID. For exporting all or multiple CPs, use a value of -1.	Integer only
aql	A valid AQL query for exporting data to a CSV file.	String
wideRowMode	Default value is OFF, meaning multi-valued attributes result in a single row. Other permitted values are SHALLOW and DEEP. Experiment to determine which option best suits your use case.	DEEP, SHALLOW, OFF
dataFile	Handle or file ID to use for downloading the exported CSV file.	string
Completed	Boolean indicating whether the exported CSV file can be downloaded right away.	true, false

Download Exported Data File

Method

GET

URL

http[s]://<server_url>rest/ng/query/export?fileId=<dataFile>

Response

Case 1:

When export data file is not ready, you will get HTTP response code 400 with the below message.

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

Case 2:

When the exported data file is ready, you will receive an HTTP response code 200 along with the ZIP binary stream included in the response body.

Sample Code

Attached zip file includes a shell script along with a config file. This is for reference; you can use similar logic in the programming language of your choice.

How to run

Extract the zip file.

Navigate the directory.

Open query-details.config file and add valid values
Make the script executable.

Run the script.

OpenSpecimen

Export Query Data

Introduction

Exporting Query Data

Download Exported Data File

Sample Code

How to run