Join (Lookup) Examples

This guide demonstrates all four join types using the $lookup aggregation stage.

Setup

use pocketdb::{Database, doc};
use pocketdb::query::{AggregationPipeline, PipelineStage};
use pocketdb::query::relationship_lookup::{LookupStage, JoinType};

fn main() -> pocketdb::Result<()> {
    let db = Database::open("joins_example.pdb")?;

    // Users collection
    let users = db.collection("users")?;
    users.insert(doc! { "name" => "Alice", "email" => "alice@example.com" })?;
    users.insert(doc! { "name" => "Bob", "email" => "bob@example.com" })?;
    users.insert(doc! { "name" => "Charlie", "email" => "charlie@example.com" })?;

    // Orders collection
    let orders = db.collection("orders")?;
    orders.insert(doc! { "user_id" => "Alice", "item" => "Laptop", "qty" => 1, "amount" => 999 })?;
    orders.insert(doc! { "user_id" => "Alice", "item" => "Mouse", "qty" => 2, "amount" => 59 })?;
    orders.insert(doc! { "user_id" => "Bob", "item" => "Keyboard", "qty" => 1, "amount" => 149 })?;

    // ... examples ...

    Ok(())
}

Left Outer Join (Default)

Returns all users, with their orders (or empty array if no orders):

let results = users.aggregate()
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "purchases")
    ))
    .execute(&users.all_documents());

for doc in &results {
    let name = doc.get("name").unwrap();
    let purchases = doc.get("purchases").and_then(|v| v.as_array());
    println!("{}: {} orders", name, purchases.map_or(0, |a| a.len()));
}

// Alice: 2 orders
// Bob: 1 orders
// Charlie: 0 orders

Inner Join

Returns only users who have orders:

let results = users.aggregate()
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "purchases")
            .with_join_type(JoinType::Inner)
    ))
    .execute(&users.all_documents());

for doc in &results {
    let name = doc.get("name").unwrap();
    println!("{}: has orders", name);
}

// Alice: has orders
// Bob: has orders
// Charlie is NOT included

Right Outer Join

Returns all orders, with user info when available:

let results = orders.aggregate()
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("users", "user_id", "name", "customer")
            .with_join_type(JoinType::RightOuter)
    ))
    .execute(&orders.all_documents());

for doc in &results {
    let item = doc.get("item").unwrap();
    let customer = doc.get("customer").and_then(|v| v.as_array());
    println!("{}: customer={:?}", item, customer);
}

Full Outer Join

Returns all users AND all orders:

let results = users.aggregate()
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "purchases")
            .with_join_type(JoinType::FullOuter)
    ))
    .execute(&users.all_documents());

for doc in &results {
    let name = doc.get("name").unwrap();
    let purchases = doc.get("purchases").and_then(|v| v.as_array());
    println!("{}: {} orders", name, purchases.map_or(0, |a| a.len()));
}

// Alice: 2 orders
// Bob: 1 orders
// Charlie: 0 orders
// (Orders without matching users would also appear with empty data)

Join with Filtering

Filter before joining for better performance:

use pocketdb::filter;

// Only active users with their orders
let results = users.aggregate()
    .match_(filter! { "active" => == true })
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "purchases")
    ))
    .execute(&users.all_documents());

Join with Sorting

use pocketdb::query::SortField;

// Join and sort by number of orders
let results = users.aggregate()
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "purchases")
    ))
    .sort(vec![SortField::desc("name")])
    .execute(&users.all_documents());

Join with Limit

// Top 10 users with their orders
let results = users.aggregate()
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "purchases")
    ))
    .limit(10)
    .execute(&users.all_documents());

Real-World Example: E-Commerce

use pocketdb::{Database, doc, filter, PocketValue};
use pocketdb::query::{
    AggregationPipeline, PipelineStage, SortField,
    Accumulator, AccumulatorOp, Projection,
};
use pocketdb::query::relationship_lookup::{LookupStage, JoinType};

fn generate_sales_report(db: &Database) -> pocketdb::Result<()> {
    let users = db.collection("users")?;
    let orders = db.collection("orders")?;

    // Join users with orders, group by user, sum order amounts
    let results = users.aggregate()
        // Only active users
        .match_(filter! { "active" => == true })
        // Join with orders
        .add_stage(PipelineStage::Lookup(
            LookupStage::new("orders", "name", "user_id", "purchases")
        ))
        // Unwind the purchases array
        .unwind("purchases")
        // Group by user, calculate totals
        .add_stage(PipelineStage::Group {
            id: Some("name".to_string()),
            accumulators: vec![
                Accumulator {
                    field: "total_spent".to_string(),
                    op: AccumulatorOp::Sum,
                    input_field: "purchases.amount".to_string(),
                },
                Accumulator {
                    field: "order_count".to_string(),
                    op: AccumulatorOp::Count,
                    input_field: "purchases.amount".to_string(),
                },
            ],
        })
        // Sort by total spent
        .sort(vec![SortField::desc("total_spent")])
        // Top 10 customers
        .limit(10)
        // Only show name and totals
        .project(Projection::include(&["name", "total_spent", "order_count"]))
        .execute(&users.all_documents());

    println!("Top Customers:");
    println!("{:<15} {:<12} {:<10}", "Name", "Total Spent", "Orders");
    println!("{}", "-".repeat(37));

    for doc in &results {
        let name = doc.get("name").and_then(|v| v.as_str()).unwrap_or("?");
        let total = doc.get("total_spent").and_then(|v| v.as_f64()).unwrap_or(0.0);
        let count = doc.get("order_count").and_then(|v| v.as_i64()).unwrap_or(0);
        println!("{:<15} ${:<11.2} {:<10}", name, total, count);
    }

    Ok(())
}

Performance Tips

1. Index the join field — create an index on the field being joined in the target collection:

   orders.create_index(IndexSpec::single("user_id"))?;

2. Filter before joining — reduce input set with $match:

   users.aggregate()
       .match_(filter! { "active" => == true })  // Filter first
       .add_stage(PipelineStage::Lookup(...))     // Then join

3. Use inner joins when possible — they skip non-matching documents:

   LookupStage::new("orders", "name", "user_id", "purchases")
       .with_join_type(JoinType::Inner)

4. Limit results — combine with $limit for pagination:

   users.aggregate()
       .add_stage(PipelineStage::Lookup(...))
       .limit(100)  // Only return 100 results