Querying the Response Server: /FDAMyStudiesHelp

Querying the Response Server

This topic explains how to retrieve data from the server in order to create reports and visualizations.

Data can be read from the server using two methods in LabKey's mobile app study API:

selectRows: read response values from one or more columns with options to filter and sort the rows
executeSQL: execute an arbitrary SQL SELECT query on the response schema, allowing for complex joins and transformations on the data

All data retrieval is filtered to a single participant (the participant corresponding to the app token accompanying the retrieval request). Also, all data retrieval is constrained to a single schema on the server named "MobileAppResponse". This means you cannot SELECT or JOIN to other tables outside this schema.

selectRows

Uses all the parameters from LABKEY.Query.selectRows, with the following differences:

schemaName is ignored, since MobileAppResponse is hardcoded in this context.
participantId is added. Its value is the <appToken>.

The general pattern for invocation is:

http://<server>/response-selectRows.api?participantId=<appToken>&queryName=<queryName>

where:

<appToken> is the unique string associated with a given participant and study folder. The <appToken> is provided to the mobile app when a participant successfully enrolls in the study.
<queryName> is the name of some table in the schema MobileAppResponse.

An example invocation:

http://myserver.com/response-selectRows.api?participantId=a1f4960d78bcc08295807901a3b74ad&queryName=InitialSurvey

The server will respond with the data in JSON format:

{
  "schemaName" : "MobileAppResponse",
  "queryName" : "InitialSurvey",
  "formatVersion" : 8.3,
  "metaData" : {
    "importTemplates" : [ {
      "label" : "Download Template",
      "url" : "/labkey/query/MyProject/MyStudy/exportExcelTemplate.view?schemaName=list&query.queryName=InitialSurvey&headerType=Name"
    } ],
    "root" : "rows",
    "totalProperty" : "rowCount",
    "description" : null,
    "id" : "Key",
    "fields" : [ {
      "ext" : { },
      "name" : "ParticipantId",
      "align" : "right",
      ...

executeSql

Uses all the parameters from LABKEY.Query.executeSql, with the following differences:

schemaName is ignored, since MobileAppResponse is hardcoded in this context.
participantId is added. Its value is the <appToken>.

The following parameters are required:

sql
participantId

The general pattern for invocation is:

http://<server>/response-selectRows.api?participantId=<appToken>&sql=<sqlQuery>

where:

<appToken> is the unique string associated with a given participant and study folder. The <appToken> is provided to the mobile app when a participant successfully enrolls in the study.
<sqlQuery> is some query on a table or tables in the schema MobileAppResponse. SQL queries passed via the URL should be URL encoded.

For example, the following queries the InitialSurvey table with 'SELECT * FROM InitialSurvey', shown in its URL encoded form:

http://myserver.com/response-executeSQL.api?participantId=ca1f4960d78bcc08295807901a3b74ad&sql=SELECT%20*%20FROM%20InitialSurvey

The following example adds a date filter 'SELECT * FROM InitialSurvey WHERE DueDate = '2017-12-12'', shown in its URL encoded form:

...response-executeSQL.api?
participantId=ca1f4960d78bcc08295807901a3b74ad&
sql=SELECT%20*%20FROM%20InitialSurvey%20WHERE%20DueDate%20=%20%272017-12-12%27

Complex joins and unions can be provided in the sql parameter, for example, the following joins to the InitialSurveyRxGroup table:

SELECT InitialSurvey.ParticipantId,
InitialSurvey.DueDate,
InitialSurvey.PlannedPregnancy,
InitialSurvey.FolicAcid,
InitialSurveyRxGroup.Medication AS MedicationName
FROM InitialSurvey
INNER JOIN InitialSurveyRxGroup ON InitialSurvey.ParticipantId = InitialSurveyRxGroup.ParticipantId

Reference

Learn more in the GitHub documentation here:

LabKey Support

LabKey Support