Understanding OData

OData (Open Data Protocol) is a widely used and standardized protocol for building and consuming RESTful APIs. OData simplifies data sharing and integration by providing a common set of conventions and best practices for working with data over the web.

In the context of the MindKey Connector API, OData is the foundation of the API, enabling users to query and manipulate data efficiently. Let's explore the basic concepts of OData using an example entity, "Employee," to illustrate key OData principles.

URL Format

The MindKey Connector API uses a key-as-segment URL format for accessing individual resources. This means you append the entity key directly as a URL path segment rather than using parentheses.

Correct format (key as segment):

GET https://connector.mindkey.com/odata/v2/Employee/EMP001

Not supported (parenthesis format):

GET https://connector.mindkey.com/odata/v2/Employee('EMP001')

This applies to all entities across the API. When constructing URLs for single-resource access, always use the segment-based format.

OData Conventions

OData uses a set of conventions for query options, filtering, and more. The conventions include query parameters like $filter, $select, $expand, $orderby, $top, $skip, and $count, making it easier to shape the data you retrieve. Understanding these conventions is key to using OData effectively with the MindKey Connector API.

In the following sections, we'll explore how to utilize these OData conventions to make specific queries and interact with the API effectively.

Example Entity: Employee

The "Employee" entity in the MindKey Connector API represents individual employee records. It contains a wide range of properties and relationships, making it an ideal example to showcase OData features. Here's a glimpse of the properties within the "Employee" entity:

employeeId: A unique identifier for the employee.
nameFirstName, nameMiddleName, nameLastName: Employee's name components.
email, mobilePhoneNumberFullPhoneNumber: Contact information.
birthDate, gender: Personal information.
position: A object representing the employee's current position.

Querying Data

OData allows you to retrieve data from the "Employee" entity using HTTP GET requests. For example, to retrieve a list of employees, you can make a request like this:

GET https://connector.mindkey.com/odata/v2/Employee

Using the `$select` Query Option

OData's $select query option allows you to specify which fields you want to retrieve from an entity. This feature is particularly useful when you're interested in only a subset of the available fields, reducing the amount of data transferred and improving query efficiency. In the context of the MindKey Connector API, let's explore how to use the $select query option using the "Employee" entity as an example.

Request:

GET https://connector.mindkey.com/odata/v2/Employee?$select=nameFirstName,nameLastName

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$select=nameFirstName,nameLastName specifies the query option to select the fields you're interested in.

Response:

{
    "value": [
        {
            "nameFirstName": "John",
            "nameLastName": "Doe"
        },
        {
            "nameFirstName": "Jane",
            "nameLastName": "Smith"
        },
        {
            "nameFirstName": "Michael",
            "nameLastName": "Johnson"
        },
        // ... (other employee records)
    ]
}

Using the `$filter` Query Option

OData's $filter query option allows you to filter data based on specific conditions. This feature is invaluable when you want to retrieve only the data that meets specific criteria. Here's how to use the $filter option in your API requests:

Request:

GET https://connector.mindkey.com/odata/v2/Employee?$filter=gender eq 2

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$filter=gender eq 2 specifies the query option to filter employees based on the condition that the "gender" property must be equal to 2 (Female)'.

Response:

{
    "value": [
        {
            "employeeId": "1",
            "nameFirstName": "John",
            "nameLastName": "Doe"
            // ... (other employee properties)
        },
        {
            "employeeId": "2",
            "nameFirstName": "Jane",
            "nameLastName": "Smith"
            // ... (other employee properties)
        },
        {
            "employeeId": "3",
            "nameFirstName": "Michael",
            "nameLastName": "Johnson"
            // ... (other employee properties)
        },
        // ... (other employee records)
    ]
}

Supported Comparison Operators

OData supports a range of comparison operators, including:

eq (equal)
ne (not equal)
gt (greater than)
lt (less than)
ge (greater than or equal)
le (less than or equal)

You can combine operators to create more complex filters as needed.

