# Search filters

This topic describes how to search for specific results using Rebilly collection APIs.
This information applies to API endpoints that return multiple resources.
It does not apply to endpoints that require a resource ID.

Use the `filter` query parameter on a collection to select the records that are returned in the response.
Fields and values in the filter are separated with `:`.

Fields are typed.
Use a value that matches the type of a field.
For example, a boolean value on a string field, such as `primaryAddress.lastName:true`, does not match any results.

The following example uses the `filter` query parameter to retrieve sales transactions from the [Transactions endpoint](/catalog/all/transactions/gettransactioncollection).

```curl
curl -G https://api-sandbox.rebilly.com/organizations/your-organization-id/transactions \
  -H 'REB-APIKEY: your-private-key' \
  --data-urlencode filter="type:sale"
```

Most values must be an exact case-insensitive match.
Some fields support [partial searches](#partial-searches).

## Compound filters

A filter consists of a field name and values separated by `:`.
To chain multiple filters, separate each `field:value` pair with `;`.
The filters are joined with `AND` logic.

The following example uses two filters to search for approved sales transactions.

```curl
curl -G https://api-sandbox.rebilly.com/organizations/your-organization-id/transactions \
  -H 'REB-APIKEY: your-private-key' \
  --data-urlencode filter="type:sale;result:approved"
```

## Field names

Field names that contain subfields use a `.` to separate each part.

For example, billing address fields use the `billingAddress` prefix with properties such as `firstName` and `lastName`.
The complete field name for `firstName` is `billingAddress.firstName`.

| **Query** | **Description** |
|  --- | --- |
| `billingAddress.lastName:Smith` | Billing address with a last name of "Smith". |
| `customer.customFields.fieldName:true` | A custom field named `fieldName` on a customer. |


## Multiple values

Separate multiple values for one field with `,`.
The values are joined with `OR` logic.

| **Query** | **Description** |
|  --- | --- |
| `primaryAddress.firstName:John` | Results with `firstName` of John. |
| `primaryAddress.firstName:John,Bill` | Results with `firstName` of John OR Bill. |


## Reserved characters

This section lists all reserved characters and how to use them.

- `:` separates a field from its value.
Only the first `:` in a filter is a separator.
A `:` in a value does not require a backslash.
- `;` separates filters.
- `,` separates values.
- To include a `;` or a `,` in a value, add a backslash before it.
- A backslash before `:`, `;`, or `,` makes the character part of the value, and the filter drops the backslash.
Before any other character, the backslash stays in the value.
For information on `*` and `?`, see [Partial searches](#partial-searches).
- A value that contains `..` becomes a [range](#range-filters).
A backslash does not affect range parsing.


| **Query** | **Description** |
|  --- | --- |
| `description:Refund\, partial` | Results with the description "Refund, partial". |
| `url:https://example.com/orders` | Results with the URL `https://example.com/orders`. |


## Boolean values

Boolean values are represented as `true` and `false`.
They are written as a string filter, but are interpreted as booleans.

| **Query** | **Description** |
|  --- | --- |
| `isDisputed:true` | Results where a transaction has a dispute. |


## Empty values

Empty values use the keyword `null`.

| **Query** | **Description** |
|  --- | --- |
| `billingAddress.address2:null` | Results where the `address2` property in a billing address is empty or missing. |
| `billingAddress.address2:!null` | Results where `address2` of the billing address is not empty. |
| `description:Something,null` | Results where a transaction description is either empty or contains the value `Something`. |


## Range filters

Range filters use `..` to separate either end of a range.
Ranges are always inclusive.

Range filters support integers, floats, and datetimes.

Ranges support integers, floats, and datetime.
This also includes relative datetimes.

A range filter that mixes dates and numeric values is not valid.

Greater than or equals (`gte`) is represented as: `1..`.

Less than or equal (`lte`) is represented as: `..3`.

| **Query** | **Description** |
|  --- | --- |
| `revision:1..3` | With revision of 1, 2, or 3. |
| `revision:2..` | Revision of 2 or higher. |
| `revision:..2` | Revision of 2 or less. |
| `createdTime:30 days ago..1 day ago` | Created between 30 days ago and one day ago. |
| `createdTime:2024-01-20T00:00:00Z..2024-01-31T23:59:59Z` | Created in January. |


## Dates

Date-time fields accept values formatted using [RFC 3339.](https://www.rfc-editor.org/rfc/rfc3339)

### Relative dates and times

Relative dates and times are accepted.
Example: `createdTime:7 days ago..`.

Relative dates and times are based from the moment the search occurs.

In relative dates and times, `now` is the moment the search occurs.

Dates and times on the edge of the time window can be unpredictable.
For example, `1 day ago` means "one day ago from the current time this query is run."

For a list of acceptable entries and syntax, see [Relative formats](https://www.php.net/manual/en/datetime.formats.php#datetime.formats.relative).

| **Query** | **Description** |
|  --- | --- |
| `createdTime:2024-01-02T00:00:00Z..` | 2024-01-02 at midnight or more recent. |
| `createdTime:2 days ago..1 day ago` | Created only two days ago. |
| `createdTime:5 mins ago..1 min ago` | Created only five minutes ago. |


## Negated filters

Negation is represented with a `!` before the negated value: `result:!approved`.

To negate multiple values, prefix each value with `!`.

| **Query** | **Description** |
|  --- | --- |
| `result:!approved` | Results that are not approved. |
| `result:!approved,!declined` | Results that are not approved nor declined. |


## Value lists

Use value lists to compare a value against a list of data.
Value lists apply to conditions for rules or binds, and to filters on data table segments.
Common lists contain values for conditions that target specific properties.
Examples include customers, transactions, and Bank Identification Numbers (BINs).

| **Query** | **Description** |
|  --- | --- |
| `primaryAddress.firstName:@ListOfNames` | Uses the value list with the ID `ListOfNames`.
The values are expanded and joined with `OR`. |
| `primaryAddress.country:!@ExcludeCountries` | Returns results that exclude the countries in this list. |


### Manage value lists using the Rebilly API

For information on how to manage lists using the Rebilly API, see [Lists](/catalog/all/lists/).
Code examples are provided for all API endpoints.

## Custom fields

[Custom fields](/docs/dev-docs/api/custom-fields) are searchable.
Each custom field name starts with a common prefix.
The last part of the name is the custom field to search.

For example, a customer has a string-based custom field named "category".
To search all customers with `category: "VIP"`, use the following request.

```curl
curl -G https://api-sandbox.rebilly.com/organizations/your-organization-id/customers \
  -H 'REB-APIKEY: your-private-key' \
  --data-urlencode filter="customFields.category:VIP"
```

Some collections support a search on their own custom fields, and on the custom fields of related resources.

| Collection | Resource | Field prefix |
|  --- | --- | --- |
| [Customer](/docs/dev-docs/customers) | [Customer](/docs/dev-docs/customers) | `customFields` |
| [Order](/docs/dev-docs/orders) | [Order](/docs/dev-docs/orders) | `customFields` |
| [Order](/docs/dev-docs/orders) | [Customer](/docs/dev-docs/customers) | `customer.customFields` |
| [Transaction](/docs/dev-docs/transactions) | [Customer](/docs/dev-docs/customers) | `customer.customFields` |
| [Transaction](/docs/dev-docs/transactions) | [Transaction](/docs/dev-docs/transactions) | `customFields` |
| [Transaction](/docs/dev-docs/transactions) | [Payment instrument](/docs/dev-docs/payment-instruments) | `paymentCard.customFields` |


### Monetary custom fields

Use filters on custom fields with `type: monetary`.
A filter value is an integer or a float.
An ISO-4217 currency code is optional.
If there is no currency code, the search covers every currency with that amount.

Do not use a currency symbol.

See the following examples.

| **Query** | **Description** |
|  --- | --- |
| `customFields.SetupFee:9.99USD,14.99` | Filter on items with a custom field named `SetupFee` of `9.99 USD` or `14.99` of any currency. |
| `customFields.SetupFee:9.99..` | Filter on items with a custom field named `SetupFee` of `9.99` or higher in any currency. |
| `customFields.SetupFee:10USD..20USD` | Filter on items with a custom field named `SetupFee` between (and including) `10 USD` and `20 USD`. |
| `customFields.SetupFee:9.99CAD,9.99USD,5.99EUR` | Filter on items with a custom field named `SetupFee` for values of `9.99 CAD`, `9.99 USD`, or `5.99 EUR`. |


## Partial searches

Some fields support partial searches.
Partial matching applies when a filter has one value.
If a filter has [multiple values](#multiple-values), each value must be an exact match.

Contact name and email fields, company fields, and string custom fields match by prefix.
A value matches every result that starts with it.
In a prefix field, an asterisk is part of the value to match.

The following example returns customers with a last name that starts with "Smi", such as Smith or Smithers.

```curl
curl -G https://api-sandbox.rebilly.com/organizations/your-organization-id/customers \
  -H 'REB-APIKEY: your-private-key' \
  --data-urlencode filter="primaryAddress.lastName:Smi"
```

Resource IDs, and the `url` and `route` fields of log resources, support wildcards.
Use `*` to match any number of characters, and `?` to match one character.
To match a literal `*` or `?`, add a backslash before it.
The first five characters of a value must be literal.
The filter ignores wildcards in the first five character positions.

The following example returns transactions with an ID that starts with `txn_0YV`.

```curl
curl -G https://api-sandbox.rebilly.com/organizations/your-organization-id/transactions \
  -H 'REB-APIKEY: your-private-key' \
  --data-urlencode filter="id:txn_0YV*"
```