---
title: "Filtering, views and SQL"
description: "Narrow a list with a saved view, a search, a filter tree or a where clause, and read or change many rows with gtable's SQL dialect. All of it through your permissions."
canonical: "https://gtable.app/docs/api/filtering-and-sql"
updated: "2026-10-05"
---

# Filtering, views and SQL

Every way of narrowing a list is AND-ed with your permissions: it can only narrow what you would
see anyway.

## Saved views

Ask for a saved view instead of rebuilding its filters. It is one parameter, it stays correct
when someone edits the view, and it is usually a much smaller answer.

```http
GET /v1/tables/tbl_8fj3kq2wmv5d/records?viewId=viw_e4ks9mz2bh7w
```

A view belongs to whoever made it; you are handed your own views and nobody else's.

## Search, filters, sorts and where

| Parameter | Takes                                                     |
| --------- | --------------------------------------------------------- |
| `search`  | a substring, matched over the text fields you can read    |
| `filter`  | a filter tree as JSON, the same shape a saved view stores |
| `sorts`   | a JSON array of `{ "fieldId", "direction" }` levels       |
| `where`   | a SQL-style condition, in the dialect below               |

```http
GET /v1/tables/tbl_8fj3kq2wmv5d/records?filter={"id":"root","kind":"group","mode":"and","children":[{"id":"c1","kind":"condition","fieldId":"fld_7qk2mv9xp4ht","op":"gt","value":50000}]}
GET /v1/tables/tbl_8fj3kq2wmv5d/records?where=Stage = 'won' AND Amount > 50000
```

URL-encode the values in real requests. A field in a filter, a sort or a grouping may be named
by id or by exact name. One you cannot read is refused with a `422` that lists the fields you
can, and never names the hidden one.

A field you can read on only some of your rows cannot be filtered or sorted on (`403`), because
counting matches would reveal rows outside your scope. `GET /v1/app` lists those fields per
table under `constrained`.

To turn a sentence into a filter tree, `filters.suggest` takes the sentence and returns a tree
you can inspect before you use it.

## SQL

`POST /v1/sql/query` reads; `POST /v1/sql/execute` changes many rows. Nothing you send is run as
written: the statement is parsed, every column is checked against what you may read, every value
becomes a bound parameter, and your permissions are added to the `WHERE`.

```sql
SELECT stage, COUNT(*) AS n, SUM(amount) AS total FROM Deals GROUP BY stage ORDER BY n DESC
SELECT name, amount FROM Deals WHERE "Expected close" > CURRENT_DATE LIMIT 20
UPDATE Deals SET probability = 100 WHERE stage = 'won'
DELETE FROM Deals WHERE stage = 'lost' AND amount < 1000
```

What the dialect covers:

- one statement over one table, named by name, plural or id; fields by name or id, with double
  quotes around a name that has spaces;
- `AND OR NOT`, comparisons, `[NOT] LIKE`, `[NOT] IN (…)`, `IS [NOT] NULL`, `[NOT] BETWEEN`,
  both forms of `CASE`;
- `LENGTH LOWER UPPER TRIM ABS ROUND COALESCE SUBSTR DATE STRFTIME IFNULL INSTR REPLACE`, and
  `COUNT SUM AVG MIN MAX` with `GROUP BY`;
- on a link field, `HAS ANY ('rec_…', …)`, `HAS ALL (…)` and `IS EMPTY`.

No `JOIN`, no subquery, no `INSERT` (use `records.create`). A `SELECT` returns at most 1,000 rows.
A column you cannot read is "Unknown column", the same as one that does not exist. A field you
can read on only some rows reads as `NULL` on the others, inside sums and expressions too.

An `UPDATE` or `DELETE` touches up to 500 rows, each through the ordinary permission checks, and
is **one** change in the history, undone in one step. Without a `WHERE` it is refused unless you
send `confirm: true`. `dryRun: true` returns the count and the first ids and changes nothing.
`sql.query` refuses a write and `sql.execute` refuses a `SELECT`, so a key holding only
`records:read` gets exactly what its scope says.

Builders have the same two operations on any app, under `/v1/apps/{appId}/sql/…` on the Studio.