> ## Documentation Index
> Fetch the complete documentation index at: https://flatfileinc-docs-update-17fa2cd7-6746-4699-92f4-b60088f8142.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Using Flatfile query language

> Learn how to filter data in Sheets with FFQL.

<Snippet file="shared/dxpbanner.mdx" />

FFQL (Flatfile Query Language) is Flatfile's custom query language used for filtering data in <Tooltip tip="A collection of fields...">[Sheets](/learning-center/blueprint/about)</Tooltip>.
It's logical, composable, and human-readable.

## Overview

### Search by value

Here's an example request for searching for rows where the First Name column is "Bender" and the Last Name column is "Rodriguez".

```sql GET sheets/<sheetId>/records request theme={"system"}
GET sheets/<sheetId>/records?q=first_name eq Bender and last_name eq Rodriguez
```

```sql query theme={"system"}
first_name eq Bender and last_name eq Rodriguez
```

### Search for null

Here's an example request for searching for rows where the First Name column is null.

```sql GET sheets/<sheetId>/records request theme={"system"}
GET sheets/<sheetId>/records?q=first_name eq ""
```

```sql query theme={"system"}
first_name eq ""
```

***

## Syntax

The basic syntax of FFQL is:

```sql theme={"system"}
[field name] <operator> <value>
```

### Field Name

`field name` is optional and excluding a field name will search across all fields. For example: `eq "Planet Express"` will search across all fields for that value.
`field name` can be the field key or the field label.

Labels or values with spaces should be wrapped in quotes. For example: `name eq "Bender Rodriguez"`, or `"First Name" eq Bender`.

### Operators

FFQL operators are:

* `eq` - Equals (exact match)
* `ne` - Not Equal
* `lt` - Less Than
* `gt` - Greater Than
* `lte` - Less Than or Equal To
* `gte` - Greater Than or Equal To
* `like` - (Case Sensitive) Like
* `ilike` - (Case Insensitive) Like
* `contains` - Contains (for columns of type `string-list` and `enum-list`)

Both `like` and `ilike` support the following wildcards:

* `%` - Matches any number of characters
* `_` - Matches a single character

So, for instance, `like "Bender%"` would match "Bender Rodriguez" and "Bender Bending Rodriguez".

### Logical Operators

FFQL supports two logical operators:

* `and` - Returns rows meeting both conditions
* `or` - Returns rows meeting either conditions (inclusive)

### The `is` Operator

The `is` operator allows you to query for validation status at both the record and field level:

* `is error` - Returns all rows that have any validation errors
* `is valid` - Returns all rows that have no validation errors
* `first_name is error` - Returns rows where the First Name field specifically has validation errors
* `first_name is valid` - Returns rows where the First Name field has no validation errors

This operator is particularly useful for filtering data based on validation status, allowing you to quickly identify and address problematic records.

<Note>
  The `is` operator works with Advanced Filters to create dynamic filters that combine field selection with validation status checks.
</Note>

Example:

```sql theme={"system"}
email is error and last_name eq "Smith"
```

This query would return all rows where the email field has validation errors and the last name is "Smith".

***

## Constructing Queries

Complex queries are possible using a combination of operators:

```sql theme={"system"}
(
    email like "@gmail.com"
    and (
        "Subscription Status" eq "On-Hold"
        or "Subscription Status" eq "Pending"
    )
    and login_attempts gte 5
)
or is warning
```

This query would return all the rows that:

1. Have a Gmail email address,
2. Have a Subscription Status of "On-Hold" or "Pending",
3. And have more than 5 login attempts.

<Note>It will also include any rows that have a "Warning" message status.</Note>

***

## Usage

### Via search bar

From the search bar in a Workbook, prepend **filter:** to your FFQL query.

```json type in search bar theme={"system"}
filter: first_name eq Bender and last_name eq Rodriguez
```

### Via Advanced Filters

The Advanced Filters feature allows you to build complex FFQL queries through a user-friendly interface. You can select fields, choose operators (including the `is` operator for validation status), and specify values to create dynamic filters.

To use Advanced Filters:

1. Click the "Filter" button in the toolbar
2. Select "Advanced filter" from the dropdown menu
3. Build your query by adding conditions and selecting operators
4. Apply the filter to see the filtered results

Advanced Filters support all FFQL operators, including field-specific validation checks using the `is` operator.

### Via API

FFQL queries can be passed to any <Tooltip tip="View API reference">[REST API](https://reference.flatfile.com/overview/welcome)</Tooltip> endpoint that supports the `q` parameter.

Here's an example **cURL** request using the `sheets/<sheetId>/records` endpoint:

```shell Shell / cURL theme={"system"}
curl --location 'https://platform.flatfile.com/api/sheets/us_sh_12345678/records?q="Subscription Status" eq "On-Hold" or "Subscription Status" eq "Pending"' \
    --header 'Accept: */*' \
    --header 'Authorization: Bearer <bearer token>'
```

Make sure to [encode](https://en.wikipedia.org/wiki/URL_encoding) percent characters if you use them.