0
# Filtering and Conditions
1

2
Comprehensive filtering system with comparison operators, pattern matching, array operations, JSON queries, range operations, and full-text search capabilities.
3

4
## Capabilities
5

6
### Comparison Filters
7

8
Basic comparison operations for filtering data based on column values.
9

10
```typescript { .api }
11
/**
12
 * Match only rows where `column` is equal to `value`.
13
 * To check if the value of `column` is NULL, you should use `.is()` instead.
14
 * @param column - The column to filter on
15
 * @param value - The value to filter with
16
 */
17
eq<ColumnName extends string>(column: ColumnName, value: any): this;
18

19
/**
20
 * Match only rows where `column` is not equal to `value`
21
 * @param column - The column to filter on
22
 * @param value - The value to filter with
23
 */
24
neq<ColumnName extends string>(column: ColumnName, value: any): this;
25

26
/**
27
 * Match only rows where `column` is greater than `value`
28
 * @param column - The column to filter on
29
 * @param value - The value to filter with
30
 */
31
gt(column: string, value: unknown): this;
32

33
/**
34
 * Match only rows where `column` is greater than or equal to `value`
35
 * @param column - The column to filter on
36
 * @param value - The value to filter with
37
 */
38
gte(column: string, value: unknown): this;
39

40
/**
41
 * Match only rows where `column` is less than `value`
42
 * @param column - The column to filter on
43
 * @param value - The value to filter with
44
 */
45
lt(column: string, value: unknown): this;
46

47
/**
48
 * Match only rows where `column` is less than or equal to `value`
49
 * @param column - The column to filter on
50
 * @param value - The value to filter with
51
 */
52
lte(column: string, value: unknown): this;
53
```
54

55
**Usage Examples:**
56

57
```typescript
58
import { PostgrestClient } from "@supabase/postgrest-js";
59

60
const client = new PostgrestClient("https://api.example.com");
61

62
// Equal comparison
63
const { data } = await client
64
  .from("users")
65
  .select("*")
66
  .eq("active", true);
67

68
// Not equal
69
const { data: inactiveUsers } = await client
70
  .from("users")
71
  .select("*")
72
  .neq("status", "deleted");
73

74
// Numeric comparisons
75
const { data: adults } = await client
76
  .from("users")
77
  .select("*")
78
  .gte("age", 18);
79

80
const { data: youngAdults } = await client
81
  .from("users")
82
  .select("*")
83
  .gte("age", 18)
84
  .lt("age", 30);
85

86
// Date comparisons
87
const { data: recentPosts } = await client
88
  .from("posts")
89
  .select("*")
90
  .gt("created_at", "2023-01-01T00:00:00Z");
91
```
92

93
### Null Value Filtering
94

95
Special handling for NULL values in database columns.
96

97
```typescript { .api }
98
/**
99
 * Match only rows where `column` IS `value`.
100
 * For non-boolean columns, this is only relevant for checking if the value of `column` is NULL
101
 * by setting `value` to `null`. For boolean columns, you can also set `value` to `true` or `false`
102
 * and it will behave the same way as `.eq()`.
103
 * @param column - The column to filter on
104
 * @param value - The value to filter with
105
 */
106
is(column: string, value: boolean | null): this;
107
```
108

109
**Usage Examples:**
110

111
```typescript
112
// Check for NULL values
113
const { data: usersWithoutEmail } = await client
114
  .from("users")
115
  .select("*")
116
  .is("email", null);
117

118
// Check for non-NULL values (alternative to neq)
119
const { data: usersWithEmail } = await client
120
  .from("users")
121
  .select("*")
122
  .not("email", "is", null);
123

124
// Boolean filtering with is()
125
const { data: activeUsers } = await client
126
  .from("users")
127
  .select("*")
128
  .is("active", true);
129
```
130

131
### Array and Set Operations
132

133
Filtering based on array membership and set operations.
134

135
```typescript { .api }
136
/**
137
 * Match only rows where `column` is included in the `values` array
138
 * @param column - The column to filter on
139
 * @param values - The values array to filter with
140
 */
141
in<ColumnName extends string>(column: ColumnName, values: readonly any[]): this;
142
```
143

144
**Usage Examples:**
145

