API v3 sorting, filtering and pagination

Updated on 20-October-2016 at 10:16 AM

Business Catalyst End of life announcement - find out more details.

Introduction

With the new version 3 API endpoints it is possible to pass various query parameters that control the output right from the endpoint's URL.

Note: With the flexibility the API v3 filtering offers the syntax can get pretty complicated. To make the syntax easier to work with we have introduced the BC API Discovery app . This is a free tool that enables you to visually create complex queries you can then copy-paste right in your app.

All of the methods detailed below can also be used with {module_data}. Take a look at the module_data sorting, filtering and pagination article for more details

Resource ordering

Each collection can be sorted (ascending and descending) using the order query parameter. Below is an example where we will get the list of orders sorted in a descending order:

var request = $.ajax({
    url: "/webresources/api/v3/sites/current/orders?order=-totalPrice",
    type: "GET",
    conenection: "keep-alive",
    contentType: "application/json",
    headers: {
        "Authorization": $.cookie('access_token')
    }
});
request.done(function (msg) {
    console.log(msg);
})
request.fail(function (jqXHR) {s
    console.log("Request failed.");
    console.log("Error code: " + jqXHR.status);
    console.log("Error text: " + jqXHR.statusText);
    console.log("Response text: " + jqXHR.responseText);
})

Filtering

You can now get the data filtered by various parameters using query operators in the case of API calls and the where parameter. In the examples below we will use the Orders endpoint:


var request = $.ajax({
    url: "/webresources/api/v3/sites/current/orders?where={'creationDate':'2014-06-10'}",
    type: "GET",
    conenection: "keep-alive",
    contentType: "application/json",
    headers: {
        "Authorization": $.cookie('access_token')
    }
});
request.done(function (msg) {
    console.log(msg);
})
request.fail(function (jqXHR) {s
    console.log("Request failed.");
    console.log("Error code: " + jqXHR.status);
    console.log("Error text: " + jqXHR.statusText);
    console.log("Response text: " + jqXHR.responseText);
})

Warning: By default all the items will be fetched, including the disabled ones. You would need to filter specifically only for the items enabled by using for example {'enabled':true}.

Query operation syntax Description Example
{
  <serializedName>: <value>
}
Equals operation applied to the specified property.

Get all orders created on 10th of June 2014.

{
  "creationDate": "2014-06-10"
}

endpoint URL:

 /webresources/api/v3/sites/current/orders?where={"creationDate":"2014-06-10"}
            
            
{
  <serializedName>: {
    "$lt": <value>
  }
}

 

 

Less than operation applied to the specified property.

Return all orders created before 10th of June 2014.

{ "creationDate": { "$lt": "2014-06-10" } }

endpoint URL:

 /webresources/api/v3/sites/current/orders?where={"creationDate":{"$lt":"2014-06-10"}}

module_data:

where="\{'creationDate':\{'$lt':'2014-06-10'\}\}"
{
  <serializedName>: {
    "$lte": <value>
  }
}
Less than or equal operation applied to the specified property.

Return all orders created until 11th of June 2014.

{
  "creationDate": {
    "$lte": "2014-06-10"
  }
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"creationDate":{"$lte":"2014-06-10"}}
            
{
  <serializedName>: {
    "$gt": <value>
  }
}
Greater than operation applied to the specified property.

Get all orders with a total price greater than 99.9.

{
  "totalPrice": {
    "$gt": 99.9
  }
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"totalPrice":{"$gt":99.9}}
{
  <serializedName>: {
    "$gte": <value>
  }
}
Greater than or equal operation applied to the specified property.

Get all orders with a total price greater than or equal with 99.9.

{
  "totalPrice": {
    "$gte": 99.9
  }
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"totalPrice":{"$gte":99.9}}
{
  <serializedName>: {
    "$ne": <value>
  }
}
Not equal operation applied to the specified property.

Get all orders where total price is different than 99.9.

{
  "totalPrice": {
    "$ne": 99.9
  }
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"totalPrice":{"$ne":99.9}}
{
  <serializedName>: {
    "$in": <value>
  }
}
In (contain) operation applied to the specified property.

Get all orders where total price is 99.9 or 22.5 or 0.99.

