Skip to content

Script Paging

Matillion's Script Paging is a powerful scripting language that allows you to specify how data should be fetched and processed in a Custom Connector, giving you control over pagination, rate limiting, and API request and response management .

Note

We assume you will have some familiarity with scripting languages in order to use this feature. If you prefer to work without code, Custom Connector also offers a range of standard paging options that will suit most needs. Read Pagination for a description of the available options.

Using Script Paging

When creating a custom connector:

  1. Follow the process given in Custom connector setup.
  2. When selecting a Pagination option, select Script.
  3. This gives you a multi-line text input field where you can write your Script Paging script, following the syntax rules given below.

You must configure any required header parameters, query parameters, and URI parameters in the custom connector set-up. Script Paging operations can then use these parameters to control the paging of the response.

For example, the following endpoint URI specifies values for the limit and offset parameters:

https://api.spacexdata.com/v3/launches?limit=5&offset=0

Your script can then read these parameter values, use them in expressions, and write new values to them, as required. For example:

// Get the current value of the offset parameter
var offset = @request.query.get("offset");

// Increment the offset parameter value by 5
var nextPageOffset = offset + 5;

// Use the new value for the offset parameter
@request.query.put("offset", nextPageOffset);

All of these script operations are described in the following sections.

It's important to understand how and when the Script Paging script is executed.

  1. The request for data is sent to the endpoint URI, passing all header, query and URI paramters. The script isn't executed at this point.
  2. The first page of results is retrieved, based on the passed parameters.
  3. The script is now executed, sequentially one line at a time. The script can now modify any parameters, including those in the response header and response body which was retrieved in step 2.
  4. The next page of data is retrieved, based on the parameters which may have been modified by the script.
  5. The script is executed again.
  6. Steps 4 and 5 continue until a script operation determines that data retrieval should stop, or the end of the retrievable data is reached.

Basic syntax

Script Paging scripts are composed of statements. Each statement typically performs a single action, such as setting a variable, modifying a request, or processing a response. A script typically consists of multiple statements. Each statement must be placed on a separate line and end with a semicolon ;.

You can include comments in your scripts to explain the logic or to temporarily disable code.

  • Single-line comments start with //.
  • Multi-line comments are enclosed between /* and */.

An example of the script syntax is as follows:

// If body is empty then stop retrieving pages
var contents = @response.body.get("/0");
@pager.stop(contents == "");

Specific elements of this script are described in the following sections.

Variables

Variables are defined by the keyword var followed by the variable name and the value assigned to it. Variables can have one of four data types:

  • String: A string of characters, enclosed in quotes " ".
  • Boolean: true or false.
  • Number: An integer with or without a decimal point.
  • Array: An array of strings, booleans, or numbers.

You don't have to explicitly assign a data type; Script Paging will automatically determine the appropriate type based on the data assigned to it.

Some examples of variable definitions:

//String
var contentType = "application/json";

// Number
var pageNumber = 2;

// Boolean
var isAuthorized = true;
var isAuthorized = false;

// Array
var numberSet = [2, 5, 5];
var nameList = ["john", "mary", "brian"];
var flagList = [true, false, false];

You can declare a variable with the value of another variable already declared:

var contentType = "application/json";
var copyOfType = contentType;

Arithmetic operations

You can decare the value of a number variable to equal the result of an arithmetic operation. For example:

var result = 1 + 1;
var multiplicationResult = result * 5;
var divisionResult = multiplicationResult / 2;
var finalResult = divisionResult - 3;

String operations

To concatenate strings or string variables, use the + operator. Some examples:

var fullName = "Joe" + " " + "Bloggs";

var firstName = "Joe";
var lastName = "Bloggs";
var fullName = firstName + " " + lastName;

If a string contains quotes, ", they must be escaped with the \ character when assigned to a string variable:

var myString = "This is a \"quoted\" string";

Comparison expressions

Script Paging supports the following comparison operators:

  • Equal to ==
  • Not equal to !=
  • Less than <
  • Less than or equal to <=
  • Greater than >
  • Greater than or equal to >=

The form of the comparison statement is "value" operator "value". For example:

fullName == "Joe Bloggs"
pageNumber < 1000

Comparison expressions are used in the pager.stop operation, which will exaluate an expression to determine if there are more pages to retrieve. For example:

