A Guide to Information Access

Retrieve Data syntax

Retrieve Data requests are executed using a SQL-like syntax encoded in JSON with the following clauses:

select [Required] contains an array of properties that define which data elements to include from the Data Dictionary.
from [Optional] contains properties and objects that define the sources contributing to and span of time constraining the returned data.
where [Optional] contains an array with an object that defines a list of data elements and applies filters to each element.
groupBy [Optional] contains an array with an object that defines a list of columns for grouping the data elements in the request.
sortBy [Optional] contains an array with an object that defines sorting behavior for each data element in the request.
options [Optional] contains an object with properties that allow you to further customize the query.

Hello, world example

In this example, a Healthcare manager requests data about her Registered Nurse employees.

The request:

retrieves the full name, job, organization, and hire date for employees using Data Dictionary keys
defines the data view as EMP for employee, which is similar to an Employee Dataview
selects the set of employees using a Hyperfind query
establishes a date range for the requested data in the form of a symbolic period
filters the results to include only employees with jobs that start with "RN" (for Registered Nurses)
limits the response to only the first 100 results

Example request

{
  "select": [
    {"key": "EMP_COMMON_FULL_NAME"},
    {"key": "EMP_COMMON_PRIMARY_JOB"},
    {"key": "EMP_COMMON_PRIMARY_ORG"},
    {"key": "PEOPLE_HIRE_DATE"}],
  "from": {
    "view": "EMP",
    "employeeSet": {
      "hyperfind": {
        "qualifier": "All Home"
      },
      "dateRange": {
         "symbolicPeriod": {
          "qualifier": "Current_Payperiod"
        }
      }
    }
  },
  "where": [
    {
      "key": "EMP_COMMON_PRIMARY_JOB",
      "operator": "STARTS_WITH",
      "values": [
        "RN"
      ]
    }
  ],
  "count": 100
}

Example response

This example response omits the metadata object and displays only the first employee's results. For more information about metadata, refer to the Cache, count, and index topic.

{
  "data": {
    "key": {
      "ROOT": "-1"
    },
    "coreEntityKey": {},
    "attributes": [],
    "children": [
      {
        "key": {
          "PEOPLE": "408"
        },
        "coreEntityKey": {
          "EMP": {
            "id": "408"
          }
        },
        "attributes": [
          {
            "key": "EMP_COMMON_PRIMARY_ORG",
            "rawValue": "Organization/Metropolitan Hospital/Clin Op/WMNB/Labor/Nurse",
            "value": "Organization/Metropolitan Hospital/Clin Op/WMNB/Labor/Nurse"
          },
          {
            "key": "PEOPLE_HIRE_DATE",
            "rawValue": "2016-08-14",
            "value": "8/14/2016"
          },
          {
            "key": "EMP_COMMON_FULL_NAME",
            "rawValue": "Williams, Lynne",
            "value": "Williams, Lynne"
          },
          {
            "key": "EMP_COMMON_PRIMARY_JOB",
            "rawValue": "RN",
            "value": "RN"
          }
        ],
        "children": [],
        "summaryListDisplay": [],
        "rootEntity": "PEOPLE",
        "customProperties": {}
      },
      ...
    ],
    "summaryListDisplay": [],
    "rootEntity": "ROOT",
    "customProperties": {}
  }
}