{
  "totalPrice": {
    "$in": [
      99.9,
      22.5,
      0.99
    ]
  }
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"totalPrice":{"$in":[99.9,22.5,0.99]}}
{
  <serializedName>: {
    "$nin": <value>
  }
}
Not in (not contained) operation applied to the specified property.

Get all orders where total price is different than 99.9 and 22.5 and 0.99.

{
  "totalPrice": {
    "$nin": [
      99.9,
      22.5,
      0.99
    ]
  }
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"totalPrice":{"$nin":[99.9,22.5,0.99]}}
{
  "$or": [
    <filter 1>,
    <filter 2>
  ]
}
Or operation applied to a list of conditions. It is the equivalent of or statement from SQL.

Get all orders where total price is 99.9 or was created on 10th of June or 11th of June 2014.

{
  "$or": [
    {
      "totalPrice": 99.9
    },
    {
      "creationDate": {
        "$in": [
          "2014-06-10",
          "2014-06-11"
        ]
      }
    }
  ]
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"$or":[{"totalPrice":99.9},{"creationDate":{"$in":["2014-06-10","2014-06-11"]}}]}
{
  "$and": [
    <filter 1>,
    <filter 2>
  ]
}
And operation applied to a list of conditions. It is equivelent of AND statement from SQL.

Get all orders where total price is between 99.99 and 199.99.

{
  "$and": [
    {"totalPrice": {"$gt": 99.99}},
    {"totalPrice": {"$lt": 199.99}}
  ]
}

endpoint URL:

/webresources/api/v3/sites/current/orders?where={"$and": [{"totalPrice": {"$gt": 99.99}},{"totalPrice": {"$lt": 199.99}}]}
{
  <serializedName>: {
    "$contains": <string value>
  }
}
Contains operation applicable only for string fields. It detects if serializedName field contains requested string value.

Get all orders where order description contains sample order string.

{
  "description": {
    "$contains": "sample order"
  }
}

endpoint URL:

 /webresources/api/v3/sites/current/orders?where={"description":{"$contains":"sampleorder"}}
{
  <serializedName>: {
    "$beginsWith": <string value>
  }
}
Begins with operation applicable only for string fields. It detects if serializedName starts with requested string value.

Get all orders where order description starts with sample.

{
  "description": {
    "$beginsWith": "sample"
  }
}

endpoint URL:

 /webresources/api/v3/sites/current/orders?where={"description":{"$beginsWith":"sample"}}
{
 <serializedMappedResourceName.serializedName>:  {
    <operator>: <value>
   }
 }
{
<serializedMappedResourceName.serializedName>:<value>
}
Operation applied to the specified property of subresource. The current version of web resources supports only one level of nesting

Lists all the products that have a price with a rate greater than 5. Prices is a subresource for orders resource.

{
  "prices.rate" : {
    "$gt" : 5
  }
}

endpoint URL:

/webresources/api/v3/sites/current/products?where={"prices.rate":{"$gt":5}}

or

Lists all customers which have email1 and it's value contains '@gmail.com'. Email1 is a subfield for customers resource.

{
  "email1.value" : {
    "$contains" : "@gmail.com"
  }
}

endpoint URL:

/webresources/api/v3/sites/current/customers?where={"email1.value":{"$contains":"@gmail.com"}}

Resource pagination

Each resource can be paginated using the skip and limit query parameters. These parameters are optional, if you do not specify them their default values are skip=0 and limit=10.

Do note the limit parameter cannot be larger than 500, all v3 APIs and module_data are limited to a maximum of 500 items per request.

In the example below we get a batch 50 orders and skip the first 100:

var request = $.ajax({
  url: "/webresources/api/v3/sites/current/orders?skip=100&limit=50",
  type: "GET",
  conenection: "keep-alive",
  contentType: "application/json",
  headers: {
    "Authorization": $.cookie('access_token')
  }
});
request.done(function(msg) {
  console.log(msg);
}) request.fail(function(jqXHR) {
  s console.log("Request failed.");
  console.log("Error code: " + jqXHR.status);
  console.log("Error text: " + jqXHR.statusText);
  console.log("Response text: " + jqXHR.responseText);
})