Manage Collections
A collection resource represents a list of items, such as accounts, customers, or opportunities. You can sort, filter, and paginate your collection resources to make large result sets easier to use and consume. For instance, you might only want to select a subset of information from a customer collection REST resource, instead of the entire data set. You can manage data returned from a collection resource using the following actions:
Query
Querying a REST resource
is easy: you simply need to know how to talk to your web server to
get back the response you want. You can use the q parameter to query and filter
a collection resource, or you can use the finder parameter supported by a
resource to query the records. If you are working with SCIM resources,
you can query using the attributes parameter, and filter results using the filter parameter. You'll find the
details about these parameters as you read through this section.
How You Identify Queryable Attributes
To know which attributes can be used in a GET operation along with
the q parameter, you can use the attribute's queryable properties
from the resource metadata. Use the GET method to retrieve metadata
about REST resources from their /describe endpoints. Use this URL syntax in the request:
https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/<version>/<resource>/describe
Let's assume that you want
to identify the queryable attributes of the receivingReceiptRequests resource.
Use this cURL command to send the describe request.
curl -X GET -u <username:password> https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/receivingReceiptRequests/describe HTTP/1.1 -H 'Content-Type: application/vnd.oracle.adf.resourceitem+json' | json_ppIn the describe response, the queryable property of an attribute indicates whether that attribute is queryable or not.
For example,
in the screenshot, the queryable property of the CurrencyCode attribute is set to true. It means, you can include
this attribute in the URL and use it in a GET operation. For example:
https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/receivingReceiptRequests?q=CurrencyCode=USDWhereas, if the queryable property is
set to false, you can't
use that attribute in a GET operation. Let's assume you want to run
a query on the InsertAndProcessFlag attribute. You include it in the URL to send a GET request:
https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/receivingReceiptRequests?q=InsertAndProcessFlag=YesThe request fails and returns the 400
Bad Request status code in the response. It's because the queryable
property of the InsertAndProcessFlag attribute is set to false (as seen in the screenshot), indicating that it's not a queryable
attribute.
The q Query Parameter
If you've defined the REST framework as Version 2, the q query parameter
uses the expanded expression syntax that supports RowMatch expressions
for filtering collection resources. In Version 2 and later, requests
that use query-by-example syntax that Version 1 supports return an
error.
For example, the following query expression separated by a semicolon, returns an error in Version 2 and later:
q=EmailAddress='loyaltytest@oracle.com'; MemberName=LoyaltyTestHowever, the following RowMatch expression is supported in Version 2 and later:
q=EmailAddress='loyaltytest@oracle.com' or MemberName=LoyaltyTestThe following are examples of advanced query syntax supported in Version 2 and later:
- To test whether
a field's value is in a list of possibilities, use the
inoperator:EmailAddress IN ('tester@oracle.com', 'loyaltytest@oracle.com)' - For relational
comparisons, use
betweenandnot between:CurrencyAmount BETWEEN 10 AND 20.5 CurrencyAmount NOT BETWEEN 10 AND 20.5 - To create complex
query expressions, use
andandor, along with the matching set of parentheses:((Comments IS NOT NULL) AND (Points BETWEEN 10 AND 50)) or TypeCode='ORA_TXN_ACC'
The following table lists REST data types and the valid operators that you can use in query parameter strings.
| REST Data Type | Supported Operators for q Parameter
|
|---|---|
string |
|
boolean |
|
number |
|
integer |
|
date |
|
datetimeNote: Both UTC and local datetime formats are supported. |
|
Query Child Resource Attributes
You can use the q query parameter to perform a subquery on the top-level
resource. The query returns the top-level resource records with child or grandchild resource
records that meet the query criteria. This functionality requires REST Framework Version 2
or higher.
Let's look a two different examples of querying the workers resource that
has child resources workRelationshipsIn and names, and a
grandchild resource called assignments. In the first example, you can run a
query for the workers with workRelationships and their
children assignments that have the specified PositionId
values.
GET https://servername.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?q=workRelationships.assignments.PositionId in (407, 67, 23)In the second example, you can query for all workers with
names, where the FirstName value contains the letters
"Ki".
GET https://servername.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?q=names.FirstName like '%Ki%'The finder Query Parameter
Use the finder parameter when you want
to find a record based on specified criteria. The finder parameter is a predefined
query associated with a resource. Use the supported finder names to
return a record based on the criteria specified in the finder variables.
For example, use the finder MemNumSearch to search for a member with a specific member number, such as "300100151989704,"
which we've used in the following cURL command example:
curl -u username:password \
-X GET https://servername.fa.us2.oraclecloud.com/crmRestApi/resources/11.13.18.05/loyMembers?finder=MemNumSearch;MemberNumber=300100151989704 \
-H 'Content-Type: application/vnd.oracle.adf.resourceitem+json'Paginate
Most REST resource collections need some kind of pagination. Without it, a simple search could return millions of records, bringing your network to a crawl. So, instead of receiving all the records of a collection resource, you can limit the number of records that are displayed on a page in the REST client response. To do so, set the following parameters in the request payload:
limit- to specify the paging sizeoffset- to specify the starting point from which the resources are returnedtotalResults- set to true to include the total number of search records that match the query
Set the following fields in the response payload:
hasMore- set to true when there more records to be returned from the collection; set to false when the last set of records are retrieved from the collectiontotalResults- set to the total number of search records
For example, the client runs a GET command on an opportunities resource.
The server stores 100 opportunities and the current request returns
only 25. To indicate that there are more records to retrieve, the
server sets the read-only hasMore field to true.
Example Response Body
{
"items": [
.
.
.
],
"count": 25,
"hasMore": true,
"limit": 25,
"offset": 0,
"links": [
{
.
.
.
}
]
}If you set the totalResults parameter to true
in the request, the response includes the totalResults field. After retrieving
all the records as indicated by the totalResults value, the server sets hasMore to false.
{
"items": [
.
.
.
],
"totalResults": 100,
"count": 25,
"hasMore": true,
"limit": 25,
"offset": 0,
"links": [
{
.
.
.
}
]
}When you specify the limit and offset parameters, the paginated
result isn't ordered. To ensure consistent results, use the orderBy parameter to order the
results. If you update the collection resource between paging requests,
the records displayed in each page may vary.
| Example Requests | Records Returned |
|---|---|
GET /loyMembers?offset=10&limit=20 |
11 through 30 |
GET /loyMembers?q=ContactFirstNameLIKE ZHRX*&limit=10 |
1 through 10 |
GET /loyMembers?q=FirstNameLIKE ZHRX* &offset=25 |
26 through 50 |
GET /loyMembers?totalResults=true&limit=20 |
1 through 20
Response includes the record count that matches the query. |
Note:
For most resources, the predefined query limit to retrieve the records is set by default to 500 rows or a similar value. If you need to retrieve more records than the set limit, you can do so in batches using the limit and offset parameters. For example, if you have about 1000 records, you can retrieve them in batches of 200 rows using a series of GET requests:/crmRestApi/resources/latest/subscriptionProducts?offset=0&limit=200
/crmRestApi/resources/latest/subscriptionProducts?offset=200&limit=200
/crmRestApi/resources/latest/subscriptionProducts?offset=400&limit=200
/crmRestApi/resources/latest/subscriptionProducts?offset=600&limit=200
/crmRestApi/resources/latest/subscriptionProducts?offset=800&limit=200Paginate Records for SCIM Resources
For paginating the response records for a SCIM resource request, set the following parameters in the request payload:
count- to specify the paging size. The recommended value for this parameter is 100.startIndex- to specify the starting point from which the resources are returned.
| Example Request | Records Returned |
|---|---|
?attributes=username&startIndex=11&count=20 |
11 through 30 |
?attributes=firstname pr&count=10 |
1 through 10 |
Sort
Sorting is another feature
that makes it easier to work with data-heavy resources. You can sort
items returned from a collection resource using the orderBy query parameter.
Note:
You must sort the results using only unique attributes, such asOptyId or PartyNumber, to get
predictable paging results. For example, to get to the first page
of opportunities, use the command:
GET /opportunities?orderBy=OptyIdAssuming that in the response, the last
opportunity returned has OptyId=1000, then to get to the next page, the GET request should be structured
as:
GET /opportunities?orderBy=OptyId&q=OptyId>1000To set the sorting sequence, use asc for ascending order and desc for descending order. The
default sequencing order is asc. Items returned in the response payload are sorted in a case-sensitive
order.
For example, to sort items
according to MemberNumber in ascending order, enter the command:
https://servername.fa.us2.oraclecloud.com/crmRestApi/resources/11.13.18.05/loyMembers?orderBy=MemberNumber:ascIf you include multiple fields in
the query parameter, the order in which you specify the fields determines
the sorting order. For example, to sort items in ascending order of MemberNumber, and then sort according
to LastUpdatedDate in descending
order, enter the command:
https://servername.fa.us2.oraclecloud.com/crmRestApi/resources/11.13.18.05/loyMembers?orderBy=MemberNumber,LastUpdateDate:descYou now know how to manage your collection resources!