data-service

latest

false

OpenAPI

important :

Data Service is currently transitioning to Data Fabric, a process during which you may see both names used for different delivery options.

preview

Data Fabric API guide

Query entity records

Queries entity records.

API endpoint

POST BaseURL/EntityService/<Entity>/query

Depending on the cloud platform you are using, use the associated Base URL.

Request headers

Header	Value
Authorization	Bearer `<access_token>`
Content-Type	application/json

Note:

Replace all the values between the angle brackets <...> with their corresponding use-case values.

The <access_token> is the one you received when you authorized the external application. It is available for 1 hour, then you need to generate a new one, or request a refresh token.

Query parameters

Query parameter	Data type	Description	Default value
expansionLevel (optional)	`int32`	Specifies the depth of the related records to be retrieved. The value of this parameter can be 1, 2, or 3.	2

Request body (required)

{
  "selectedFields": [
    "string"
  ],
  "filterGroup": {
    "logicalOperator": 0,
    "queryFilters": [
      {
        "fieldName": "string",
        "operator": "string",
        "value": "string"
      }
    ],
    "filterGroups": [
    ]
  },
  "start": 0,
  "limit": 0,
  "sortOptions": [
    {
      "fieldName": "string",
      "isDescending": true
    }
  ]
}
{
  "selectedFields": [
    "string"
  ],
  "filterGroup": {
    "logicalOperator": 0,
    "queryFilters": [
      {
        "fieldName": "string",
        "operator": "string",
        "value": "string"
      }
    ],
    "filterGroups": [
    ]
  },
  "start": 0,
  "limit": 0,
  "sortOptions": [
    {
      "fieldName": "string",
      "isDescending": true
    }
  ]
}

Request body schemas

The query endpoint request body has the following components:

selectedFields

This is an optional component, of type string. It specifies the list of the fields to be returned for each record of the query. If left empty, or if the query list is empty, all the record fields are returned. Default value is null.

This is a required component, that helps you set the filtering properties for the query. It combines the following properties:

logicalOperator - This is an optional filterGroup property, of type int32. It specifies if all the filters and filter groups apply to the query. Use 0 to apply all the filters and filter groups. Use 1 to apply any of the filters and filter groups. Default value is 0.

queryFilter - This is a required filterGroup property, that helps you define the filtering expression. It has the following properties:

queryFilter property	Data type	Description
fieldName	`string`	Specifies the name of the field that is to be filtered.
operator	`string`	Specifies the filter operator. Supports the following: `contains`, `not contains`, `startswith`, `endswith`, `=`, `!=`, `>`, `<`,`>=`, `<=`, `in`, `not in`. Use the adequate operator for the corresponding field type.
value	`string`	Specifies the filtering value.

filterGroups - This is an optional filterGroup property, in case you need to set another filtering option to the query. It contains all the filterGroup properties listed above.

start

This is an optional component, of type int32. Specifies the number of records to skip before retrieving the records from the query. Can be used together with the limit property to implement pagination. The records are ordered ascendingly by their IDs. To modify the sort order, use the sortOptions property. Default value is 0.

limit

This is an optional component, of type int32. It specifies the maximum number of records to be read from the entity. Can be used together with the start property to implement pagination. The default value is 100, and the maximum value is 1000.

sortOptions

This is an optional component, that helps you sort the queried records by a list of fields. If left empty, the records are sort ascendingly by their IDs. It combines the following properties:

fieldName - This is a required sortOptions property, of type string. It specifies the name of the field used to sort the records. The name must correspond to a valid field, and it is case-sensitive.
isDescending - This is an optional sortOptions property, of type boolean. Set it to true if you want to sort the records descendingly. The default value is false.

Responses

200 OK

