--- title: "Querying data" url: "https://developer.tesma.com/getting-started/querying-data" image: "https://developer.tesma.com/_og/d/c_Ocean.takumi,title_Querying+data,props_eyJ0aGVtZSI6eyJtb2RlIjoibGlnaHQiLCJjb2xvcnMiOnsicHJpbWFyeSI6IiM0Mjg1ZjQifX19,p_Ii9nZXR0aW5nLXN0YXJ0ZWQvcXVlcnlpbmctZGF0YSI,s_Z1e_SNNQNMSz3G-3.png" --- # 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](#load-result) An example of a load result can look like this when all properties are used. ```json { "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](#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](#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. ```json { "data": null, "totalCount": 130396 } ``` ### [Skip and Take](#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: ```json { "data": [ { "assetId": 3544211, "leaseScheduleId": 2538861, "contractStatus": "inLease", [...] }, { "assetId": 3544221, "leaseScheduleId": 2538861, "contractStatus": "inLease", [...] }, { "assetId": 3544231, "leaseScheduleId": 2538861, "contractStatus": "inLease", [...] } ] } ``` ### [TotalCount](#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. ```json { "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](#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](#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 ```json { "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](#filter) The data can be filtered by providing the `filter`property. 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](#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: ```json { "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: ```json { "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](#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: ```json { "data": [ { "key": "inLease", "items": null, "count": 2601 }, { "key": "terminated", "items": null, "count": 10 } ], "totalCount": 2611, "groupCount": 2 } ``` ### [GroupSummary](#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: ```json { "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](#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. ```json { "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`.