146
```typescript
147
// Filter by array of values
148
const { data: specificUsers } = await client
149
  .from("users")
150
  .select("*")
151
  .in("id", [1, 2, 3, 4, 5]);
152

153
// Filter by status array
154
const { data: publishedOrDrafts } = await client
155
  .from("posts")
156
  .select("*")
157
  .in("status", ["published", "draft"]);
158

159
// Filter by string array
160
const { data: specificRoles } = await client
161
  .from("users")
162
  .select("*")
163
  .in("role", ["admin", "moderator", "editor"]);
164
```
165

166
### Pattern Matching
167

168
String pattern matching with LIKE and ILIKE operators.
169

170
```typescript { .api }
171
/**
172
 * Match only rows where `column` matches `pattern` case-sensitively
173
 * @param column - The column to filter on
174
 * @param pattern - The pattern to match with
175
 */
176
like(column: string, pattern: string): this;
177

178
/**
179
 * Match only rows where `column` matches all of `patterns` case-sensitively
180
 * @param column - The column to filter on
181
 * @param patterns - The patterns to match with
182
 */
183
likeAllOf(column: string, patterns: readonly string[]): this;
184

185
/**
186
 * Match only rows where `column` matches any of `patterns` case-sensitively
187
 * @param column - The column to filter on
188
 * @param patterns - The patterns to match with
189
 */
190
likeAnyOf(column: string, patterns: readonly string[]): this;
191

192
/**
193
 * Match only rows where `column` matches `pattern` case-insensitively
194
 * @param column - The column to filter on
195
 * @param pattern - The pattern to match with
196
 */
197
ilike(column: string, pattern: string): this;
198

199
/**
200
 * Match only rows where `column` matches all of `patterns` case-insensitively
201
 * @param column - The column to filter on
202
 * @param patterns - The patterns to match with
203
 */
204
ilikeAllOf(column: string, patterns: readonly string[]): this;
205

206
/**
207
 * Match only rows where `column` matches any of `patterns` case-insensitively
208
 * @param column - The column to filter on
209
 * @param patterns - The patterns to match with
210
 */
211
ilikeAnyOf(column: string, patterns: readonly string[]): this;
212
```
213

214
**Usage Examples:**
215

216
```typescript
217
// Case-sensitive pattern matching
218
const { data: johnUsers } = await client
219
  .from("users")
220
  .select("*")
221
  .like("name", "John%"); // Names starting with "John"
222

223
// Case-insensitive pattern matching
224
const { data: emailUsers } = await client
225
  .from("users")
226
  .select("*")
227
  .ilike("email", "%@gmail.com"); // Gmail addresses
228

229
// Wildcard patterns
230
const { data: middleNameUsers } = await client
231
  .from("users")
232
  .select("*")
233
  .like("name", "% % %"); // Names with middle names
234

235
// Multiple pattern matching
236
const { data: techUsers } = await client
237
  .from("users")
238
  .select("*")
239
  .ilikeAnyOf("bio", ["%developer%", "%engineer%", "%programmer%"]);
240

241
// All patterns must match
242
const { data: seniorDevs } = await client
243
  .from("users")
244
  .select("*")
245
  .ilikeAllOf("bio", ["%senior%", "%developer%"]);
246
```
247

248
### JSON and Array Operations
249

250
Operations for JSONB, array, and range column types.
251

252
```typescript { .api }
253
/**
254
 * Only relevant for jsonb, array, and range columns. Match only rows where `column`
255
 * contains every element appearing in `value`
256
 * @param column - The jsonb, array, or range column to filter on
257
 * @param value - The jsonb, array, or range value to filter with
258
 */
259
contains(column: string, value: string | readonly unknown[] | Record<string, unknown>): this;
260

261
/**
262
 * Only relevant for jsonb, array, and range columns. Match only rows where every element
263
 * appearing in `column` is contained by `value`
264
 * @param column - The jsonb, array, or range column to filter on
265
 * @param value - The jsonb, array, or range value to filter with
266
 */
267
containedBy(column: string, value: string | readonly unknown[] | Record<string, unknown>): this;
268

269
/**
270
 * Only relevant for array and range columns. Match only rows where `column` and `value`
271
 * have an element in common
272
 * @param column - The array or range column to filter on
273
 * @param value - The array or range value to filter with
274
 */
275
overlaps(column: string, value: string | readonly unknown[]): this;
276
```
277

278
**Usage Examples:**
279