// paging will stop when page number 1000 is reached
@pager.stop(pageNumber = 1000);

Script Paging operations

The full power of Script Paging is from the operations that allow you to define how responses from endpoints are processed. These operations are listed in this section.

Operation statements begin with the @ symbol. The operation may return data which you can assign to a variable, or it may perform some operation based on the result of an evaluated expression.

pager.pageCount()

Returns the number of pages fetched. You can assign the returned number to a variable for use later in the script.

Example:

// Get the count of pages and assign it to the variable "pages"

var pages = @pager.pageCount();

pager.stop(expression)

Tells the connector not to attempt to fetch the next page if the expression evaluates to true.

Parameters:

  • expression: string. A valid Script Paging comparison expression that will be evaluated to produce a true or false result.

Examples:

// Stop fetching pages if the pages variable equals 1000

@pager.stop(pages == 1000);

// Stop fetching pages if the @pager.pageCount() operation returns a number greater than 1000

@pager.stop(@pager.pageCount() > 1000);

response.header.get(key)

Returns the value of the specified key in a key-value pair in the response header.

Parameters:

  • key: string. The key that you want the value of.

Example:

// Return the X-Pagination value from the response header

var pagingJson = @response.header.get("X-Pagination");

Gets the next page link from the response header of an API that uses link header paging. This works for any API that uses a standard link header paging model, for example the GitHub API illustrated in Example 6.

Example:

// Assign the next page link to the variable nextLink

var nextLink = @response.header.getNextLink();

Gets the last page link from the response header of an API that uses link header paging. This works for any API that uses a standard link header paging model, for example the GitHub API illustrated in Example 6.

Example:

// Assign the last page link to the varible lastLink

var lastLink = @response.header.getLastLink();

response.body.get(key)

Returns the value of the specified key in a key-value pair in the response body.

Parameters:

  • key: string. The key that you want the value of. You must specify the key location using JSON Pointer notation, as defined in RFC 6901.

Example:

// Get the value of "/data/name" from the response and assign it the variable "name"

var name = @response.body.get("/data/name");

response.status.get()

Return the value of the response status code as a number.

Example:

// Assign the status code to the variable "status"

var status = @response.status.get();

request.header.put(key, value)

Adds the specified key value to the request headers parameters.

Parameters:

  • key: string. The name of the key that the value will be assigned to.
  • value: string. The value that will be assigned to the key.

Example:

// Assign the value "1" to the key "page"

@request.query.put("page", "1");

request.header.remove(key)

Removes the value of the specified key from request header parameters.

Parameters:

  • key: string. The name of the key that will be removed.

Example:

// Remove the key "page" from the request header

@request.header.remove("page");

request.header.clear()

Clears all existing values from the request header parameters.

Example:

// Remove all values from the request header

@request.header.clear();

request.header.get(key)

Returns the value of the specified key in a key-value pair in the request header.

Parameters:

  • key: string. The key that you want the value of.

Example:

// Get the value of "name" from the response and assign it the variable "name"

var name = @request.header.get("name");

request.query.put(key, value)

Adds the specified key value to the request query parameters.

Parameters:

  • key: string. The name of the key that the value will be assigned to.
  • value: string. The value that will be assigned to the key.

Example:

// Assigns the string "json" as the value of the "format" key

@request.query.put("format", "json");

request.query.remove(key)

Removes the value of the specified key from request query parameters.

Parameters:

  • key: string. The name of the key that will be removed.

Example:

// Remove the key "page" from the request query

@request.query.remove("page");

request.query.clear()

Clear all existing values from the request query parameters.

Example:

// Remove all values from the request query

@request.query.clear();

request.query.get(key)

Returns the value of the specified key in a key-value pair in the request query parameters.

Parameters:

  • key: string. The key that you want the value of.

Example:

// Get the value of "name" from the request query and assign it the variable "name"

var name = @request.header.get("name");

request.body.set(jsonString)

Sets the value of a key-value pair in the request body. This is used with JSON body format.

Parameters:

  • jsonString: string. A valid JSON string that contains a key-value pair. You must specify the key location using JSON Pointer notation, as defined in RFC 6901. Quotes in the string must be escaped as \".

Example:

// Set the JSON string in a variable, then pass that variable to the operation
var jsonString = "{ \"key\": \"value\" }";
@request.body.set(jsonString);

request.body.put(key, value)

