Querying Data
OData supports various kinds of query options for querying data. This section will help you go through the common scenarios for these query options.
Filtering a Collection
Use the $filter system query option to filter a collection of resources. The expression specified with $filter is evaluated for each resource in the collection, and only items where the expression evaluates to be true are included in the response. Note that the response to a filtered collection is a collection of the same type, regardless of the number of matched resources.
The following operators are supported:
- The
eqoperator returns true if the left operand is equal to the right operand, otherwise it returns false. - The
neoperator returns true if the left operand is not equal to the right operand, otherwise it returns false. - The
gtoperator returns true if the left operand is greater than the right operand, otherwise it returns false. - The
geoperator returns true if the left operand is greater than or equal to the right operand, otherwise it returns false. - The
ltoperator returns true if the left operand is less than the right operand, otherwise it returns false. - The
leoperator returns true if the left operand is less than or equal to the right operand, otherwise it returns false.
GET serviceRoot/LOGPART?$filter=FAMILYNAME eq '001'
Response Payload
{
"@odata.context": "serviceRoot/$metadata#LOGPART",
"value": [
{
"PARTNAME": "111-001",
"BARCODE": "111-001",
"PARTDES": "Sony HT7100DH Home Theater",
"EPARTDES": null,
"STATDES": "In Use",
...
"EXTPART": 18
},
{
"PARTNAME": "111-002",
"BARCODE": "111-002",
"PARTDES": "Sony HT-DDW790 Home-theater",
...
}
]
}
Filtering a Collection - Dates
Since operators use spaces, which are converted into %20 in the HTTP request, a common pain point is confusing the space as part of the value if it starts with a 20. This is especially true for dates, as YYYY will always start with a 20. For example, in
serviceroot/CUSTNOTESA?$filter=STATUSDATE%20ge%2018-02-23T09:59:00+02:00, ge%2018 is incorrect. It should be ge%202018, instead.
Another issue is the + symbol, which should be replaced with %2B, instead.
GET serviceroot/CUSTNOTESA?$filter=STATUSDATE%20ge%202018-02-23T09:59:00%2B02:00
Filtering a Collection with Logic Operators
You can use and, and or to create more complex filter clauses.
GET serviceRoot/LOGPART?$filter=TYPE eq 'P' and LASTPRICE gt 200
Response Payload
{
"@odata.context": "serviceRoot/$metadata#LOGPART",
"value": [
{
"PARTNAME": "111-012",
"BARCODE": "111-012",
"PARTDES": "Panasonic Black Blu-ray Disc Theater SCBT100",
...
"TYPE": "P",
...
"LASTPRICE": 233.48,
...
"EXTPART": 29
}
]
}
You can also use parantheses to enclose logic operators:
GET serviceRoot/LOGPART?$filter=(TYPE eq 'P' or TYPE eq 'R') and LASTPRICE gt 500
Response Payload
{
"@odata.context": "serviceRoot/$metadata#LOGPART",
"value": [
{
"PARTNAME": "201-005",
"BARCODE": "201-005",
"PARTDES": "Sony White Blu-ray Theater GDGT300",
...
"TYPE": "R",
...
"LASTPRICE": 678.48,
...
"EXTPART": 29
}
]
}
Retrieving Only Records With Changes
Frequently, there is no need to retrieve all records from a given collection, but rather only those records that have been changed in a given time period. You can accomplish this using the $since query option. This will only retrieve results that have been changed since a specified date and time.
GET serviceRoot/ORDERS?$since=2020-01-01T01:15:00%2B02:00
This example would retrieve all order that were changed since January 1st, 2020, after 1:15 in the morning IST. The + of the timezone is econded.
Note that the $since query option is only available for entities that have business process management (BPM) applied to them. However, you can retain the change detection functionality for related entities by using the $expand query option. For example:
GET serviceRoot/ORDERS?$since=2020-01-01T07:25:00%2B02:00&$expand=ORDERITEMS_SUBFORM
This would retrieve the order items for all orders that have had changes since January 1st, 2020.
Sorting a Collection
Use the $orderby system query option to specify ordering criteria. Qualify the sort direction by using the asc or desc keywords.
GET serviceRoot/FAMILY_LOG?$orderby=FAMILYDESC desc
Response Payload
{
"@odata.context": "serviceRoot/$metadata#FAMILY_LOG",
"value": [
{
"FAMILYNAME": "0",
"FAMILYDESC": "Default part family",
...
},
{
"FAMILYNAME": "007",
"FAMILYDESC": "Office Equipments",
...
},
{
"FAMILYNAME": "006",
"FAMILYDESC": "Work Hours",
......
}
]
}
Limiting the Number of Entities
Use $top to limit the number of entities from a collection that will be included in the result.
Please note that even if you do not use $top, the upper limit of returned entities is determined by the MAXFORMLINES system constant in Priority.
GET serviceRoot/FAMILY_LOG?$top=2
Response Payload
{
"@odata.context": "serviceRoot/$metadata#FAMILY_LOG",
"value": [
{
"FAMILYNAME": "0",
"FAMILYDESC": "Default part family",
...
"FAMILY": -1
},
{
"FAMILYNAME": "001",
"FAMILYDESC": "Sound Systems",
...
"FAMILY": 5
}
]
}
Skipping Displayed Entities
Use $skip to request the number of entities in the collection that are to be skipped and not included in the result.
GET serviceRoot/FAMILY_LOG?$skip=1
Response Payload
{
"@odata.context": "serviceRoot/$metadata#FAMILY_LOG",
"value": [
{
"FAMILYNAME": "001",
"FAMILYDESC": "Sound Systems",
...
"FAMILY": 5
},
{
"FAMILYNAME": "00004",
"FAMILYDESC": "Video Equipment",
...
"FAMILY": 62
}
]
}
Combine $skip with $top to divide your entities into pages.
GET serviceRoot/FAMILY_LOG?$top=3&$skip=1
Response Payload
{
"@odata.context": "serviceRoot/$metadata#FAMILY_LOG",
"value": [
{
"FAMILYNAME": "001",
"FAMILYDESC": "Sound Systems",
...
"FAMILY": 5
},
{
"FAMILYNAME": "00004",
"FAMILYDESC": "Video Equipment",
...
"FAMILY": 62
},
{
"FAMILYNAME": "040219",
"FAMILYDESC": "New Family Desc",
...
"FAMILY": 153
}
]
}
Specifying Fields
By default, the the OData service returns all available fields for every record retrieved. It’s possible to specify a subset of the fields which is recommended in order to improve network and application performance.
This is done using the $select query option. The request below returns the only CUSTNAME, CDES and ORDNAME fields for a specified ORDERS record:
GET serviceRoot/ORDERS('SO17000003')?$select=CUSTNAME,CDES,ORDNAME
Response Payload
{
"@odata.context": "serviceRoot/$metadata#ORDERS(CUSTNAME,CDES,ORDNAME)/$entity",
"CUSTNAME": "T000001",
"CDES": "Joshua P. Atkins",
"ORDNAME": "SO17000003"
}
Combining Query Options
It is possible to mix multiple query options in order to create complex queries:
- To include the subform of a subform, use the following syntax:
$expand=ORDERITEMS_SUBFORM($expand=ORDISTATUSLOG_SUBFORM) - When combining options for a subform, use the
;separator:$expand=ORDERITEMS_SUBFORM($filter=PRICE gt 3;$select=PARTNAME,TQUANT,PRICE)
Important: When using the $expand command for a subform with composite keys, the $select command must include all key fields from the upper-level form.
The following request shows how to create a single request using $filter, $expand and $select options:
GET serviceRoot/ORDERS?$filter=CUSTNAME eq '1011'&$expand=ORDERITEMS_SUBFORM($filter=PRICE gt 3;$select=CHARGEIV,KLINE,PARTNAME,PDES,TQUANT,PRICE; $expand=ORDISTATUSLOG_SUBFORM),SHIPTO2_SUBFORM, ORDERSTEXT_SUBFORM&$select=CUSTNAME,CDES,ORDNAME
Response Payload
{
"@odata.context": "serviceRoot/$metadata#ORDERS(CUSTNAME,CDES,ORDNAME,ORDERITEMS_SUBFORM,SHIPTO2_SUBFORM,ORDERSTEXT_SUBFORM,ORDERITEMS_SUBFORM(CHARGEIV,KLINE,PARTNAME,PDES,TQUANT,PRICE,ORDISTATUSLOG_SUBFORM))",
"value": [
{
"CUSTNAME": "T000001",
"CDES": "Joshua P. Atkins",
"ORDNAME": "SO17000003",
"ORDERITEMS_SUBFORM@odata.context": "serviceRoot/$metadata#ORDERS('SO17000003')/ORDERITEMS_SUBFORM(CHARGEIV,KLINE,PARTNAME,PDES,TQUANT,PRICE,ORDISTATUSLOG_SUBFORM)",
"ORDERITEMS_SUBFORM": [
{
"CHARGEIV": 5,
"KLINE": 1,
"PARTNAME": "TR0001",
"PDES": "Almonds, mascarpone & strawberries tart",
"TQUANT": 1,
"PRICE": 33,
"ORDISTATUSLOG_SUBFORM@odata.context": "serviceRoot/$metadata#ORDERS('SO17000003')/ORDERITEMS_SUBFORM(CHARGEIV=5,KLINE=1)/ORDISTATUSLOG_SUBFORM",
"ORDISTATUSLOG_SUBFORM": [
{
"ORDISTATUSDES": null,
"UDATE": "2017-05-18T09:26:00+03:00",
"USERLOGIN": "apidemo",
"KLINE": 15450668
}
]
},
{
"CHARGEIV": 5,
"KLINE": 2,
"PARTNAME": "FD0001",
"PDES": "Phistacio Macrons with chocolate",
"TQUANT": 1,
"PRICE": 25,
"ORDISTATUSLOG_SUBFORM@odata.context": "serviceRoot/$metadata#ORDERS('SO17000003')/ORDERITEMS_SUBFORM(CHARGEIV=5,KLINE=2)/ORDISTATUSLOG_SUBFORM",
"ORDISTATUSLOG_SUBFORM": [
{
"ORDISTATUSDES": null,
"UDATE": "2017-05-18T09:26:00+03:00",
"USERLOGIN": "apidemo",
"KLINE": 15450669
}
]
}
],
"SHIPTO2_SUBFORM@odata.context": "serviceRoot/$metadata#ORDERS('SO17000003')/SHIPTO2_SUBFORM/$entity",
"SHIPTO2_SUBFORM": null,
"ORDERSTEXT_SUBFORM@odata.context": "serviceRoot/$metadata#ORDERS('SO17000003')/ORDERSTEXT_SUBFORM",
"ORDERSTEXT_SUBFORM": []
}
]
}