Tutorial: Building with CleaveDB

Nested SCOOP Queries

Sometimes the condition for one bucket depends on what another bucket says. Rather than fetch both collections and join them in application code, SCOOP can evaluate a second SCOOP inside a condition. The inner query supplies the values the outer query should use, keeping the relationship between the two steps close to the data request.

Use a subquery to define the match set

Consider orders that store a customer ID, while customer status lives in the users bucket. First select the IDs of active users, then use that result to decide which orders qualify. The nested statement sits inside IN (...), where its projected gid values become the candidate IDs for the outer query.

SCOOP orders WHERE customer_id IN (
  SCOOP users WHOSE status IS "active" YIELD gid
)

Read it from the inside out. The inner query selects users whose status is active and yields only their document IDs. The outer query keeps orders whose customer_id appears in that ID set. The application can ask one coherent question—orders for active users—without manually passing the intermediate IDs between separate reads.

Keep nested queries readable

Nested queries are easiest to maintain when each level has one clear role: the inner SCOOP identifies a set of values, and the outer SCOOP retrieves the documents that refer to those values. Project only the field needed by the outer condition. If the logic becomes hard to explain in one sentence, split it into named application steps or reconsider whether a direct relationship model would express it more clearly.