Adds a new key-value pair to the request body. This is used with JSON body format.

Parameters:

  • key: string. The name of the key that you want to add.
  • value: string. The value assigned to the key.

Example:

// Put the key "name" in the request body and assign it the value of the variable "name"

@request.body.put("name", name);

request.body.remove(key)

Removes the value of the specified key from the request body. This is used with JSON body format.

Parameters:

  • key: string. The name of the key that you want to remove.

Example:

// Remove the key "name" from the request body

@request.body.remove("name");

request.body.clear()

Clears all existing values from the request body. This is used with JSON body format.

Example:

// Clear all values from the request body

@request.body.clear();

request.body.get(key)

Returns the value of the specified key in a key-value pair in the request body. This is used with JSON body format.

Parameters:

  • key: string. The key that you want the value of.

Example:

// Get the value of "name" from the request body and assign it the variable "name"

var name = @request.body.get("name");

request.uri.set(URI)

Sets the value of the URI.

Parameter:

  • URI: string. The URI to set.

Example:

// Set the request URI to be https://api.datasource.com

@request.uri.set("https://api.datasource.com")

request.uri.append(path)

Appends a string to the request URI. Typically used to append a relative path to a base URI.

Parameter:

  • path: string. The path to append to the request URI.

Example:

// Append the string /pagination/next to the request URI

@request.uri.append("/pagination/next");

request.uri.replace(parameter, value)

Replaces parameterized values in the URI path. The parameterized values are set as URI parameters.

Parameters:

  • parameter: string. The name of the parameter to be replaced.
  • value: string or number. The value to replace the parameter with.

Example:

// Replace the id paramter with the value 5 in the URI https://api.com/user/{id}

@request.uri.replace("id", 5)

request.ratelimit.set(allowlist, header, wait, retry)

Configures the rate limit for the endpoint. Suggested values for rate limits can usually be found in the API's documentation.

Parameters:

  • allowlist: array of numbers, for example [429, 403]. These are the status codes for surpressing the rate limit error.
  • header: string. The response header key whose presence indicates throttled response.
  • wait: number. Sets the wait in milliseconds, which should be extracted from the response.
  • retry: number. Sets number of retries.

Example:

// Set the ratelimit

@request.ratelimit.set([403, 329], "x-ratelimit-reset", 3600000, 5);

json.get(path, object)

Extracts a value from a JSON object. This requires knowing the path to the object in the JSON structure.

Parameters:

  • path: string. The JSON path to the object. Specify the path using / to separate the names of nested elements, with / by itself indicating a root-level element.
  • object: string. The JSON object to extract the value from.

Example:

Assuming the following JSON structure:

{
  "total_count": 4672,
  "pages": {
    "type": "pages",
    "next": {
      "page": 2,
    },
  }
}

// Assign the JSON object to a variable
var response = @response.header.get("X-Pagination");

// Extract the value of "page"
var page = @json.get("/pages/next/page", response)

// Extract the value of total_count
var count = @json.get("/total_count", response)

Examples

The following examples show some common uses for Script Paging. In each case, we give a real-world API call and the response returned by that call. We then show a suggested script to paginate that response.

Example 1: relative path

In this example, each page of data retrieved from the API endpoint provides us with a relative path which points to the next page we need to retrieve. We can use a script to read that path from each page and use it to retrieve the next page.

The following URI retrieves filtered data in JSON format:

https://api.coronavirus.data.gov.uk//v1/data?filters=areaName=England&format=json

This retrieves a page of data with the following structure:

{
  "data": [],
  "pagination": {
    "current": "/v1/data?filters=areaName=England&format=json&page=1",
    "next": "/v1/data?filters=areaName=England&format=json&page=2",
    "previous": null,
    "first": "/v1/data?filters=areaName=England&format=json&page=1",
    "last": "/v1/data?filters=areaName=England&format=json&page=1"
  }
}

We can use the following script to query that data structure and extract from it the path to the next page of data, appending that path to the URI used in the next call to the endpoint. As this script is executed after each page is retrieved, we continue to fetch each subsequent page until we reach the last page.

@request.uri.set("https://api.coronavirus.data.gov.uk");
var nextPage = @response.body.get("pagination/next");
@request.uri.append(nextPage);
@pager.stop(nextPage == null);

Example 2: full path

