Request options
Copy
The following OData query options are used to apply additional conditions on a returned data set.
$filter
The $filter option applies conditions to the returned data set. The $filter expression is translated to AML using the following AML features:
- The condition attribute of property element
- The <or> and <and> elements
- The where attribute of an Item element.
Table 2 describes how each element of the $filter option is translated to AML.
Table 1: Comparison Operators
| Comparison Operators | |||
| Operator | Description | Example | AML |
| eq | Equal | name eq “Part Name” | condition=“eq” Note: OData equal is case sensitive. |
| ne | Not equal | name ne “Part Name” | condition=“ne” |
| gt | Greater than | price gt 20 | condition=“gt” |
| ge | Greater than or equal | price ge 10 | condition=“ge” |
| lt | Less than | price lt 20 | condition=“lt” |
| le | Less than or equal | price le 20 | condition=“le” |
Table 2: Logical Operators
| Logical Operators | |||
| Operator | Description | Example | AML element |
| and | Logical and | price le 200 and price gt 3.5 | <and> |
| or | Logical or | price le 3.5 or price gt 200 | <or> |
| not | Logical negation | not endswith(description, ‘milk’) | <not> |
The $filter expression can contain a call to a built-in function. Table 4 shows how to translate a built-in function to AML.
Table 3: String Functions
| String Functions | ||
| Function | Example | AML |
| contains | contains(company_name,'freds’) | <company_name condition=“like”> %freds% </company_name> |
| endswith | endswith(company_name, ‘freds’) | <company_name condition=“like”> %freds </company_name> |
| startswith | startswith(company_name,'Alfr’) | <company_name condition=“like”> Alfr% |
Null values
OData uses the special value null to indicate that a property doesn’t have value. Equality to null is translated using the condition “is null.”
GET http://host/server/odata/Part?$filter=name eq null
Inequality is translated using the condition “is not null”:
GET http://host/server/odata/Part?$filter=name ne null
You can use the previous condition with negation:
GET http://host/server/odata/Part?$filter=not name eq null
Filtering by Related Item Properties
The OData interface supports filtering using a property associated with the related item:
GET http://host/server/odata/Part?$filter=created_by_id/name eq ‘Admin’
In Aras Innovator, item type names may contain spaces. This conflicts with the filter expression syntax where a space character is used as a delimiter. In order to resolve the conflict, names containing a space character should be enclosed by square brackets in filter expressions, as shown:
GET http://host/server/odata/Part?$filter=[Part BOM]/quantity eq 2
$expand
The $expand option specifies which Item property or relationship must be included in a response. If you do not specify the $expand option, Item properties are included in the response as an Item with a single id property. Relationships are not included at all.
Expanding Item property:
GET http://host/server/odata/Part?$expand=created_by_id
Expanding relationship:
GET http://host/server/odata/Part(‘048649CAF3F04D75928B1ECD702E23BC’)?$expand=Part_CAD
Response:
{
"@odata.context": “http://host/server/odata/$metadata#Part”,
“id": "…",
“Part_CAD":
[
{ “id": "…", … remaining Part CAD item propertires },
{ “id": "…", … remaining Part CAD item propertires },
… remainig Part CAD items
],
… remaining Part item properties
}
Expanding property of a related item:
GET http://host/server/odata/Part(‘048649CAF3F04D75928B1ECD702E23BC’)?$expand=Part_CAD($expand=related_id)
Response:
{
"@odata.context": “http://host/server/odata/$metadata#Part”,
“id": “048649CAF3F04D75928B1ECD702E23BC”,
“Part_CAD":
[
{
“related_id":
{
“item_number": “CAD-1",
… remaining CAD item properties
},
… remainig Part CAD item properties
},
… remainig Part CAD items
],
… remaining Part item properties
}
Properties specified using the $expand option are always implicitly included in the $select list.
$select
The $select option defines which properties should be included in a result set. The ID property must always be included:
GET http://host/server/odata/Part?$select=item_number
Response:
{
"@odata.context": “http://host/server/odata/$metadata#Part(item_number)”,
“value":
[
{"id": "…", “item_number": “P-01"},
{"id": "…", “item_number": “P-02"},
… remaining Part items
]
}
You can also apply the $select option to related items:
GET http://host/server/odata/Part?$expand=Part_CAD($select=related_id)
Response:
{
"@odata.context": “http://host/server/odata/$metadata#Part”,
“value":
[
{
“id": "…",
“item_number": “P-01",
“Part_CAD":
[
{
“id": "…",
“related_id@odata.navigationLink": “CAD(‘CCF205347C814DD1AF056875E0A880AC’)”,
“related_id@aras.keyed_name": “CAD-01",
},
… remaining Part CAD items
],
… remaining Part item properties
},
… remaining Part items
]
}
If you omit the $select option, the OData service will not return properties that have a null value. In order to return all properties the client must use the star “*” value for the $select query option in the request.
$orderby
The $orderby option specifies the order of items in a result set. The Translator converts the sorting expression to AML syntax and sets the orderBy attribute of the Item element:
GET http://host/server/odata/Part?$orderby=item_numer desc
$top
The $top option limits the result set to the first $top items. In the following example, the query specifies that Part items 01-10 be returned. The Translator adds the fetch attribute to the AML request and specifies the GetItemWithPropertyConditions method as the action:
GET http://host/server/odata/Part?$top=10
Response:
{
"@odata.context": “serviceRoot/$metadata#Part”,
“value":
[
{"id": "…", “item_number": “P-01",…},
{"id": "…", “item_number": “P-02",…},
… remaining 8 Part items
]
}
$skip
The $skip option allows a specified number of items in the resulting collection to be skipped. The following example specifies that parts 01-10 be excluded from the collection.
The Translator adds the offset attribute to the AML request and specifies the GetItemWithPropertyConditions method as the action:
GET http://host/server/odata/Part?$skip=10
Response:
{
"@odata.context": “serviceRoot/$metadata#Part”,
“value":
[
… Parts starting from 11th item remaining 8 Part items
{"id": "…", “item_number": “P-01",…},
{"id": "…", “item_number": “P-02",…},
]
}
$count
If the $count option is present and set to true, the translator adds the @odata.count property to the resulting data set with a value equal to the number of items to be included in the response:
GET http://host/server/odata/Part?$count=true
Response:
{
"@odata.context": “serviceRoot/$metadata#Part”,
"@odata.count": 10
“value":
[
{"id": "…", “item_number": “P-01",…},
{"id": "…", “item_number": “P-02",…},
…
]
}
If the result does not return a data set $count is ignored.
$format
The $format option only supports the JSON format for communication.