Filtering, Sorting, Pagination

The returned response may contain over million rows in some cases. You can narrow down the large result set by employing a filter in your request and/or using pagination to define how many rows should be returned in the response. Returned rows can be also sorted by the specified attribute.

Filtering

Set up the filter on the object's attributes. There are two types of filters available – simple and advanced.

Simple Filtering

The following filter settings return only those objects where the version attribute is 8 and (the filters are ANDed) the currency attribute is USD:

{
   "textMatchStyle":"exact",
   "data":{
      "version":"8",
      "currency":"USD"
   }
}

The following filter returns only those objects where the sku attribute starts with P10 (by the textMatchStyle equals to startWith filter setting):

{
   "textMatchStyle":"startsWith",
   "data":{
      "sku":"P10"
   }
}

The following filter returns only objects where the sku attribute contains product (by the textMatchStyle equals to substring filter setting):

{
   "textMatchStyle":"substring",
   "data":{
      "sku":"product"
   }
}

Advanced Filtering

The advanced filter (specified by "_constructor":"AdvancedCriteria") enables you to employ more complex filtering options.

The following filter example returns only workflows where code equals to Q AND (defined by "operator":"and") workflowStatus equals to Approved.

{
   "data":{
      "_constructor":"AdvancedCriteria",
      "operator":"and",
      "criteria":[
         {
            "fieldName":"code",
            "operator":"equals",
            "value":"Q"
         },
         {
            "fieldName":"workflowStatus",
            "operator":"equals",
            "value":"APPROVED"
         }
      ]
   }
}

As you can see above, you can define filter objects within the criteria object (array). Each filter object consists of:

fieldName : The name of the field you want to filter results by. Use the custom operator to apply a custom JPQL/HQL expression within the fieldName – see the example below.

operator : The operator of the filter criteria. See the list of available operators. See the custom() method for more details regarding the custom filter.

value : The value of the field you want to filter results by.

start : The start date of the date range when using, for example, the iBetween operator. Use the ISO 8601 date format (YYYY-MM-DD).

end : The end date of the date range when using, for example, the iBetween operator. Use the ISO 8601 date format (YYYY-MM-DD).

Example – when the filter below is applied, only records where lastUpdateDate is not equal to attribute30 are returned:

{
    "startRow": 0,
    "endRow": 300,
    "data": {
        "operator": "or",
        "criteria": [{
            "operator": "custom",
            "fieldName": "lastUpdateDate<>attribute30",
            "value": ""
        }]
    },
    "distinctResults": false,
    "valueFields": []
}

Custom JPQL/HQL expression operators

The following operators can be used when a custom filter is applied:

• Comparison operators: = , <> (or != ), < , > , <= , >= . These are used to compare one expression with another. The result is true only if the comparison is valid.
• Logical operators: AND , OR , NOT . These are used to combine or invert conditions.
• Null comparison operators: IS NULL , IS NOT NULL . These are used to check whether a value is null or not.
• Arithmetic operators: + , - , * , / , mod. These are used to perform basic arithmetic operations.
• LIKE operator: Used for string matching. The '%' character can be used as a wildcard.
• BETWEEN operator: Used to filter the result within a range of values.
• IN operator: Used to check if a value is within a list of values.
• Member Of : Used to check if a specific entity instance is a contained in a collection.
• EMPTY operator: Used to check if a collection is empty or not.
• SIZE operator: Used to determine the number of elements in a collection.

Operators

Sorting

You can set the sort order of the returned objects. Use the sortBy parameter - an array of strings that specify one or more fields to sort on (in that order).

By default, the order is ascending, but if you need descending, you can prepend "-" (minus) character before the name of the field you want to sort by.

The following sorting setting sorts the returned result by the label field in descending order:

{
   "sortBy":[
      "-label"
   ]
}

Pagination

Commands of the type fetch enforce the maximum result size of 1 million rows. This is enforced for unbounded requests (startRow and endRow parameters not set) or when the delta between them exceeds that number.

Please note: It is generally advisable to fetch results in reasonably sized batches and support the paging mechanism of the protocol (startRow / endRow / totalRows).

Implement the pagination to return a defined number of items in the result and/or to return a specified result set. Pricefx REST API utilizes startRow and EndRow parameters to set the pagination on returned objects.

The following pagination setting returns 300 rows, starting with row 2700 and ending with row 3000:

{
   "startRow":2700,
   "endRow":3000
}