Search
K

Getting started

Querying data

Learn how to query and filter data from our APIs using request parameters and query expressions.

All API endpoints which can be used to query a set of items, support load options as query parameters and return a load result as response.

In this article, the endpoint /v1/leases/assets is used as an example but the parameters are the same for all endpoints.

Load result

An example of a load result can look like this when all properties are used.

{
    "data": [
        {
            "key": "inLease",
            "items": null,
            "count": 84871
        },
        {
            "key": "terminated",
            "items": null,
            "count": 45525
        }
    ],
    "totalCount": 130396,
    "groupCount": 2,
    "summary": [
        "2022-10-01"
    ]
}

The data property is the only one which is always returned from the API. The other three properties are only delivered when explicitly requested through the load options,

The data block contains the requested data, in this example the data is based on all assets that the current user is allowed to see. The example is a request grouped by contract status, that´s why you can´t see any specific asset data.

Load options

These are the properties which can be sent to the api to tell which data is needed.

Hint: The responses are shortened here to provide better readability.

Count

When calling the API with URL parameter isCountQuery=true, no data will be returned but only a number representing the total count of items.

{
    "data": null,
    "totalCount": 130396
}

Skip and Take

Always querying all data is possible, but is not guaranteed to provide good performance. Thats why paging the data is strongly recommended.

This can be done via passing in skip and take parameters, e.g. skip=100&take=3 will load the 101st to 103rd item and produce an output comparable to this:

{
    "data": [
        {
            "assetId": 3544211,
            "leaseScheduleId": 2538861,
            "contractStatus": "inLease",
            [...]
        },
        {
            "assetId": 3544221,
            "leaseScheduleId": 2538861,
            "contractStatus": "inLease",
            [...]
        },
        {
            "assetId": 3544231,
            "leaseScheduleId": 2538861,
            "contractStatus": "inLease",
            [...]
        }
    ]
}

TotalCount

By providing the requireTotalCount=true property, the load result will add a property named totalCount containg the entire count of the data set on the server. Note that calling requireTotalCount=true&skip=100&take=3 will load 3 items in the data property but will not produce 3 as a totalCount but the total count that is on the server.

{
    "data": [
        {
            "assetId": 3544211,
            "leaseScheduleId": 2538861,
            "contractStatus": "inLease",
            [...]
        },
        {
            "assetId": 3544221,
            "leaseScheduleId": 2538861,
            "contractStatus": "inLease",
            [...]
        },
        [...]
    ],
    "totalCount": 130396
}

If 100 of 130 assets are in lease, the request filter=["contractStatus","=","inLease"]&take=10&requireTotalCount=true will return 10 items and a totalCount of 100. So the totalCount is always relative to the filter.

Sort

The data can also be sorted by a specific property by passing in the sort property. An example value for assets would be sort=[{"selector":"leaseStartDate"}] which will sort the data by leaseStartDate ascending. By providing the desc property, the sort order can ce inverted sort=[{"selector":"leaseStartDate","desc":true}].

The data can be sorted by more than one criteria, e.g. sort=[{"selector":"leaseStartDate","desc":true}, {"selector":"plannedLeaseEndDate","desc":false}] will sort the data by leaseStartDate descending and if multiple assets share the same leaseStartDate, the data will be sorted by plannedLeaseEndDate ascending.

Select

The select property defines, which properties of the target data should be returned. When omitting this property, all propeties of the target type will be returned. When requesting e.g. select=["assetId","leaseScheduleId","leaseStartDate"] the output will look like this

{
    "data": [
        {
            "assetId": 30313871,
            "leaseScheduleId": 2564330,
            "leaseStartDate": "2015-10-01"
        },
        {
            "assetId": 30580481,
            "leaseScheduleId": 2564330,
            "leaseStartDate": "2015-10-01"
        },
        {
            "assetId": 30605921,
            "leaseScheduleId": 2564330,
            "leaseStartDate": "2015-10-01"
        }
    ]
}

Note that this response is not shortened, but the actual return value.

Filter

The data can be filtered by providing the filterproperty.

The following filter operations are supported:

  • Equals is represented by =, e.g. filter=["assetId","=",1111]
  • Not equals is by passing <>, e.g. filter=["assetId","<>",1111]
  • Greather than, greather than or equals, less than and less than or equals are represented by >, >=, < and <=, e.g. filter=["assetId","<=",1111]
  • Empty values can be filtered by comparing to null filter=["assetId","<>",null]
  • For string properties, filters can also be applied by using the special operations startswith, endswith, contains and notcontains, e.g. filter=["location","startswith","London"]

