Tutorial: Building with CleaveDB

Sub-query Injection

In real-world applications, the documents you want to update—or the values you want to assign to them—often depend on data located in another bucket. CleaveQL allows you to embed SCOOP queries directly inside a CHANGE statement, eliminating the need to write multi-step orchestration logic in your application layer.

Targeting documents via sub-query

When you need to update documents based on properties stored elsewhere, embed a nested SCOOP inside your WHERE condition:

CHANGE orders SET status = "flagged" WHERE customer_id IN (
  SCOOP users WHOSE status IS "banned" YIELD gid
)

The inner query runs first, yielding the document IDs (gid) of all banned users. The outer CHANGE statement then matches orders whose customer_id is found in that list and updates their status to "flagged" in a single coordinated operation.

Computing field values from sub-queries

You can also use a nested SCOOP expression directly in the SET clause to populate a computed or aggregate value:

CHANGE accounts "acc_901" SET total_orders = (
  SCOOP orders WHERE account_id = "acc_901" YIELD THE TALLY
)

Here, CleaveDB evaluates the count of matching orders directly in the engine and injects the result into the total_orders attribute of account "acc_901" without shipping intermediate counts back to your web service.

Best practices for injected sub-queries

To maintain optimal performance when using sub-queries inside updates:

• Index the join fields: Ensure that foreign-key fields like customer_id or account_id have index definitions on their respective buckets.
• Narrow the inner query: Always use selective filters on the inner SCOOP query to keep the set of candidate IDs as compact as possible.
• Yield only required fields: Use YIELD gid or target single scalar aggregations to avoid transferring extraneous document payloads in memory.