Tutorial: Building with CleaveDB

Filter & Sort Stages

The most common pipeline operations involve filtering incoming records, sorting them by specific dimensions, and projecting the exact attributes required by frontend applications or microservices.

1. Filtering with WHERE

The THEN WHERE stage discards any document that fails the specified criteria:

PIPE FROM emp THEN WHERE dept = "IT"

You can use standard comparison operators (=, !=, >, <, >=, <=) and join conditions using AND and OR.

2. Sorting with ARRANGED BY

CleaveQL uses the natural keywords ARRANGED BY <field> GOING UP (ascending) and GOING DOWN (descending) to sort records:

PIPE FROM emp
  THEN WHERE dept = "IT"
  THEN ARRANGED BY age GOING UP
  THEN SHOW name

This selects IT employees, sorts them from youngest to oldest, and projects only their names.

3. Windowing with LIMIT

To construct leaderboards or paginated views, add THEN LIMIT <n> directly following a sort stage:

PIPE FROM users
  THEN WHERE age > 20
  THEN ARRANGED BY age GOING DOWN
  THEN LIMIT 3
  THEN SHOW name, age

When a LIMIT follows a sort, CleaveDB uses a bounded min-heap in Rust to track only the top k candidates in O(N log k) time rather than allocating a full array sort.

4. Field projection with SHOW

The THEN SHOW stage filters output JSON objects down to only the explicitly listed keys:

PIPE FROM products THEN WHERE category = "Books" THEN SHOW title, price