Filtering Data

Select rows based on conditions using the filter() method.

Basic Filtering

Single Condition

import { col } from "molniya";

// Greater than
df.filter(col("age").gt(18))

// Greater than or equal
df.filter(col("score").gte(60))

// Less than
df.filter(col("price").lt(100))

// Less than or equal
df.filter(col("quantity").lte(10))

// Equal
df.filter(col("status").eq("active"))

// Not equal
df.filter(col("type").neq("deleted"))

Null Checks

// Is null
df.filter(col("email").isNull())

// Is not null
df.filter(col("email").isNotNull())

Combining Conditions

AND (All conditions must match)

import { and, col } from "molniya";

// Method 1: Using and()
df.filter(and(
  col("age").gte(18),
  col("age").lt(65),
  col("active").eq(true)
))

// Method 2: Chaining filters (more efficient)
df
  .filter(col("age").gte(18))
  .filter(col("age").lt(65))
  .filter(col("active").eq(true))

Chaining vs AND

Chaining filter() calls is often more readable and can be more efficient as it allows the optimizer to process conditions incrementally.

OR (Any condition can match)

import { or, col } from "molniya";

df.filter(or(
  col("status").eq("pending"),
  col("status").eq("processing")
))

NOT (Negate a condition)

import { not, col } from "molniya";

// Exclude specific status
df.filter(not(col("status").eq("deleted")))

// Equivalent to:
df.filter(col("status").neq("deleted"))

Complex Combinations

import { and, or, col } from "molniya";

// (age >= 18 AND age < 65) OR (type === 'senior' AND age >= 65)
df.filter(or(
  and(
    col("age").gte(18),
    col("age").lt(65)
  ),
  and(
    col("type").eq("senior"),
    col("age").gte(65)
  )
))

Range Filtering

Between (Inclusive)

// Value between low and high (inclusive)
df.filter(col("age").between(18, 65))

// Equivalent to:
df.filter(and(
  col("age").gte(18),
  col("age").lte(65)
))

String Filtering

Contains

// String contains substring
df.filter(col("email").contains("@company.com"))

Starts With / Ends With

// Starts with prefix
df.filter(col("name").startsWith("John"))

// Ends with suffix
df.filter(col("email").endsWith("@gmail.com"))

String Performance

String operations are case-sensitive and work on dictionary-encoded strings. For large datasets, consider normalizing case before filtering.

Practical Examples

Filter by Date Range

// Assuming timestamps as milliseconds
df.filter(and(
  col("created_at").gte(1704067200000),  // Jan 1, 2024
  col("created_at").lt(1735689600000)     // Jan 1, 2025
))

Filter by Multiple Categories

const allowedCategories = ["electronics", "books", "clothing"];

// Build OR condition dynamically
const condition = allowedCategories
  .map(cat => col("category").eq(cat))
  .reduce((acc, curr) => or(acc, curr));

df.filter(condition)

Filter with Calculated Values

import { col, lit } from "molniya";

// Filter where price * quantity > 1000
df
  .withColumn("total", col("price").mul(col("quantity")))
  .filter(col("total").gt(1000))

Excluding Specific Values

const excludedStatuses = ["deleted", "archived", "banned"];

// Build NOT(OR) condition
const excludeCondition = excludedStatuses
  .map(status => col("status").eq(status))
  .reduce((acc, curr) => or(acc, curr));

df.filter(not(excludeCondition))

// Or chain neq filters
df
  .filter(col("status").neq("deleted"))
  .filter(col("status").neq("archived"))
  .filter(col("status").neq("banned"))

Common Patterns

Active Records Only

const active = df.filter(and(
  col("deleted_at").isNull(),
  col("status").eq("active")
))

Recent Records

const oneWeekAgo = Date.now() - 7 * 24 * 60 * 60 * 1000;
const recent = df.filter(col("created_at").gte(oneWeekAgo))

Non-Empty Strings

const valid = df.filter(and(
  col("name").isNotNull(),
  col("name").neq("")
))

Performance Tips

1. Filter early: Apply filters as soon as possible to reduce data volume
2. Use simple comparisons: eq(), gt(), etc. are faster than string operations
3. Chain filters: Multiple chained filters can be more efficient than complex AND expressions
4. Avoid redundant filters: Don't filter the same condition twice

// Good: Filter early, then process
const result = await df
  .filter(col("year").eq(2024))      // Filter first
  .withColumn("bonus", ...)           // Then transform
  .sort("amount")
  .limit(100)
  .collect();

// Less efficient: Transform everything, then filter
const result = await df
  .withColumn("bonus", ...)           // Processes all rows
  .filter(col("year").eq(2024))      // Then filters
  .collect();