{
  "TotalRecordCount": 0,
  "Value": [
    {
      "ClosingDate": "2021-03-04",
      "CreatedBy": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "CreateTime": "2021-03-04T10:21:22.771Z",
      "Id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "Logo": {
        "Name": "string",
        "Path": "string",
        "Size": 0,
        "Type": "string"
      },
      "Name": "string",
      "Nations": 0,
      "OlymipcsVersion": 0,
      "OpeningDate": "2021-03-04",
      "UpdatedBy": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "UpdateTime": "2021-03-04T10:21:22.771Z"
    }
  ]
}
{
  "TotalRecordCount": 0,
  "Value": [
    {
      "ClosingDate": "2021-03-04",
      "CreatedBy": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "CreateTime": "2021-03-04T10:21:22.771Z",
      "Id": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "Logo": {
        "Name": "string",
        "Path": "string",
        "Size": 0,
        "Type": "string"
      },
      "Name": "string",
      "Nations": 0,
      "OlymipcsVersion": 0,
      "OpeningDate": "2021-03-04",
      "UpdatedBy": "3fa85f64-5717-4562-b3fc-2c963f66afa6",
      "UpdateTime": "2021-03-04T10:21:22.771Z"
    }
  ]
}

Response body schema

The query endpoint response body has the following components:

totalRecordCount - The total number of records matching the specified query filters.
value - An array of entity records matching the specified query filters, limited by limit property.

401 Unauthorized

You are not authenticated to access Data Fabric / Data Service. Make sure your Assistant or Robot is connected to a modern folder in your account.

403 Forbidden

You don't have permission to access the entity, field or record or you are using an unsupported robot type. Please contact your administrator for necessary permissions.

Aggregate functions

You can run aggregate calculations over a single entity by adding the aggregates and groupBy properties to the request body. Aggregates return computed values (such as totals or averages), either on their own or alongside grouped field values.

Request body

{
  "selectedFields": [
    "Department"
  ],
  "aggregates": [
    {
      "function": "COUNT",
      "field": "Id",
      "alias": "EmployeeCount"
    }
  ],
  "groupBy": [
    "Department"
  ],
  "filterGroup": {
    "logicalOperator": 0,
    "queryFilters": [
      {
        "fieldName": "Status",
        "operator": "=",
        "value": "Active"
      }
    ]
  },
  "sortOptions": [
    {
      "fieldName": "EmployeeCount",
      "isDescending": true
    }
  ],
  "start": 0,
  "limit": 100
}
{
  "selectedFields": [
    "Department"
  ],
  "aggregates": [
    {
      "function": "COUNT",
      "field": "Id",
      "alias": "EmployeeCount"
    }
  ],
  "groupBy": [
    "Department"
  ],
  "filterGroup": {
    "logicalOperator": 0,
    "queryFilters": [
      {
        "fieldName": "Status",
        "operator": "=",
        "value": "Active"
      }
    ]
  },
  "sortOptions": [
    {
      "fieldName": "EmployeeCount",
      "isDescending": true
    }
  ],
  "start": 0,
  "limit": 100
}

Aggregate request components

In addition to the base query properties, aggregate queries use the following components:

aggregates

This is an optional component, of type array. It specifies the aggregate functions to apply. You can include a maximum of 5 aggregates per query. Each item has the following properties:

