Filter database query results using API request parameters in Jitterbit Studio

Introduction

When an operation is published as a custom API, callers can pass values through the URL as query parameters. This guide shows how to read those values using API Jitterbit variables and use them to filter results in a Database Query activity WHERE clause, then return the matching records as the API response.

This pattern is useful for building data-query endpoints where the caller controls which records are returned: for example, a GET endpoint that looks up a customer by ID, retrieves orders for a specific date range, or returns products filtered by category.

This guide assumes the following:

• A Database connection is configured and accessible from the project.
• The operation is published as a custom API with the response type set to Variable. For setup steps, see Expose a Studio operation as a REST API.

Design pattern

Part 1: Add a parameter validation script

URL parameters sent to the API are available as Jitterbit variables in the format $jitterbit.api.request.parameters.<name>, where <name> matches the parameter key in the URL. For example, if a caller sends GET /customers?CustomerID=123, then $jitterbit.api.request.parameters.CustomerID holds 123.

Add a Script component as the first step of the operation. This script reads the parameter value, stores it in a shorter global variable for use in the query, and returns a 400 error if the parameter is missing or empty:

<trans>
$customer_id = $jitterbit.api.request.parameters.CustomerID;

If(IsNull($customer_id) || Length($customer_id) == 0,
  $err = Dict();
  $err["error"] = "Missing required parameter: CustomerID";
  $jitterbit.api.response = JSONStringify($err);
  $jitterbit.api.response.status_code = 400;
  RaiseError("Missing required parameter");
);
</trans>

IsNull and Length together guard against a missing and an empty value. RaiseError stops execution immediately and triggers the operation's configured error action.

Replace CustomerID with the name of the URL parameter as documented for your API. Parameter names are case-sensitive.

Part 2: Configure the Database Query activity

Add a Database Query activity after the validation script and configure it to filter records using the captured parameter value. The customer_id variable set by the validation script is available to the activity at runtime.

Using the wizard

In Step 2: Add conditions of the activity configuration:

1. Under Select Fields, select the fields to return (for example, CustomerID, Name, and Email).

2. Under WHERE clause, use the dropdowns to select the field and operator for the filter condition.

3. In the Value field, enter the variable using square bracket syntax:

   [customer_id]
   

   The variable icon in the field indicates that global variables, project variables, and Jitterbit variables are all accepted here. Begin typing an open bracket ([) or click the icon to see available variables.

4. Click Add to append the condition to the WHERE clause.

5. Click Test Query to validate the query against the database.

   Tip

   Because customer_id is a global variable set only at runtime, Test Query will fail if no value is present. To run a test, temporarily set customer_id to a sample value in the validation script (for example, $customer_id = "123";), test, then remove the sample value before saving.

6. Click Next, review the data schema, and click Finished.

Using manual SQL

For JDBC connections, click Skip Wizard / Write SQL Statement on the first step and enter the query directly. Variables in square brackets are substituted with their runtime values before the query executes:

SELECT CustomerID, Name, Email
FROM Customers
WHERE CustomerID = '[customer_id]'

For numeric columns, omit the surrounding quotes:

SELECT OrderID, Total, Status
FROM Orders
WHERE CustomerID = [customer_id]

Part 3: Return the results as the API response

Add a Transformation after the Database Query activity to map the result fields to output variables. After the transformation, add a trailing Script component to serialize the results and set $jitterbit.api.response:

<trans>
$result = Dict();
$result["CustomerID"] = $out_CustomerID;
$result["Name"] = $out_Name;
$result["Email"] = $out_Email;
$jitterbit.api.response = JSONStringify($result);
$jitterbit.api.response.status_code = 200;
</trans>

Replace $out_CustomerID, $out_Name, and $out_Email with the global variables that the transformation maps the query result fields to. For queries that can return multiple records, collect them into an array in the transformation and serialize the array.

If $jitterbit.api.response is not set before the operation completes, API Manager returns an empty 200 OK response. For the full list of variables available for controlling the API response, see API Jitterbit variables.

Verify the integration

1. Deploy the project.

2. Send a test request to the published endpoint with a valid parameter value:

   GET https://<host>/<service-root>/<version>/<path>?CustomerID=123
   

   Confirm that the response body contains the expected record.

3. Send a request with the parameter omitted:

   GET https://<host>/<service-root>/<version>/<path>
   

   Confirm that the endpoint returns a 400 response with the error message from the validation script.

4. Send a request with a parameter value that matches no records. Confirm that the response reflects an empty result or an appropriate error, depending on how the transformation handles a zero-row query.

5. If any step returns an unexpected result, open the operation logs in Studio and the API logs in API Manager to diagnose the issue.