Applying Filters in Sheet2DB API

To apply filters to the data returned from the Sheet2DB API, you can use the filter query parameter. The filter accepts a query string that defines conditions to be applied to the data fields. The filtering syntax is flexible and supports both basic filters and complex nested conditions using AND and OR groups.

Basic Filter Format

The basic format for a filter is as follows:

<field>:<operator>:<value>

Where:

• <field>: The name of the column you want to filter.
• <operator>: The comparison operator you want to apply (e.g., eq, lt, contains, etc.).
• <value>: The value you're comparing the field against.

Supported Operators

Here is a list of supported operators that you can use in your filters:

Operator	Description
eq	Equal to (e.g., name:eq:John)
ne	Not equal to (e.g., age:ne:30)
lt	Less than (e.g., price:lt:100)
lte	Less than or equal to (e.g., quantity:lte:10)
gt	Greater than (e.g., height:gt:5.5)
gte	Greater than or equal to (e.g., score:gte:75)
like	Matches pattern (e.g., name:like:Jo%)
ilike	Case-insensitive match (e.g., description:ilike:dog)
starts_with	Starts with (e.g., title:starts_with:Intro)
ends_with	Ends with (e.g., title:ends_with:end)
contains	Contains (e.g., description:contains:apple)
in	Value in a list (e.g., color:in:red,blue,green)
nin	Value not in a list (e.g., status:nin:inactive)
is_null	Is null (e.g., discount:is_null)
is_not_null	Is not null (e.g., quantity:is_not_null)
json_has_key	JSON field contains key (e.g., data:json_has_key:id)

Example Queries

To filter for rows where the name field is equal to "John":

name:eq:John

To filter for rows where the age is greater than or equal to 18:

age:gte:18

To filter for rows where the status is "active" and score is greater than 75:

status:eq:active;score:gt:75

To filter for rows where the description contains the word "apple" or the category is "Fruit":

description:contains:apple;or(category:eq:Fruit)

Combining Filters Using AND/OR

You can combine filters using AND or OR to create more complex queries.

AND Example: All conditions must be true:

age:gte:18;status:eq:active

OR Example: At least one condition must be true:

or(status:eq:inactive;age:lt:18)

You can mix both AND and OR conditions for more complex filtering:

Combined AND/OR Example: Filtering with nested conditions:

and(age:gte:18;or(status:eq:active;status:eq:pending))

How to Use the Filter in the API

To use the filter in your API request, include it as a query parameter like this:

https://<api-endpoint>?filter=<query_string>

For example, if you want to filter for rows where the age is greater than 18 and status is active, you would call:

https://api.sheet2db.com/data/aace0271-c068-499f-bb40-83354a35eebc?filter=age:gte:18;status:eq:active

Notes:

• Filters are case-sensitive unless using the ilike or contains operators.
• For fields that contain special characters (like : or &), make sure to URL-encode the filter string.