GET https://connector.mindkey.com/odata/v2/Employee?$filter=gender eq 2 and birthDate ge 1990-01-01

This request, for example, retrieves female employees born on or after January 1, 1990.

Using the $filter query option, you can precisely tailor your API requests to retrieve data that meets your specific criteria, making data integration and automation tasks more efficient.

Using the `$expand` Query Option

OData's $expand query option allows you to retrieve related entities, expanding the depth and richness of the data you retrieve. This is particularly useful when you want to retrieve related information in a single API call. Here's how to use the $expand option in your API requests:

Request for a single employee:

GET https://connector.mindkey.com/odata/v2/Employee/1?$expand=position

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee/1 is the URL for the "Employee" entity with employee id 1.
?$expand=position specifies the query option to retrieve related "position" entity.

Response:

{
    "employeeId": "1",
    "nameFirstName": "John",
    "nameLastName": "Doe",
    // ... (other employee properties),
    "position": {
        "positionId": "ABCD",
        "title": "Developer",
        // ... (other position properties),
    }
}

Request for a list of employees:

GET https://connector.mindkey.com/odata/v2/Employee?$expand=position

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$expand=position specifies the query option to retrieve related "position" entity.

Response:

{
    "value": [
        {
            "employeeId": "1",
            "nameFirstName": "John",
            "nameLastName": "Doe",
            // ... (other employee properties)
            "position": {
                "positionId": "ABCD",
                "title": "Developer",
                // ... (other position properties),
            }
        },
        {
            "employeeId": "2",
            "nameFirstName": "Jane",
            "nameLastName": "Smith",
            // ... (other employee properties)
            "position": {
                "positionId": "EFGH",
                "title": "Product Manager",
                // ... (other position properties),
            }
        }
        // ... (other employee records)
    ]
}

Using $expand with $select and $filter

You can enhance the precision and efficiency of your data retrieval by using $select and $filter in conjunction with the $expand operation. Here's an example of how to use $select and $expand in combination:

GET https://connector.mindkey.com/odata/v2/Employee
    ?$select=nameFirstName,nameLastName
    &$expand=position($select=title)

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$select=nameFirstName,nameLastName specifies the query option to select the fields you're interested in.
&$expand=position($select=title) specifies the query option to retrieve the related "position" entity, but only the "title" field.

Response:

{
    "value": [
        {
            "nameFirstName": "John",
            "nameLastName": "Doe",
            "position": {
                "title": "Developer"
            }
        },
        {
            "nameFirstName": "Jane",
            "nameLastName": "Smith",
            "position": {
                "title": "Product Manager"
            }
        }
        // ... (other employee records)
    ]
}

The $expand query option enables you to retrieve related entities in a single API request, streamlining your data retrieval process.

By leveraging the $expand operation along with $select and $filter, you can tailor your API requests to retrieve precisely the data you need, even when accessing related properties, resulting in more effective data extraction and integration processes.

Nested Query Options on `$expand`

When expanding related entities, you can apply $filter, $select, $orderby, $top, and $skip directly within the $expand clause to control the expanded data. This is useful when a related collection is large or you only need a subset of its properties.

Request:

GET https://connector.mindkey.com/odata/v2/Employee
    ?$select=employeeId,nameFirstName,nameLastName
    &$expand=equipments($filter=active eq true;$select=equipmentId,description;$orderby=description)

In this example:

$expand=equipments(...) retrieves the related equipment collection.
$filter=active eq true inside the expansion filters to only active equipment.
$select=equipmentId,description limits which properties are returned for each equipment record.
$orderby=description sorts the equipment within each employee.

Note that nested query options within $expand are separated by semicolons (;), not ampersands (&).

Using the `$orderby` Query Option

The $orderby query option allows you to sort the results by one or more properties. You can sort in ascending (default) or descending order.

Request:

GET https://connector.mindkey.com/odata/v2/Employee?$orderby=nameLastName

This returns employees sorted alphabetically by last name in ascending order.

