On Key Query Language
For most GET calls, you can specify one or more optional query parameters on the request URI using the On Key Query Language (OKQL). OKQL has powerful capabilities to limit, filter, page, sort, join and parameterize the data that is returned in API responses for resources.
Query Fragments
An OKQL query consists out of the following fragments:
| Fragment | Type | Description | Example |
|---|---|---|---|
$select |
string | A comma separated list of the data properties on the resource to return. | $select=id,code,description |
$top |
integer | The number of items to return in the response. | $top=20 |
$skip |
integer | The number of items to skip. Use a combination of $top and $skip to page through data. |
|
$orderby |
string | A comma separated list of the data properties on the resource to order by. | $orderby=id DESC, code ASC |
$filter |
string | A complex query filter to apply on the resource to limit the response data being returned. | $filter=id gt 0 and code startswith 'Code'. |
$param |
string | A comma separated list of key/value pairs to parameterise $filter expressions. |
$param=@code:'JUMA' |
Given the above, the following Asset Operation Management API returns an ordered list of the first 10 meters that were created within the last year:
curl -v -X GET https://{server}/api/tenants/{client}/{connection}/modules/aom/meters?$top=10&$filter=datediff(utcnow(), createdOn) le timespan'365.00:00:00'&$skip=0&$select=id,code,isPaused$orderby=id desc \
-H "Content-Type:application/vnd.onkey.entity+json" \
Case Insensitive
OKQL is a case-insensitive language. Any language keyword can be specified using mixed casing as illustrated in the following equivalent filter segments:
$filter=datediff(utcnow(), createdOn) le timespan'365.00:00:00'
$Filter=DateDiff(UTCNOW(), createdOn) LE TiMeSpaN'365.00:00:00'
The case-insensitivity nature of OKQL only applies to the grammar elements of the language and not to how data is compared using the string comparison operators. The following filter segments are therefore not equivalent:
$filter=createdByUserFullName eq 'Luke'
$filter=createdByUserFullName eq 'LUKE'
Property Identifiers
OKQL supports referencing and exploring the properties and relationships between resources using an unique property identifier syntax. Property identifiers are also used as part of request and response bodies.
Property identifiers are expressed using either Property Name or Property Path syntax.
Note
Please familiarize yourself with property identifiers before continuing any further.
Selecting data
By default all GET API calls will return the default properties for a specific resource. To selective choose which of the data properties to include in the response, add a $select fragment to the query string.
A $select fragment consists out of a comma separated list of property identifiers that can be identified using either property path or property name syntax.
?$select=id,code,depreciationPercentagePerYear
?$select=Asset->Id,Asset->Code,Asset->Depreciation_PercentagePerYear
Paging data
By default all GET collection API calls return a list of records. To page through the response data, add the $top and $skip fragments to the query string.
| Paging scenario | Paging fragment |
|---|---|
| First page of 20 records | ?$top=20 |
| Second page of 20 records | ?$top=20&$skip=20 |
The response body will also include next and previous hypermedia links. These hypermedia links make it easy to page through the list of records using the specified $top parameter as the default page size. If no paging parameters are specified, the system will use its own default page size to return a paged set of records.
Ordering data
By default all GET API calls support the ordering of the data records returned for a specific resource. To specify the order, add a $orderby fragment to the query string.
A $orderby fragment consists out of a comma separated list of property identifiers with an ASC or DESC specifier.
?$orderby=id ASC, depreciationPercentagePerYear DESC
?$orderby=Asset->Id ASC,Asset->Depreciation_PercentagePerYear DESC
Filtering data
By default all GET API calls support the filtering of the data records returned for a specific resource. To specify the filter, add a $filter fragment to the query string. A $filter fragment consists out of list of expressions that return either true or false.
Expressions can be nested and grouped using ( and ) brackets and support the logical AND, OR, NOT and IN operators.
?$filter=code startswith 'PUMP' AND developmentStatus eq 'Accepted'
Tip
Refer to the special characters section for information on how to write filter expressions with string literals that include special characters.
Parameterising data
By default all GET API calls can make use of parameters to enable a single custom resource query to be executed with different parameter values. Instead of creating different resource queries for different combination of parameters, create and save a single parameterized query that can be executed with different run-time parameter values.
To specify the parameters, add a $param fragment to the query string. A $param fragment consists out of a comma separated list of key/value pairs. Each key is prefixed with @ and the value can be any data type available for use in $filter expressions.
?$param=@code:'PUMP',@status:'Accepted'
Parameters can be referenced in $filter expressions as follows:
?$param=@code:'PUMP',@status:'Accepted'&$select=id,code,developementStatus&$filter=code startswith @code and developmentStatus eq @status
In addition to $param segment, $filter expressions also support inline parameter definitions. inline parameter definitions can be made within the filter expression itself. Here is the same query written using inline parameters:
?$select=id,code,developementStatus&$filter=code startswith @code:'PUMP' and developmentStatus eq @status:'Accepted'
Tip
Refer to the special characters section for information on how to use parameter values that include special characters.