Depending on the filter property data type, the compare value must be formatted respectively.

  • Empty value is passed as filter=["assetId","=",null]
  • Integers are passed as number filter=["assetId","=",1111]
  • Strings with quotes filter=["description","=","my asset"]
  • Date values with quotes filter=["leaseStartDate","=","2013-01-01"]
  • Datetime values with quotes filter=["leaseStartDate","=","2013-01-01"]
  • Currencies as number with .as separator filter=["periodicalPrice","=",100.66]

The filters can be nested, e.g. filter=[["contractStatus","=","inLease"],"and",["category","=","hardware"]] lists the assets with contractStatus "inLease" and category "hardware". The possible chaining operator are and and or. There is not limit to nesting the filter operations but performance may decrease when applying hundreds of filter criterias.

In case the response object contains a nested object, typically the customerContext property, the filter can be applied by separating the object and field with a dot, e.g. filter=["customerContext.customerNumber","=",292189]

Group

By providing the group parameter, result can be grouped on server side and loaded in groups. The parameter group=[{"selector":"contractStatus","isExpanded":false}] will not load asset data but instead return group items:

{
    "data": [
        {
            "key": "inLease",
            "items": null,
            "count": 2601
        },
        {
            "key": "terminated",
            "items": null,
            "count": 10
        }
    ],
    "totalCount": 2611
}

These group items always have the same structure: A key which represents the grouped property, the items which include that property and count as the number of items in that group.

By passing group=[{"selector":"contractStatus","desc":true}], the sorting order of the groups (sorted by key) can be inverted.

Passing "isExpanded":false will set the items to null and returns just the groups and their counts.

You can also pass multiple groups, for example group=[{"selector":"contractStatus","isExpanded":false},{"selector":"offLeaseType","isExpanded":false}] which will returnd nested groups:

{
    "data": [
        {
            "key": "inLease",
            "items": [
                {
                    "key": null,
                    "items": null,
                    "count": 84871
                }
            ]
        },
        {
            "key": "terminated",
            "items": [
                {
                    "key": "buyout",
                    "items": null,
                    "count": 569
                },
                {
                    "key": "derecognition",
                    "items": null,
                    "count": 6244
                },
                {
                    "key": "followUpLease",
                    "items": null,
                    "count": 406
                },
                {
                    "key": "stock",
                    "items": null,
                    "count": 38306
                }
            ]
        }
    ],
    "totalCount": 130396
}

GroupCount

When including requireGroupCount=true in a grouped query, the load result will have an additional property groupCount that indicated the number of groups in the load result:

{
    "data": [
        {
            "key": "inLease",
            "items": null,
            "count": 2601
        },
        {
            "key": "terminated",
            "items": null,
            "count": 10
        }
    ],
    "totalCount": 2611,
    "groupCount": 2
}

GroupSummary

When providing the groupSummary parameter to a grouped query, a summary will be created for every group. For example the request group=[{"selector":"contractStatus","isExpanded":false}]&groupSummary=[{"selector":"leaseStartDate","summaryType":"max"}] will group by contractStatus and return the maximum leaseStartDate for every group:

{
    "data": [
        {
            "key": "inLease",
            "items": null,
            "count": 84871,
            "summary": [
                "2022-10-01"
            ]
        },
        {
            "key": "terminated",
            "items": null,
            "count": 45525,
            "summary": [
                "2021-02-01"
            ]
        }
    ],
    "totalCount": 130396
}

The supported operations are sum, min, max, avg, and count.

TotalSummary

The totalSummary parameter defines an aggregate function on all data, meaning that totalSummary=[{"selector":"leaseStartDate","summaryType":"min"},{"selector":"leaseStartDate","summaryType":"max"}] will produce an output containing all data and also included a summary property containing the minimum and maximum leaseStartDate of all assets.

{
    "data": [
        {
            "assetId": 3544211,
            "category": "hardware",
            "leaseScheduleId": 2538861,
            [...]
        },
        [...]
    ],
    "totalCount": 130396,
    "summary": [
        "2012-08-01",
        "2022-10-01"
    ]
}

The supported operations are sum, min, max, avg, and count.