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 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_pp

In the describe response, the queryable property of an attribute indicates whether that attribute is queryable or not.

Identifying queryable attributes

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=USD

Whereas, 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=Yes

The 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 query expression separated by a semicolon, q=deptno>=10;loc!=NY, returns an error in Version 2 and later. The RowMatch expression, q= deptno>=10 or loc!=NY, is supported in Version 2 and later. The 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 in operator:
    • DecisionLevelCode in ('DIRECTOR','VP')
  • For relational comparisons, use between and not between:
    • WinProb between 80 and 100
    • WinProb not between 80 and 100
  • To create complex query expressions, use and and or, along with the matching set of parentheses:
    • (DescriptionText is not null) or ((Revenue <= 300000) and (WinProb between 80 and 100))

If you're using REST Framework Version 1, you can use query-by-example syntax in requests. However, note that query-by-example syntax doesn't work in Version 2 and later. To learn more about versions, see Set the REST Framework Version.

The following table lists REST data types and the valid operators that you can use in query parameter strings. Note that the operators BETWEEN, NOT BETWEEN, IN, NOT IN, and the wildcard character % are available only in REST framework Version 2 and later.

REST Data Type Supported Operators for q Parameter
string
  • = (Equal to)

    .../Departments?q=DeptName = 'SALES'

  • <> (Not equal to)

    .../Departments?q=DeptName <> 'SALES'

  • LIKE (Like)

    .../Departments?q=DeptName LIKE 'SA%'

    .../Departments?q=DeptName LIKE '%ES'

    .../Departments?q=UPPER(DeptName) LIKE UPPER(''%e%')

  • NOT LIKE (Not like)

    .../Departments?q=UPPER(DeptName) NOT LIKE UPPER('%c%')

  • IN (In)

    .../Departments?q=DeptName IN ('SALES', 'RESEARCH')

  • NOT IN (Not in)

    .../Departments?q=DeptName NOT IN ('SALES', 'RESEARCH')

  • IS NULL (Is null)

    .../Departments?q=DeptName IS NULL

  • IS NOT NULL (Is not null)

    .../Departments?q=DeptName IS NOT NULL

boolean
  • = 'true' (true)

    .../Employees?q=Active = 'true'

  • = 'false' (false)

    .../Employees?q=Active = 'false'

  • <> 'true' (false)

    .../Employees?q=Active <> 'true'

  • <> 'false' (true)

    .../Employees?q=Active <> 'false'

  • = 'Y' (true)

    .../Employees?q=Active = 'Y'

  • = 'N' (false)

    .../Employees?q=Active = 'N'

  • = true (true)

    .../Employees?q=Active = true

  • = false (false)

    .../Employees?q=Active = false

number
  • = (Equal to)

    .../Departments?q=Salary = 3120.99

  • <> (Not equal to)

    .../Departments?q=Salary <> 3120.99

  • < (Less than)

    .../Departments?q=Salary < 3120.99

  • <= (Less than or equal to)

    .../Departments?q=Salary <= 3120.99

  • > (Greater than)

    .../Departments?q=Salary > 3120.99

  • >= (Greater than or equal to)

    .../Departments?q=Salary >= 3120.99

  • BETWEEN (Between)

    .../Departments?q=Salary BETWEEN 2000 AND 3120.99

  • NOT BETWEEN (Not between)

    .../Departments?q=Salary NOT BETWEEN 2000 and 3120.99

  • IN (In)

    .../Departments?q=Salary IN (800, 3120.99)

  • NOT IN (Not in)

    .../Departments?q=Salary NOT IN (800, 3120.99)

  • IS NULL (Is null)

    .../Departments?q=Salary IS NULL

  • NOT NULL (Not null)

    .../Departments?q=Salary NOT NULL

integer
  • = (Equal to)

    .../Departments?q=Deptno = 20

  • <> (Not equal to)

    .../Departments?q=Deptno <> 20

  • < (Less than)

    .../Departments?q=Deptno < 20

  • <= (Less than or equal to)

    .../Departments?q=Deptno <= 20

  • > (Greater than)

    .../Departments?q=Deptno > 30

  • >= (Greater than or equal to)

    .../Departments?q=Deptno >= 30

  • BETWEEN (Between)

    .../Departments?q=Deptno BETWEEN 10 AND 30

  • NOT BETWEEN (Not between)

    .../Departments?q=Deptno NOT BETWEEN 10 and 30

  • IN (In)

    .../Departments?q=Deptno IN (10, 30)

  • NOT IN (Not in)

    .../Departments?q=Deptno NOT IN (10, 30)

  • IS NULL (Is null)

    .../Departments?q=Deptno IS NULL

  • NOT NULL (Not null)

    .../Departments?q=Deptno NOT NULL

date
  • = (Equal to)

    .../Employees?q=HireDate = '1999-01-01'

  • <> (Not equal to)

    .../Employees?q=HireDate <> '1999-01-01'

  • < (Less than)

    .../Employees?q=HireDate < '1999-01-01'

  • <= (Less than or equal to)

    .../Employees?q=HireDate <= '1999-01-01'

  • > (Greater than)

    .../Employees?q=HireDate > '1999-01-01'

  • >= (Greater than or equal to)

    .../Employees?q=HireDate >= '1999-01-01'

  • BETWEEN (Between)

    .../Employees?q=HireDate BETWEEN '1999-01-01' AND '2010-01-01'

  • NOT BETWEEN (Not between)

    .../Employees?q=HireDate NOT BETWEEN '1999-01-01' AND '2010-01-01'

  • IS NULL (Is null)

    .../Employees?q=HireDate IS NULL

  • NOT NULL (Not null)

    .../Employees?q=HireDate NOT NULL

datetime

Note:

Both UTC and local datetime formats are supported.
  • = (Equal to)

    .../Employees?q=HireDateTime = '1999-01-01T08:30:40Z'

  • <> (Not equal to)

    .../Employees?q=HireDateTime <> '1999-01-01T08:30:40Z'

  • < (Less than)

    .../Employees?q=HireDateTime < '1999-01-01T08:30:40Z'

  • <= (Less than or equal to)

    .../Employees?q=HireDateTime <= '1999-01-01T08:30:40Z'

  • > (Greater than)

    .../Employees?q=HireDateTime > '1999-01-01T08:30:40Z'

  • >= (Greater than or equal to)

    .../Employees?q=HireDateTime >= '1999-01-01T08:30:40Z'

  • BETWEEN (Between)

    .../Employees?q=HireDateTime BETWEEN '1999-01-01T08:30:40Z' AND '1999-12-01T08:30:40Z'

  • NOT BETWEEN (Not between)

    .../Employees?q=HireDateTime NOT BETWEEN '1999-01-01T08:30:40Z' AND '1999-12-01T08:30:40Z'

  • IS NULL (Is null)

    .../Employees?q=HireDateTime IS NULL

  • NOT NULL (Not null)

    .../Employees?q=HireDateTime NOT NULL

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 PrimaryKey, and the finder variable AnnouncementId, to get an announcement with a specific identifier. Here's a cURL command example:

curl -u username:password \
-X GET https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/announcements?finder=PrimaryKey;AnnouncementId=300100015957778\
-H 'Content-Type: application/vnd.oracle.adf.resourceitem+json'

Note:

To learn more about the supported finder names and finder variables for any resource, see the Request > Query Parameter > finder section for the resource's Get All method located in the Tasks section of this guide.

How You Use the attributes Parameter for Querying the System for Cross-Domain Identity Management (SCIM) Resources

Use the attributes parameter to query the SCIM resources:

?attributes=<scim-attribute-list>
where the SCIM notation for the attribute list is
<attribute-name>[.<sub-attribute-name>]

You can include the attributes parameter in any request that returns a response with an embedded SCIM resource, for example GET, PATCH, POST, and PUT.

Here are some examples:

  • ?attributes=id, name
  • ?attributes=username, name.familyName
  • ?attributes=displayName, email.primary

How You Use the filter Parameter with SCIM Resources

Use the filter parameter to specify a subset of matching SCIM resources to return:

?filter=<scim-filter>

The following table lists the valid operators in a filtering expression you can use for SCIM resources.

Operator Description

and

Use the and operator and parentheses to organize expressions into a group.

meta.lastModified gt "2011-05-13T04:42:34Z" and userType eq "Employee"

or

You can use the and operator, the or operator, and parentheses to organize expressions into a group.

meta.lastModified gt "2011-05-13T04:42:34Z" or userType eq "Employee"

not

You can use the and operator, the or operator, the not operator, and parentheses to organize expressions into a group.

sw

Interpreted as begin with.

userName sw "J"

ew

Interpreted as end with.

userName ew "N"

co

Interpreted as contains.

userName co "jenson"

eq

Interpreted as equal to.

created eq true

nq

Interpreted as not equal to.

created nq true

ge

Interpreted as greater than or equal to.

meta.lastModified ge "2011-05-13T04:42:34Z"

gt

Interpreted as greater than.

meta.lastModified gt "2011-05-13T04:42:34Z"

le

Interpreted as less than or equal to.

meta.lastModified le "2011-05-13T04:42:34Z"

lt

Interpreted as less than.

meta.lastModified lt "2011-05-13T04:42:34Z"

pr

Interpreted as present, indicating that the attribute contains a value or a complex attribute contains a non-empty node.

firstName pr

Paginate

Most 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 size
  • offset - to specify the starting point from which the resources are returned
  • totalResults - 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 collection
  • totalResults - set to the total number of search records

For example, the client runs a GET command on an announcements resource. The server stores 100 announcements 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 to indicate the total number of records that match your search criteria. 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 /announcements?offset=10&limit=20 11 through 30
GET /announcements?q=Subject LIKE 'meeting%'&limit=10 1 through 10
GET /announcements?q=Subject LIKE 'meeting%'&offset=25 26 through 50
GET /announcements?totalResults=true&limit=20 1 through 20

Response includes the record count that match 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=200

Paginate 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 as OptyId or PartyNumber, to get predictable paging results. For example, to get to the first page of opportunities, use the command:
GET /opportunities?orderBy=OptyId

Assuming 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>1000

To 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 documentId in descending order, enter the command:

?orderBy=documentId:desc

To sort items according to Deptno in ascending order, enter the command:

?orderBy=Deptno:asc

or only

?orderBy=Deptno

because asc is the default sorting order.

If 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 title, and then sort according to dateAdded in descending order, enter the command:

?orderBy=title,dateAdded:desc

How You Sort Records for SCIM Resources

You can sort items returned from a collection resource using the sortBy parameter:

?sortBy=<scim-attribute>
where the SCIM attribute name must be <attribute> for simple attributes and <attribute.subattribute> for complex attributes.

For example, to sort items according to documentId in descending order, enter the command:

?sortBy=documentId&sortOrder=descending

To sort based on multiple fields, for example, to sort items in ascending order of title, and then sort according to dateAdded in descending order, enter the command:

?sortBy=title,dateAdded&sortOrder=descending

You now know how to manage your collection resources!