Joins (Lookup)

PocketDB supports all four join types via the $lookup stage in aggregation pipelines, enabling you to combine documents from multiple collections.

Installation

Add PocketDB to your Cargo.toml:

[dependencies]
pocketdb = "0.1"

Or via the command line:

cargo add pocketdb

Lookup Stage

Basic Syntax

use pocketdb::query::relationship_lookup::LookupStage;

let stage = LookupStage::new(
    "orders",      // foreign collection
    "_id_ref",     // local field (in source)
    "user_id",     // foreign field (in target)
    "user_orders", // output field name
);

Setting Join Type

use pocketdb::query::relationship_lookup::{LookupStage, JoinType};

let stage = LookupStage::new("orders", "user_id", "customer_id", "purchases")
    .with_join_type(JoinType::Inner);

Join Types

Left Outer Join (Default)

Returns all source documents. If a match exists in the target, it's included; otherwise the output field is an empty array.

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

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

// Alice (has orders) -> { "name": "Alice", "orders": [...] }
// Bob (no orders)    -> { "name": "Bob", "orders": [] }

Inner Join

Returns only source documents that have matching documents in the target.

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

// Alice (has orders) -> { "name": "Alice", "orders": [...] }
// Bob (no orders)    -> not included

Right Outer Join

Returns all target documents. If a match exists in the source, it's included.

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

// All orders are included, with user data when available

Full Outer Join

Returns all documents from both collections.

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

// All users + all orders, with matches where they exist

Complete Example

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

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

    // Set up users
    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" })?;

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

    // Left join: all users with their orders
    let results = users.aggregate()
        .add_stage(PipelineStage::Lookup(
            LookupStage::new("orders", "name", "user_id", "purchases")
                .with_join_type(JoinType::LeftOuter)
        ))
        .execute(&users.all_documents());

    for doc in &results {
        let name = doc.get("name").and_then(|v| v.as_str()).unwrap_or("?");
        let orders = doc.get("purchases")
            .and_then(|v| v.as_array())
            .map(|a| a.len())
            .unwrap_or(0);
        println!("{}: {} orders", name, orders);
    }
    // Alice: 2 orders
    // Bob: 1 orders
    // Charlie: 0 orders

    Ok(())
}

Join Result Structure

Each source document gets an additional array field containing matched documents from the foreign collection:

{
  "_id": "...",
  "name": "Alice",
  "email": "alice@example.com",
  "purchases": [
    { "item": "Laptop", "qty": 1 },
    { "item": "Mouse", "qty": 2 }
  ]
}

Performance Tips

1. Index the foreign field — create an index on the field being joined on in the target collection
2. Filter before joining — use $match to reduce the input set before $lookup
3. Use inner joins when you only need matching documents — they're more efficient
4. Limit result sets — combine with $limit for pagination

// Efficient: filter first, then join
let results = users.aggregate()
    .match_(filter! { "active" => == true })  // Reduce input first
    .add_stage(PipelineStage::Lookup(
        LookupStage::new("orders", "name", "user_id", "orders")
    ))
    .limit(100)  // Limit results
    .execute(&users.all_documents());