# Querying and Filtering Records

Frontline provides a powerful JSON-based Query DSL to filter, search, and sort records (both CRM Objects and custom Tables). You can use this syntax in the `query` field of the request bodies of list and export endpoints.

## Structure of a Query

A query is represented as a JSON object. It can be a single condition or a nested logical group that joins multiple conditions.

### Single Condition

A single condition performs a comparison on a specific field path:

```json
{
    "path": "[Status]",
    "operator": "equals",
    "value": "Active"
}
```

- **`path`**: The path to the field formatted in bracket notation: `"[Field Name]"`.
- **`operator`**: The comparison operator (e.g., `equals`, `contains`, `gte`).
- **`value`**: The comparison value. Certain operators (like `isNull` or `isNotNull`) do not require a value.


### Logical Group

You can combine multiple conditions using logical operators:

```json
{
    "operator": "and",
    "conditions": [
        { "path": "[Age]", "operator": "gte", "value": 18 },
        { "path": "[Status]", "operator": "equals", "value": "Active" }
    ]
}
```

- **`operator`**: Either `"and"` or `"or"`.
- **`conditions`**: An array of condition objects or nested logical groups.


## Operators by Field Type

Each field type supports a specific set of operators.

### String Fields

| Operator | Description | Value Type |
|  --- | --- | --- |
| `equals` | Exact match | `"string"` |
| `ne` | Not equal | `"string"` |
| `contains` | Substring match | `"string"` |
| `startsWith` | Starts with | `"string"` |
| `endsWith` | Ends with | `"string"` |
| `isNull` | Field is empty | *None* |
| `isNotNull` | Field has value | *None* |


### Number Fields

| Operator | Description | Value Type |
|  --- | --- | --- |
| `equals` | Equal to | `number` |
| `ne` | Not equal | `number` |
| `lt` | Less than | `number` |
| `lte` | Less than or equal | `number` |
| `gt` | Greater than | `number` |
| `gte` | Greater than or equal | `number` |
| `isNull` / `isNotNull` | Null checks | *None* |


### Date / DateOnly Fields

> [!NOTE]
- For **`date`** fields, values must be ISO-8601 Date-Time strings (e.g., `"2026-04-17T00:00:00Z"`).
- For **`dateOnly`** fields, values must be `YYYY-MM-DD` Date strings (e.g., `"2026-04-17"`).



| Operator | Description | Value Type (Date) | Value Type (DateOnly) |
|  --- | --- | --- | --- |
| `equals` | Exact date | `"2026-04-17T00:00:00Z"` | `"2026-04-17"` |
| `before` | Before date | `"2026-04-17T00:00:00Z"` | `"2026-04-17"` |
| `after` | After date | `"2026-04-17T00:00:00Z"` | `"2026-04-17"` |
| `onOrBefore` | On or before | `"2026-04-17T00:00:00Z"` | `"2026-04-17"` |
| `onOrAfter` | On or after | `"2026-04-17T00:00:00Z"` | `"2026-04-17"` |
| `between` | Date range | `{ from: "2026-01-01T00:00:00Z", to: "2026-12-31T23:59:59Z" }` | `{ from: "2026-01-01", to: "2026-12-31" }` |
| `isNull` / `isNotNull` | Null checks | *None* | *None* |


### Tags (Select/Multi-select) Fields

> [!NOTE]
Tag values must be numeric **tag IDs**, not names. Use the field metadata (retrieved via the fields API) to list option IDs.


| Operator | Description | Value Type |
|  --- | --- | --- |
| `containsAny` | Has any of these tags | `[1, 2]` (tag IDs) |
| `containsAll` | Has all of these tags | `[3, 5]` (tag IDs) |
| `notIn` | Does not have any of | `[4]` (tag IDs) |
| `isNull` / `isNotNull` | Null checks | *None* |


### Relation Fields

| Operator | Description | Value Type |
|  --- | --- | --- |
| `containsAny` | Linked to any of | `["id1", "id2"]` |
| `notIn` | Not linked to any of | `["id1"]` |
| `isNull` | No linked records | *None* |
| `isNotNull` | Has linked records | *None* |


### Boolean Fields

| Operator | Description | Value Type |
|  --- | --- | --- |
| `isTrue` | Value is true | *None* |
| `isFalse` | Value is false | *None* |
| `isNull` / `isNotNull` | Null checks | *None* |


### Formula Fields

Formula fields evaluate to one of the primitive types (number, string, boolean, date, tags) and support all of their respective operators. In addition, they support calculation status operators:

| Operator | Description | Value Type |
|  --- | --- | --- |
| `isValid` | Formula was evaluated successfully | *None* |
| `isInvalid` | Formula evaluation failed with an error | *None* |


## Advanced Query Examples

### 1. Complex Nested Logic (AND + OR)

To query records matching `(Status is Open AND Amount >= 10,000) OR (Priority is High)`, combine logical groups recursively:

```json
{
    "operator": "or",
    "conditions": [
        {
            "operator": "and",
            "conditions": [
                { "path": "[Status]", "operator": "containsAny", "value": [1] },
                { "path": "[Amount]", "operator": "gte", "value": 10000 }
            ]
        },
        {
            "path": "[Priority]",
            "operator": "containsAny",
            "value": [5]
        }
    ]
}
```

### 2. Date Range Queries

To query records created during the year 2026, use the `between` operator with an object specifying `from` and `to` date strings:

```json
{
    "path": "[Created At]",
    "operator": "between",
    "value": {
        "from": "2026-01-01T00:00:00Z",
        "to": "2026-12-31T23:59:59Z"
    }
}
```

### 3. Tag Filtering (Select / Multi-select)

To find rows tagged with both Tag ID `3` (e.g., `"Premium"`) and Tag ID `5` (e.g., `"High Value"`):

```json
{
    "path": "[Customer Category]",
    "operator": "containsAll",
    "value": [3, 5]
}
```