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 finder parameter supported by a resource to query records, or you can use the q parameter to query and filter a collection resource.

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.

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=WinProb>=40;StgOrder!=1, returns an error in Version 2 and later. The RowMatch expression, =WinProb>=40 or StgOrder!=1, 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:
  • CurrencyCode in ('USD','GBP')
• 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 Setting 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.

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 MyOpportunitiesFinder, and the finder variable name, to get an opportunity with a specific name, such as "Big Data Analytics Servers," 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/opportunities?finder=MyOpportunitiesFinder;Name=Big Data Analytics Servers \
-H 'Content-Type: application/vnd.oracle.adf.resourceitem+json'

Paginate

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.

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. To set the sorting sequence, use asc for ascending order and desc for descending order. The default sequencing order is asc. The items returned in the response payload are sorted in a case-sensitive order.

GET /opportunities?orderBy=OptyId

GET /opportunities?orderBy=OptyId&q=OptyId>1000

To sort items according to OptyId in descending order, enter the command:

?orderBy=OptyId:desc

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

?orderBy=OptyId:asc

or only

?orderBy=OptyId

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

?orderBy=CustomerAccountId,dateAdded:desc

You now know how to manage your collection resources!