Descending order:

GET https://connector.mindkey.com/odata/v2/Employee?$orderby=nameLastName desc

Multiple sort criteria:

GET https://connector.mindkey.com/odata/v2/Employee?$orderby=nameLastName asc,nameFirstName asc

This sorts employees by last name first, then by first name within matching last names.

Using `$count`

The $count query option requests the total number of records matching the query. Include $count=true as a query parameter to receive the count alongside the data.

Request:

GET https://connector.mindkey.com/odata/v2/Employee?$filter=active eq true&$count=true

Response:

{
    "@odata.context": "https://connector.mindkey.com/odata/v2/$metadata#Employee",
    "@odata.count": 245,
    "value": [
        {
            "employeeId": "1",
            "nameFirstName": "John",
            "nameLastName": "Doe"
            // ... (other employee properties)
        }
        // ... (other employee records)
    ]
}

The count reflects the total number of matching records, not the number of records returned in the current page.

warning

The /$count URL segment (e.g., /odata/v2/Employee/$count) is not supported. Always use the $count=true query parameter instead.

Pagination

The MindKey Connector API uses server-side pagination. By default, the API returns a maximum of 3,000 records per request. When a result set exceeds this limit, the response includes an @odata.nextLink property containing the URL to retrieve the next page.

Response with pagination:

{
    "@odata.context": "https://connector.mindkey.com/odata/v2/$metadata#Employee",
    "value": [
        // ... (up to 3,000 records)
    ],
    "@odata.nextLink": "https://connector.mindkey.com/odata/v2/Employee?$skip=3000"
}

To retrieve all records, follow the @odata.nextLink URL in subsequent requests until no @odata.nextLink is present in the response.

Controlling Page Size with `$top` and `$skip`

You can control pagination manually using $top and $skip:

$top limits the number of records returned.
$skip skips a given number of records from the beginning of the result set.

Request the first 100 employees:

GET https://connector.mindkey.com/odata/v2/Employee?$top=100

Request the next 100 employees:

GET https://connector.mindkey.com/odata/v2/Employee?$top=100&$skip=100

tip

When extracting large datasets, follow the @odata.nextLink rather than manually calculating $skip values. This ensures correct handling of the pagination cursor and avoids issues with data that may change between requests.

Filtering by Date

Many entities in the MindKey Connector API include date properties such as birthDate, hireDate, and terminationDate. OData supports filtering on dates using the YYYY-MM-DD format.

Filter employees hired after a specific date:

GET https://connector.mindkey.com/odata/v2/Employee?$filter=hireDate ge 2024-01-01

Filter employees hired within a date range:

GET https://connector.mindkey.com/odata/v2/Employee?$filter=hireDate ge 2024-01-01 and hireDate lt 2025-01-01

Filter on null dates (e.g., employees without a termination date):

GET https://connector.mindkey.com/odata/v2/Employee?$filter=terminationDate eq null

Date comparison operators work the same as with other types: eq, ne, gt, lt, ge, le.

Examples

Query active, female employees

GET https://connector.mindkey.com/odata/v2/Employee?$filter=gender eq 2 and active eq true

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$filter=gender eq 2 and active eq true is the $filter query option, specifying the conditions for retrieving female employees (where "gender" is 2) who are also active (where "Active" is true).

Response:

{
    "value": [
        {
            "employeeId": "1",
            "nameFirstName": "Jane",
            "nameLastName": "Smith",
            "gender": 2,
            "active": true
            // ... (other employee properties)
        },
        {
            "employeeId": "2",
            "nameFirstName": "Emily",
            "nameLastName": "Brown",
            "gender": 2,
            "active": true
            // ... (other employee properties)
        },
        {
            "employeeId": "3",
            "nameFirstName": "Grace",
            "nameLastName": "Williams",
            "gender": 2,
            "active": true
            // ... (other employee properties)
        },
        // ... (other active female employees)
    ]
}

Query employees with a position