aggregates property	Data type	Required	Description
function	`string`	Yes	The aggregate function to apply. Supported values: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`. Case-insensitive.
field	`string`	Yes	The field to aggregate. Must be a field of the queried entity.
alias	`string`	No	The name of the result column. If omitted, it is auto-generated as `{FUNCTION}_{FIELD}` (for example, `COUNT_Id`). Aliases must be unique within the query (case-insensitive), start with a letter, and contain only letters, digits, and underscores.

groupBy

This is an optional component, of type array of string. It specifies the fields to group the results by. You can include a maximum of 5 fields.

When you provide both aggregates and a non-empty selectedFields, groupBy is required, and every field in selectedFields must also appear in groupBy.

Note:

When sorting an aggregate query, set sortOptions.fieldName to an aggregate alias, or to a field that is part of selectedFields/groupBy.

Field type support

Function	Supported field types
COUNT	Any field type.
SUM, AVG, MIN, MAX	Numeric fields only: `INT`, `BIGINT`, `DECIMAL`, `FLOAT`, `REAL`.

Limits and restrictions

A maximum of 5 aggregate functions and 5 groupBy fields per query.
Aggregates cannot be combined with expansions (expansionLevel).
Aggregates are supported on standard (table-based) entities only. They are not supported on virtual entities (external data sources), choice sets, or system entities.
Aggregates cannot be applied to attachment fields or large (multiline max) text fields.
Record-level and field-level permission filters are not applied to aggregated results. Entity-level access is still enforced.

Example: count grouped by a field

Counts employees per department, sorted by count descending.

Request:

POST BaseURL/EntityService/Employees/query

{
  "selectedFields": ["Department"],
  "aggregates": [
    { "function": "COUNT", "field": "Id", "alias": "EmployeeCount" }
  ],
  "groupBy": ["Department"],
  "sortOptions": [
    { "fieldName": "EmployeeCount", "isDescending": true }
  ]
}
POST BaseURL/EntityService/Employees/query

{
  "selectedFields": ["Department"],
  "aggregates": [
    { "function": "COUNT", "field": "Id", "alias": "EmployeeCount" }
  ],
  "groupBy": ["Department"],
  "sortOptions": [
    { "fieldName": "EmployeeCount", "isDescending": true }
  ]
}

Response:

{
  "totalRecordCount": 3,
  "value": [
    { "Department": "Engineering", "EmployeeCount": 150 },
    { "Department": "Sales", "EmployeeCount": 80 },
    { "Department": "HR", "EmployeeCount": 20 }
  ]
}
{
  "totalRecordCount": 3,
  "value": [
    { "Department": "Engineering", "EmployeeCount": 150 },
    { "Department": "Sales", "EmployeeCount": 80 },
    { "Department": "HR", "EmployeeCount": 20 }
  ]
}

Multi-entity queries (joins)

You can combine records from related entities in a single query by adding the joins property to the request body. Joins can be used on their own, or together with aggregates and groupBy.

The main (left) entity is the one in the request URL (<Entity>). Each join adds another entity to the query.

Request body

{
  "selectedFields": [
    "Employees.Name",
    "Departments.Name"
  ],
  "joins": [
    {
      "type": "INNER",
      "entity": "Departments",
      "on": {
        "left": "Employees.DepartmentId",
        "right": "Departments.Id"
      }
    }
  ],
  "sortOptions": [
    {
      "fieldName": "Employees.Name",
      "isDescending": false
    }
  ],
  "start": 0,
  "limit": 100
}
{
  "selectedFields": [
    "Employees.Name",
    "Departments.Name"
  ],
  "joins": [
    {
      "type": "INNER",
      "entity": "Departments",
      "on": {
        "left": "Employees.DepartmentId",
        "right": "Departments.Id"
      }
    }
  ],
  "sortOptions": [
    {
      "fieldName": "Employees.Name",
      "isDescending": false
    }
  ],
  "start": 0,
  "limit": 100
}

Field qualification

When a query spans multiple entities, refer to fields as EntityName.fieldName. You can use the unqualified fieldName only when it is unique across all entities in the query; otherwise it is rejected as ambiguous. Entity names are case-insensitive.

This applies to selectedFields, groupBy, sortOptions.fieldName, the filterGroup filters, aggregates.field, and the join on.left/on.right values. In the response, fields are always returned qualified as EntityName.fieldName.

Join request components

joins

This is an optional component, of type array. It specifies the entities to join. You can include a maximum of 3 joins. Each item has the following properties:

joins property	Data type	Required	Description
type	`string`	Yes	The join type. Supported values: `INNER`, `LEFT`. Case-insensitive. All joins in a single query must be of the same type; mixing `INNER` and `LEFT` is not supported.
entity	`string`	Yes	The name of the entity to join. Each entity can appear only once, and cannot be the main entity (self-joins are not supported).
on	`object`	Yes	The join condition. Contains `left` and `right`.
on.left	`string`	Yes	The join field on the left side, as `fieldName` (if unambiguous) or `EntityName.fieldName`.
on.right	`string`	Yes	The join field on the right side, as `fieldName` (if unambiguous) or `EntityName.fieldName`.

Limits and restrictions

A maximum of 3 joins per query, all of the same type (INNER or LEFT).
Join keys on both sides must have compatible types (numeric with numeric, text with text, date with date).
You cannot join on: system-generated fields, encrypted fields, large (multiline max) text fields, choice set or multiple choice set fields, or file (attachment) fields. Relationship fields can be used as join keys.
The HAVING clause is not supported.
When combining joins with aggregates, the aggregate and groupBy rules described in the Aggregate functions section also apply.

Example: left join with an aggregate

Lists customers with their order count, including customers that have no orders.

Request:

POST BaseURL/EntityService/Customers/query

{
  "selectedFields": ["Customers.Id", "Customers.Name"],
  "aggregates": [
    { "function": "COUNT", "field": "Orders.Id", "alias": "OrderCount" }
  ],
  "joins": [
    {
      "type": "LEFT",
      "entity": "Orders",
      "on": { "left": "Customers.Id", "right": "Orders.CustomerId" }
    }
  ],
  "groupBy": ["Customers.Id", "Customers.Name"],
  "sortOptions": [
    { "fieldName": "OrderCount", "isDescending": true }
  ]
}
POST BaseURL/EntityService/Customers/query

{
  "selectedFields": ["Customers.Id", "Customers.Name"],
  "aggregates": [
    { "function": "COUNT", "field": "Orders.Id", "alias": "OrderCount" }
  ],
  "joins": [
    {
      "type": "LEFT",
      "entity": "Orders",
      "on": { "left": "Customers.Id", "right": "Orders.CustomerId" }
    }
  ],
  "groupBy": ["Customers.Id", "Customers.Name"],
  "sortOptions": [
    { "fieldName": "OrderCount", "isDescending": true }
  ]
}

Response:

{
  "totalRecordCount": 3,
  "value": [
    { "Customers.Id": "C-001", "Customers.Name": "Acme Corp", "OrderCount": 12 },
    { "Customers.Id": "C-002", "Customers.Name": "Globex", "OrderCount": 3 },
    { "Customers.Id": "C-003", "Customers.Name": "Initech", "OrderCount": 0 }
  ]
}
{
  "totalRecordCount": 3,
  "value": [
    { "Customers.Id": "C-001", "Customers.Name": "Acme Corp", "OrderCount": 12 },
    { "Customers.Id": "C-002", "Customers.Name": "Globex", "OrderCount": 3 },
    { "Customers.Id": "C-003", "Customers.Name": "Initech", "OrderCount": 0 }
  ]
}

Was this page helpful?

PREVIOUSGet entity records

NEXTDelete entity record by ID

Data Fabric API guide

API endpoint​

Request headers​

Query parameters​

Request body (required)​

Request body schemas​

selectedFields​

filterGroup​

start​

limit​

sortOptions​

Responses​

200 OK​

Response body schema​

401 Unauthorized​

403 Forbidden​

Aggregate functions​

Request body​

Aggregate request components​

aggregates​

groupBy​

Field type support​

Limits and restrictions​

Example: count grouped by a field​

Multi-entity queries (joins)​

Request body​

Field qualification​

Join request components​

joins​

Limits and restrictions​

Example: left join with an aggregate​

Was this page helpful?

API endpoint

Request headers

Query parameters

Request body (required)

Request body schemas

selectedFields

filterGroup

start

limit

sortOptions

Responses

200 OK

Response body schema

401 Unauthorized

403 Forbidden

Aggregate functions

Request body

Aggregate request components

aggregates

groupBy

Field type support

Limits and restrictions

Example: count grouped by a field

Multi-entity queries (joins)

Request body

Field qualification

Join request components

joins

Limits and restrictions

Example: left join with an aggregate