Using Filters

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

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

If a Postgres 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.

1const { data, error } = await supabase
2  .from('cities')
3  .select('name, country_id')
4  .eq('name', 'The Shire')    // Correct
5
6const { data, error } = 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:

1const { data, error } = 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:

1const filterByName = null
2const filterPopLow = 1000
3const filterPopHigh = 10000
4
5let query = supabase
6  .from('cities')
7  .select('name, country_id')
8
9if (filterByName)  { query = query.eq('name', filterByName) }
10if (filterPopLow)  { query = query.gte('population', filterPopLow) }
11if (filterPopHigh) { query = query.lt('population', filterPopHigh) }
12
13const { data, error } = 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);

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');