Query Parameters

When query REST calls need to be more specific, Oracle CPQ Cloud Service can interpret query specifications that follow Oracle CPQ Cloud Service query parameters syntax (specified below), and query specifications that follow a subset of MongoDB syntax.

Query Parameters Rules

  • The parameters list must be separated from the URI endpoint of the REST resources by a "?"
  • Each parameter in the parameters list must be separated by an "&"ampersand
  • Each parameter must be connected to its parameter specification by an "="
  • Parameters can appear in any order in the list

Query Parameters URL Format

{resourceURI}?{param}={paramSpec}&{param}={paramSpec}&{param}={paramSpec}
Endpoint URL Parameter Description
{resourceURI} The URI endpoint of the REST API resource.
{param} A query parameter.
{paramSpec} The parameter specification of the preceding parameter.

Query Parameter Descriptions

Query Parameter Description

fields

Specifies a comma-separated list of fields to be included in response.

fields=node,eventDate,applicationVersion

Note: If the fields query parameter isn't used, all fields will be included in the response. If one or more fields are specified with the fields query parameter, those fields and system-determined default fields will be included in the response.

Note: Calls made to the Transaction Manager (a call to a Commerce Process without specifying a Transaction ID) do not support the fields query parameter. System-determined default fields and all Data Columns will always be included in the response of Transaction Manager calls.

q

Declares a query specification expression in MongoDB format.

q={createdBy:'Matt'}

orderby

Specifies a comma-separated list of pairs to order the response by.

orderby=node,eventDate:desc,applicationVersion:asc

limit

Specifies the pagination limit, up to 1000. If no limit is specified or if a limit greater than 1000 is specified, a pagination limit of 1000 will be used.

limit=2

offset

Specifies the offset in the record set to start the response from.

Note: Usually used in conjunction with limit parameter to obtain the next set of records.

Note: When limit=1, the offset parameter is ignored in the query specifications.

offset=2

totalResults

Specifies that the total count of records should be included in the response when doing pagination. Always will be equal to true, because if totalResults is not specified, the total count of records will not be included in the response.

Note: Only effective when limit or offset are specified.

totalResults=true

expand

This parameter expands an asset object and its children.

expand=all

childAssests.all

This parameter expands a child asset object and its children.

expand=childAssests.all

childAssets.wrongname.all

This parameter expands a non-existing object. Child wrongname at path childAssets.wrongname does not exist or is not accessible in childAssets object for current use.

expand=childAssets.wrongname.all

childAssets*.all

This parameter recursively expands an asset object and all children assets.

expand=childAssets*.all

childAssets*.all&fields=childAssets.partNumber

This parameter recursively expands and activates specified fields. It will only activate and retrieve fields specified in the query.

expand=childAssets*.all&fields=childAssets.partNumber

MongoDB Query Specifications

When a q parameter is introduced within query parameters, Oracle CPQ Cloud Service will interpret the subsequent query specification as a JSON construct following MongoDB query syntax. The MongoDB expression can contain a single condition, or multiple conditions that are combined with a conjunction operator. $and and $or are the only MongoDB conjunction operators that can be interpreted by Oracle CPQ Cloud Service.

Each condition must be expressed as a name-value pair, using the following format:

{[varName]:{$[op]'[value]'}}
Condition Parameter Description
[varName] The variable name of the attribute being used in the condition.
[op] The comparison operator to relate the attribute with an attribute value.
[value] The value of the attribute to relate to the comparison operator.

The following MongoDB comparison operators are the only comparison operators that can be interpreted by Oracle CPQ Cloud Service:

Comparison Operator Description Example

eq

Equals

q={createdBy:{$eq:'Matt'}}

ne

Not equals

q={createdBy:{$ne:'Matt'}}

gt

Greater than

q={quantity:{$gt:'100'}}

gte

Greater than or equals

q={quantity:{$gte:'100'}}

lt

Less than

q={quantity:{$lt:'100'}}

lte

Less than or equals

q={quantity:{$lte:'100'}}

exists

  • exists true = IS NOT NULL
  • exists false = IS NULL

q={createdBy:{$exists:true}}

For more information on MongoDB syntax, see MongoDB Query Documents documentation.

$like Operator Support for q Parameter

This addition provides sequel style "LIKE" operations via the $regex operator, because this functionality is not supported by the MONGO standard. To ease usage, it also provides an extension "$like" operator, which translates to the SQL LIKE operator more intuitively. Both cases support a Case Insensitive search option.

$like Operator Examples:

Description $like Operator Example $regex Operator Example

FieldN contains "myValue"

?q={"FieldN":{$like:"%myValue%"}}

?q={"FieldN":{$regex:"myValue"}}

FieldN starts with "myValue"

?q={"FieldN":{$like:"myvalue%"}}

?q={"FieldN": {$regex:"^myvalue"}}

FieldN ends with "myValue"

?q={"FieldN":{$like:"%myvalue"}}

?q={"FieldN":{$regex:"myvalue$"}}

FieldN matches "myValue"

?q={"FieldN":{$like:"myvalue"}}

?q={"FieldN":{$regex:"^myvalue$"}}

FieldN matches "myValue"

Case Insensitive

?q={"FieldN":{$like:"myvalue",$options:"I"}}

?q={"FieldN":{$regex:"/^myvalue$/i"}