This is similar to the last example, in that each page of data contains a pointer to the next page of data. However, the pointer is a full URI, not a relative path. This makes our script simpler, as we don't have to concatenate the different parts of a URI.

Use the following URI to retrieve the first page of data:

https://www.zopim.com/api/v2/chats

This returns data with the following structure:

{
  "data": [],
  "count": 1016,
  "next_url": "https://www.zopim.com/api/v2/chats?cursor=eyJjb3VudCI6IDc"
}

A simple script extracts the next page URI and uses it to fetch the next page of data:

var nextPage = @response.body.get("/next_url");
@request.uri.set(nextPage);
@pager.stop(nextPage == "");

Example 3: page based

An endpoint which uses page-based pagination requires an incrementing a page parameter to retrieve each subsequent page. Each page of data contains its page number in the response structure, meaning that we need to read that number, increment it, and query again with the incremented page number.

In the following URI, we are telling the endpoint to send us data starting at page 1:

https://randomuser.me/api?page=1&results=10&format=json

The first page of data is returned with the following structure:

{
  "data": [],
  "info": {
    "seed": "faf4b0a59eae416e",
    "results": 10,
    "page": 1,
    "version": "1.4"
  }
}

Our script needs to extract the value of the current page from the response body, add 1 to it, and construct a new query which includes the new page number as a parameter. We will use the script to stop data retrieval after 50 pages:

var currentPage = @response.body.get("/info/page");
var nextPage = currentPage + 1;

@request.query.put("page", nextPage);
@request.query.put("results", "10");
@request.query.put("format", "json");

var pageNumber = @response.body.get("/info/page");
@pager.stop(pageNumber == 50);

Example 4: cursor based

Cursor pagination uses a cursor parameter to navigate between pages. We need a script that reads the value of the next page cursor and puts that into the next query.

Use the following URI to retrieve a page of data:

https://api.intercom.io/contacts?per_page=150

The response has the following structure:

{
  "data": [],
  "total_count": 4672,
  "pages": {
    "type": "pages",
    "next": {
      "page": 2,
      "starting_after": "WzE2NjI1MzU0OTgwMDAsIjYyNGFkMDVlOWJkOTg5MWFlYzVlYzI0ZSIsMl0="
    },
    "page": 1,
    "per_page": 150,
    "total_pages": 32
  }
}

The cursor that points to the next page is starting_after. The following script will extract this and put it into the next page query:

var cursor = @response.body.get("/pages/next/starting_after");
@request.query.put("starting_after", cursor);
@request.query.put("per_page", 150);

var totalPages = @response.body.get("/pages/total_pages");
var pageNumber = @response.body.get("/pages/page");
@pager.stop(totalPages == pageNumber);

Example 5: offset

Offset pagination involves paging by incrementing a query parameter. We need to set the parameter in our initial query, and then use a script to increment the parameter for each subsequent page.

Use the following URI to retrieve the first page of data:

https://api.spacexdata.com/v3/launches?limit=5&offset=0

This data has the following structure:

[
    {
        "flight_number": 1,
        "mission_name": "FalconSat",
        "mission_id": [],
        "upcoming": false,
        "launch_year": "2006"
    }
]

As there is no paging data in the response, the following script takes the offset value from the original query paramter and increments it by a fixed amount to retrieve the next page of data. Sensible offset numbers can usually be found in the API's documentation. Note that because nothing in the response tells us when we've reached the last page, we're checking for when an empty response body is returned, as this will tell us we've reached the end of the data.

var currentOffset = @request.query.get("offset");
var newOffset = currentOffset + 5;
@request.query.put("offset", newOffset);

// If the body is an empty array, stop paging
var contents = @response.body.get("/0");
@pager.stop(contents == "");

Link header pagination uses a field in the response header to point to the next page, as in the following example:

Link=<https://api.github.com/repositories/1300192/issues?page=2>; rel="next", <https://api.github.com/repositories/1300192/issues?page=572>; rel="last"

We can use the following script to retrieve the link to the next page. If we also retrieve the link to the last page, we can then compare the two and stop paging when we reach the last page:

var nextLink = @response.header.getNextLink();
var lastLink = @response.header.getLastLink();
@request.uri.set(nextLink);
@pager.stop(nextLink == lastLink);
© Copyright 2024 Matillion Ltd | Legal | Privacy Policy | Disclaimer | Modern Slavery Statement