280
```typescript
281
// JSONB contains
282
const { data: usersWithSkills } = await client
283
  .from("users")
284
  .select("*")
285
  .contains("skills", { javascript: true, react: true });
286

287
// Array contains
288
const { data: usersWithTags } = await client
289
  .from("users")
290
  .select("*")
291
  .contains("tags", ["developer", "javascript"]);
292

293
// JSONB contained by
294
const { data: basicUsers } = await client
295
  .from("users")
296
  .select("*")
297
  .containedBy("skills", { 
298
    javascript: true, 
299
    react: true, 
300
    node: true, 
301
    python: true 
302
  });
303

304
// Array overlaps
305
const { data: overlappingUsers } = await client
306
  .from("users")
307
  .select("*")
308
  .overlaps("interests", ["coding", "music"]);
309

310
// JSONB path queries
311
const { data: specificConfig } = await client
312
  .from("apps")
313
  .select("*")
314
  .contains("config", { api: { timeout: 5000 } });
315
```
316

317
### Range Operations
318

319
Specialized operations for PostgreSQL range types.
320

321
```typescript { .api }
322
/**
323
 * Only relevant for range columns. Match only rows where every element in `column`
324
 * is greater than any element in `range`
325
 * @param column - The range column to filter on
326
 * @param range - The range to filter with
327
 */
328
rangeGt(column: string, range: string): this;
329

330
/**
331
 * Only relevant for range columns. Match only rows where every element in `column`
332
 * is either contained in `range` or greater than any element in `range`
333
 * @param column - The range column to filter on
334
 * @param range - The range to filter with
335
 */
336
rangeGte(column: string, range: string): this;
337

338
/**
339
 * Only relevant for range columns. Match only rows where every element in `column`
340
 * is less than any element in `range`
341
 * @param column - The range column to filter on
342
 * @param range - The range to filter with
343
 */
344
rangeLt(column: string, range: string): this;
345

346
/**
347
 * Only relevant for range columns. Match only rows where every element in `column`
348
 * is either contained in `range` or less than any element in `range`
349
 * @param column - The range column to filter on
350
 * @param range - The range to filter with
351
 */
352
rangeLte(column: string, range: string): this;
353

354
/**
355
 * Only relevant for range columns. Match only rows where `column` is mutually exclusive
356
 * to `range` and there can be no element between the two ranges
357
 * @param column - The range column to filter on
358
 * @param range - The range to filter with
359
 */
360
rangeAdjacent(column: string, range: string): this;
361
```
362

363
**Usage Examples:**
364

365
```typescript
366
// Date range comparisons
367
const { data: futureEvents } = await client
368
  .from("events")
369
  .select("*")
370
  .rangeGt("date_range", "[2023-01-01,2023-12-31]");
371

372
// Integer range operations
373
const { data: highPriceItems } = await client
374
  .from("products")
375
  .select("*")
376
  .rangeGte("price_range", "[100,500]");
377

378
// Adjacent ranges
379
const { data: adjacentSlots } = await client
380
  .from("time_slots")
381
  .select("*")
382
  .rangeAdjacent("time_range", "[09:00,12:00]");
383
```
384

385
### Full-Text Search
386

387
Advanced text search capabilities using PostgreSQL's full-text search features.
388

389
```typescript { .api }
390
/**
391
 * Only relevant for text and tsvector columns. Match only rows where `column` matches
392
 * the query string in `query`
393
 * @param column - The text or tsvector column to filter on
394
 * @param query - The query text to match with
395
 * @param options - Named parameters
396
 * @param options.config - Text search configuration
397
 * @param options.type - Search type
398
 */
399
textSearch(
400
  column: string,
401
  query: string,
402
  options?: {
403
    config?: string;
404
    type?: 'plain' | 'phrase' | 'websearch';
405
  }
406
): this;
407
```
408

409
**Usage Examples:**
410

411
```typescript
412
// Basic text search
413
const { data: searchResults } = await client
414
  .from("documents")
415
  .select("*")
416
  .textSearch("content", "postgresql database");
417

418
// Phrase search
419
const { data: exactMatches } = await client
420
  .from("articles")
421
  .select("*")
422
  .textSearch("title", "machine learning", { type: 'phrase' });
423

424
// Web-style search
425
const { data: webResults } = await client
426
  .from("posts")
427
  .select("*")
428
  .textSearch("content", "react OR vue", { type: 'websearch' });
429

430
// Custom text search configuration
431
const { data: customResults } = await client
432
  .from("documents")
433
  .select("*")
434
  .textSearch("content", "javascript", { config: 'english' });
435

436
// Search on tsvector column
437
const { data: vectorResults } = await client
438
  .from("indexed_documents")
439
  .select("*")
440
  .textSearch("search_vector", "typescript & react");
441
```
442

