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:JohnTo filter for rows where the age is greater than or equal to 18:
age:gte:18To filter for rows where the status is "active" and score is greater than 75:
status:eq:active;score:gt:75To 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:activeOR 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
ilikeorcontainsoperators. - For fields that contain special characters (like
:or&), make sure to URL-encode the filter string.