GET https://connector.mindkey.com/odata/v2/Employee
    ?$filter=position ne null
    &$select=nameFirstName,nameLastName

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$filter=position ne null is the $filter query option, which filters employees based on the condition that they have at a position.
&$select=nameFirstName,nameLastName is the $select query option, specifying that you want to retrieve only the "nameFirstName" and "nameLastName" fields for the selected employees.

Response:

{
    "value": [
        {
            "nameFirstName": "John",
            "nameLastName": "Doe"
        },
        {
            "nameFirstName": "Jane",
            "nameLastName": "Smith"
        },
        {
            "nameFirstName": "Michael",
            "nameLastName": "Johnson"
        },
        // ... (other employee records with an equipment)
    ]
}

Query employees with a equipment

GET https://connector.mindkey.com/odata/v2/Employee
    ?$filter=position/title eq 'Projektkonsulent'
    &$select=nameFirstName,nameLastName
    &$expand=position($select=positionId,title)

In this example:

GET is the HTTP method used to retrieve data.
https://connector.mindkey.com/odata/v2/Employee is the base URL for the "Employee" entity.
?$filter=position/title eq 'Projektkonsulent' is the $filter query option, filtering employees based on the condition that they have a position with the title equal to 'Projektkonsulent'.
&$select=nameFirstName,nameLastName is the $select query option, specifying that you want to retrieve the "nameFirstName" and "nameLastName" fields.
&$expand=position($select=positionId,title) is the $expand query option, indicating that you want to retrieve related position information (positionId and title) for employees who meet the filter criteria.

Response:

{
    "value": [
        {
            "nameFirstName": "John",
            "nameLastName": "Doe",
            "position": {
                "positionId": "100422",
                "title": "Projektkonsulent"
            }
        },
        {
            "nameFirstName": "Jane",
            "nameLastName": "Smith",
            "position": {
                "positionId": "100119",
                "title": "Projektkonsulent"
            }
        },
        {
            "nameFirstName": "Michael",
            "nameLastName": "Johnson",
            "position": {
                "positionId": "100400",
                "title": "Projektkonsulent"
            }
        },
        // ... (other employee records with telephone equipment)
    ]
}

Learning More About OData

If you're interested in diving deeper into OData (Open Data Protocol) and want to explore the official specifications and resources, you can find valuable information at the following sources:

OData Official Website: The OData official website is a comprehensive resource for understanding OData and staying updated on the latest developments. It provides a wealth of information, tutorials, and community resources to help you master OData.
OASIS OData Version 4.01 Specification: For in-depth technical details and the official specification of OData Version 4.01, you can refer to the OASIS OData Version 4.01 Specification. This document serves as the authoritative source for OData's protocol and data format standards.

Exploring these resources will enhance your understanding of OData and help you make the most of the MindKey Connector API's capabilities. Whether you're a seasoned developer or new to OData, these sources provide valuable insights and guidance for leveraging this powerful protocol effectively.

Understanding OData

URL Format​

OData Conventions​

Example Entity: Employee​

Querying Data​

Using the $select Query Option​

Using the $filter Query Option​

Supported Comparison Operators​

Using the $expand Query Option​

Using $expand with $select and $filter​

Nested Query Options on $expand​

Using the $orderby Query Option​

Using $count​

Pagination​

Controlling Page Size with $top and $skip​

Filtering by Date​

Examples​

Query active, female employees​

Query employees with a position​

Query employees with a equipment​

Learning More About OData​

URL Format

OData Conventions

Example Entity: Employee

Querying Data

Using the `$select` Query Option

Using the `$filter` Query Option

Supported Comparison Operators

Using the `$expand` Query Option

Using $expand with $select and $filter

Nested Query Options on `$expand`

Using the `$orderby` Query Option

Using `$count`

Pagination

Controlling Page Size with `$top` and `$skip`

Filtering by Date

Examples

Query active, female employees

Query employees with a position

Query employees with a equipment

Learning More About OData