Tutorial: Building with CleaveDB

Retrieving Documents (SCOOP) — Overview

SCOOP is CleaveQL’s structured read command: it retrieves documents from a bucket, applies conditions to their fields, and shapes the result for the application. Think of it as asking a well-organized archive a precise question. You choose the collection, describe what qualifies, then decide whether you need complete documents, a few selected fields, or a compact summary.

The closest SQL comparison is SELECT. A SQL query selects rows from a table; a SCOOP query selects JSON documents from a bucket. The familiar ideas remain—filters, ordering, projections, limits, and summaries—but CleaveQL speaks in buckets and flexible documents. Documents in a bucket need not all have identical fields, so the query can focus on the fields that matter to the question at hand.

Build a query from the question

Begin with the bucket, such as users or products. Use WHERE for comparisons and combined conditions, WHOSE for a direct exact-field match, or MATCHING when the desired document should match a JSON shape. A request can be as broad as SCOOP EVERYTHING FROM users, or as focused as SCOOP users WHERE age > 21. The command stays declarative: it describes the records you want rather than a step-by-step procedure for finding them.

Once the matching documents are clear, decide what the caller needs back. YIELD selects fields, ARRANGED BY sorts the result, and LIMIT caps its size. These modifiers can be combined: a catalog can return the names and email addresses of users in a chosen order, or show only the first page of products that match a filter. The goal is a result shaped for its destination, not extra data the screen will immediately discard.

Use summaries when a list is not the answer

SCOOP can answer common analytical questions directly. THE TALLY counts documents; ONLY UNIQUE finds distinct field values; THE HIGHEST and THE LOWEST rank documents by a field; THE FIRST and THE LAST return a chosen number from the result; and THE TOTAL with GROUPED BY can sum values by category. A dashboard that needs an employee count or revenue by region can request that answer without asking the application to collect every source document first.

Keep structured search expressive

A SCOOP query can also match a structured JSON template with MATCHING, nest another SCOOP inside a condition, or use the explicit MEANING modifier when semantic similarity needs to work alongside structured filters. AS OF asks for a historical view. Together, these options let an application build a query around its actual task while keeping the main shape readable: choose a bucket, state the criteria, and request the result in the form that will be useful.

Retrieval follows the same tenant and access boundaries as the rest of CleaveDB. The authenticated tenant scopes the bucket, read rules determine which documents are visible, and field masks can remove protected values from the returned JSON. A query describes what the application wants; CleaveDB still decides what that caller is permitted to receive.

Explore the SCOOP topics

Each lesson focuses on one part of structured retrieval, with separate CleaveQL examples and guidance on when to use it:

• Structured retrieval: choose a bucket and fetch a document or collection.
• Filtering results: compare fields, combine conditions, and match JSON shapes.
• Sorting and choosing fields: order, limit, and project the returned documents.
• Summaries and totals: count, rank, find unique values, and aggregate by category.
• Nested SCOOP queries: use one structured query inside another.
• Meaning search: add semantic matching to a structured query.
• Historical reads: query data as of an earlier point in time.