Database

Lookup

The lookup method performs a foreign key join, replacing a field in your documents with the referenced data from another table. It is available on both Stream<T> and Datum<T>.

Parameters

Parameter	Type	Description
`other`	`Selection<U>`	The table containing the referenced documents
`localKey`	`keyof T`	The field in the current document holding the foreign key
`otherKey`	`keyof U`	The field in the other table to match against

Behavior

The lookup method replaces the local key field with the populated data:

If the local key contains a single value, the field becomes a single document from the other table.
If the local key contains an array of values, the field becomes an array of documents from the other table.
TypeScript automatically tracks the type transformation.

One-to-One Relationship

Replace a single foreign key with the referenced document:

import { Schema } from "@antelopejs/interface-database";

interface Post {
  _id: string;
  title: string;
  authorId: string;
}

interface User {
  _id: string;
  name: string;
  email: string;
}

const schema = Schema.get("blog")!;
const db = schema.instance();

const postsWithAuthors = await db
  .table<Post>("posts")
  .lookup(db.table<User>("users"), "authorId", "_id");

// Result type: { _id: string, title: string, authorId: User }
for (const post of postsWithAuthors) {
  console.log(`${post.title} by ${post.authorId.name}`);
}

One-to-Many Relationship

Replace an array of foreign keys with an array of documents:

interface Order {
  _id: string;
  customerId: string;
  productIds: string[];
  total: number;
}

interface Product {
  _id: string;
  name: string;
  price: number;
}

const ordersWithProducts = await db
  .table<Order>("orders")
  .lookup(db.table<Product>("products"), "productIds", "_id");

// Result type: { ..., productIds: Product[] }
for (const order of ordersWithProducts) {
  for (const product of order.productIds) {
    console.log(`  - ${product.name}: $${product.price}`);
  }
}

Combine with Other Operations

Lookup works seamlessly with filters, sorting, and other stream operations:

// Filter before lookup
const recentWithCustomers = await db
  .table("orders")
  .filter((order) => order.key("createdAt").gt(new Date("2024-01-01")))
  .lookup(db.table("customers"), "customerId", "_id");

// Lookup on a single document
const post = await db
  .table<Post>("posts")
  .get("post-1")
  .lookup(db.table<User>("users"), "authorId", "_id");

Lookup vs Join

Use `lookup` when:

You have a straightforward foreign key relationship.
You want the referenced data to replace the foreign key field.
You are working with one-to-one or one-to-many relationships.

Use `join` or `joinInner` when:

You need custom join predicates beyond simple equality.
You want to keep both original and joined data as separate fields.
You need to transform the result into a custom structure.

Comparison

// Lookup: replaces the field
const withLookup = await db
  .table("orders")
  .lookup(db.table("customers"), "customerId", "_id");
// order.customerId is now the full customer object

// Join: custom output structure
const withJoin = await db
  .table("orders")
  .joinInner(
    db.table("customers"),
    (order, customer) => order.key("customerId").eq(customer.key("_id")),
    (order, customer) => ({
      orderId: order.key("_id"),
      orderTotal: order.key("total"),
      customerName: customer.key("name"),
    }),
  );
// Result is a custom object with selected fields from both tables

Filtering and Querying

This guide covers techniques for selecting, comparing, and transforming documents using AQL's filter and ValueProxy capabilities.

Results

AQL operations return structured result values that communicate what happened during execution. This section covers the result types produced by write operations and change feeds.