443
### Complex Filtering
444

445
Advanced filtering patterns and combinations.
446

447
```typescript { .api }
448
/**
449
 * Match only rows where each column in `query` keys is equal to its associated value.
450
 * Shorthand for multiple `.eq()`s
451
 * @param query - The object to filter with, with column names as keys mapped to their filter values
452
 */
453
match(query: Record<string, unknown>): this;
454

455
/**
456
 * Match only rows which doesn't satisfy the filter. Unlike most filters, `operator` and
457
 * `value` are used as-is and need to follow PostgREST syntax. You also need to make sure
458
 * they are properly sanitized.
459
 * @param column - The column to filter on
460
 * @param operator - The operator to be negated to filter with, following PostgREST syntax
461
 * @param value - The value to filter with, following PostgREST syntax
462
 */
463
not(column: string, operator: string, value: unknown): this;
464

465
/**
466
 * Match only rows which satisfy at least one of the filters. Unlike most filters, `filters`
467
 * is used as-is and needs to follow PostgREST syntax. You also need to make sure it's
468
 * properly sanitized. It's currently not possible to do an `.or()` filter across multiple tables.
469
 * @param filters - The filters to use, following PostgREST syntax
470
 * @param options - Named parameters
471
 * @param options.referencedTable - Reference table for OR operations
472
 */
473
or(filters: string, options?: { referencedTable?: string }): this;
474

475
/**
476
 * Match only rows which satisfy the filter. This is an escape hatch - you should use the
477
 * specific filter methods wherever possible. Unlike most filters, `operator` and `value`
478
 * are used as-is and need to follow PostgREST syntax. You also need to make sure they
479
 * are properly sanitized.
480
 * @param column - The column to filter on
481
 * @param operator - The operator to filter with, following PostgREST syntax
482
 * @param value - The value to filter with, following PostgREST syntax
483
 */
484
filter(column: string, operator: string, value: unknown): this;
485
```
486

487
**Usage Examples:**
488

489
```typescript
490
// Match multiple conditions (shorthand for multiple eq)
491
const { data: specificUsers } = await client
492
  .from("users")
493
  .select("*")
494
  .match({ 
495
    active: true, 
496
    role: "admin", 
497
    department: "engineering" 
498
  });
499

500
// NOT operations
501
const { data: notDeleted } = await client
502
  .from("posts")
503
  .select("*")
504
  .not("status", "eq", "deleted");
505

506
const { data: notInRange } = await client
507
  .from("products")
508
  .select("*")
509
  .not("price", "gte", 100)
510
  .not("price", "lte", 500);
511

512
// OR operations
513
const { data: publishedOrFeatured } = await client
514
  .from("posts")
515
  .select("*")
516
  .or("status.eq.published,featured.eq.true");
517

518
const { data: urgentTasks } = await client
519
  .from("tasks")
520
  .select("*")
521
  .or("priority.eq.high,due_date.lt.2023-12-01");
522

523
// Custom filter (escape hatch)
524
const { data: customFiltered } = await client
525
  .from("analytics")
526
  .select("*")
527
  .filter("views", "gte", 1000)
528
  .filter("created_at", "gte", "2023-01-01");
529

530
// Complex OR with referenced table
531
const { data: postsWithActiveAuthors } = await client
532
  .from("posts")
533
  .select(`
534
    *,
535
    users (
536
      id,
537
      name,
538
      active
539
    )
540
  `)
541
  .or("users.active.eq.true,users.role.eq.admin", { referencedTable: "users" });
542
```
543

544
### Filter Chaining
545

546
All filter operations can be chained together for complex query building:
547

548
```typescript
549
// Complex filtering example
550
const { data: complexResults } = await client
551
  .from("users")
552
  .select("*")
553
  .eq("active", true)
554
  .neq("status", "banned")
555
  .gte("age", 18)
556
  .in("role", ["user", "premium", "admin"])
557
  .ilike("name", "%john%")
558
  .not("email", "ilike", "%spam%")
559
  .or("verified.eq.true,created_at.gte.2023-01-01")
560
  .order("created_at", { ascending: false })
561
  .limit(50);
562
```