Using Filters

Filters allow you to only return rows that match certain conditions.

Filters can be used on select(), update(), and delete() queries.

If a Database function returns a table response, you can also apply filters.

Applying Filters#

Filters must be applied after any of select(), update(), upsert(), delete(), and rpc() and before modifiers.

1final data = await supabase
2  .from('cities')
3  .select('name, country_id')
4  .eq('name', 'The Shire');    // Correct
5
6final data = await supabase
7  .from('cities')
8  .eq('name', 'The Shire')    // Incorrect
9  .select('name, country_id');

Chaining#

Filters can be chained together to produce advanced queries. For example, to query cities with population between 1,000 and 10,000:

1final data = await supabase
2  .from('cities')
3  .select('name, country_id')
4  .gte('population', 1000)
5  .lt('population', 10000);

Conditional Chaining#

Filters can be built up one step at a time and then executed. For example:

1final filterByName = null;
2final filterPopLow = 1000;
3final filterPopHigh = 10000;
4
5var query = supabase
6  .from('cities')
7  .select('name, country_id');
8
9if (filterByName != null)  { query = query.eq('name', filterByName); }
10if (filterPopLow != null)  { query = query.gte('population', filterPopLow); }
11if (filterPopHigh != null) { query = query.lt('population', filterPopHigh); }
12
13final data = await query;

Filter by values within a JSON column#

1create table
2  users (
3    id int8 primary key,
4    name text,
5    address jsonb
6  );
7
8insert into
9  users (id, name, address)
10values
11  (1, 'Michael', '{ "postcode": 90210 }'),
12  (2, 'Jane', null);

1final data = await supabase
2  .from('users')
3  .select()
4  .eq('address->postcode', 90210);

1{
2  "data": [
3    {
4      "id": 1,
5      "name": "Michael",
6      "address": {
7        "postcode": 90210
8      }
9    }
10  ],
11  "status": 200,
12  "statusText": "OK"
13}

Filter Foreign Tables#

You can filter on foreign tables in your select() query using dot notation:

1create table
2  countries (id int8 primary key, name text);
3create table
4  cities (
5    id int8 primary key,
6    country_id int8 not null references countries,
7    name text
8  );
9
10insert into
11  countries (id, name)
12values
13  (1, 'Germany'),
14  (2, 'Indonesia');
15insert into
16  cities (id, country_id, name)
17values
18  (1, 2, 'Bali'),
19  (2, 1, 'Munich');

1final data = await supabase
2  .from('countries')
3  .select('''
4    name,
5    cities!inner (
6      name
7    )
8  ''')
9  .eq('cities.name', 'Bali');

1{
2  "data": [
3    {
4      "name": "Indonesia",
5      "cities": [
6        {
7          "name": "Bali"
8        }
9      ]
10    }
11  ],
12  "status": 200,
13  "statusText": "OK"
14}