Relational Queries (Joins) in Document Databases

A relational query in a document database is a join done with references and lookups instead of foreign keys — or avoided by embedding. That “or” is the whole subject: document databases give you three ways to relate data, and the design decision is when the join happens — at write time (embed), at query time in the server (lookup), or at query time in the application (references plus a follow-up fetch).

Key takeaways

The three ways to relate documents

// 1 · Embed — the "join" happened at write time
{ _id: 1, title: "Dune", author: { name: "Frank Herbert", born: 1920 } }

// 2 · Reference + $lookup — the join happens at query time, server-side
db.books.aggregate([
  { $lookup: { from: "authors", localField: "authorId",
               foreignField: "_id", as: "author" } },  // left outer join → array
  { $unwind: "$author" }                               // flatten → inner join
])

// 3 · Reference + application-side join — the ODM/SDK way (below)

The third path is where most application code lives — typed references walked eagerly in one request, so related documents arrive together without a pipeline:

// JavaScript / Node.js — Back4app JS SDK
// A join in a document database: Pointer + include, one request
const query = new Parse.Query('Comment');
query.equalTo('post', postPointer);   // Comment.post is a Pointer<Post>
query.include('author');              // "join" the author document in
const comments = await query.find();

const name = comments[0].get('author').get('username'); // already loaded — no N+1

// Flutter / Dart — Back4app Flutter SDK
// A join in a document database: Pointer + include, one request
final query = QueryBuilder<ParseObject>(ParseObject('Comment'))
  ..whereEqualTo('post', postPointer)
  ..includeObject(['author']); // "join" the author document in
final response = await query.query();

final author = (response.results!.first as ParseObject)
    .get<ParseObject>('author'); // already loaded — no N+1

// iOS / Swift — Back4app Swift SDK
// A join in a document database: Pointer + include, one request
let query = Comment.query("post" == postPointer)
  .include("author") // "join" the author document in
query.find { result in
  if case .success(let comments) = result {
    print(comments.first?.author?.username ?? "") // already loaded — no N+1
  }
}

// Android / Kotlin — Back4app Android SDK
// A join in a document database: Pointer + include, one request
val query = ParseQuery.getQuery<ParseObject>("Comment")
query.whereEqualTo("post", postPointer)
query.include("author") // "join" the author document in
query.findInBackground { comments, e ->
  if (e == null) {
    val name = comments[0].getParseObject("author")?.getString("username")
    Log.d("Comments", "by $name") // already loaded — no N+1
  }
}

Embed vs. reference: the decision

The cardinality tiers give the same table as rules of thumb: one-to-few embeds the array, one-to-many references by ID, one-to-enormous flips direction — the child stores the parent reference, because no parent document can hold an ever-growing array. Which points at the two anti-patterns behind most document-modeling incidents: unbounded arrays and the 16MB document cap they eventually threaten. The standard fixes — reference instead, embed a bounded subset (newest N with an overflow collection), or bucket children into grouped documents — are all versions of “stop the document growing.”

The lookup, honestly

$lookup is a real join with two honest asterisks. Shape: it returns matches as an embedded array per input document — $unwind flattens it to rows and, without preserve-empty semantics, converts left-join to inner-join behavior. Performance: document engines execute nested-loop joins only, and public benchmarks are blunt — joining a million documents took tens of seconds with indexes, against half a second for the embedded equivalent. The operational rules that follow: always index the foreign field (without it, every input document triggers a collection scan), use lookups for per-request joins over a handful of documents, and never build analytics fan-outs on them — that workload belongs to a warehouse or a relational engine.

The N+1 problem, and its four exits

The classic bug: one query for N parents, then a loop issuing one query per parent for its children — N+1 round trips that scale with the page size. The exits, best first: batch — collect the parent IDs and fetch all children in one contained-in query (good ODMs’ populate does this for you); include — SDK-level eager loading that returns referenced documents in the same request, as in the tabs above; lookup — one server-side pipeline; embed — the join stops existing. What turns N+1 from bug to architecture is not noticing it: it ships fast on ten test records and melts under a thousand — the full pathology has its own entry in this glossary’s registry.

Common use cases

• Content with authorship. Posts, comments, authors — references with includes for the list views, embeds for the display-only snapshot fields.
• Catalogs and orders. The canonical extended reference: an order embeds the product name and price as-sold (immutable snapshot) plus a reference to the live product.
• Activity and event streams. One-to-enormous — child documents holding parent references, never arrays on the parent.
• User profiles. The embed showcase: addresses, preferences, settings — read together, bounded, owned.
• Social graphs. Many-to-many reference arrays — and the honest signal that, past a point, this shape wants a relational or graph engine.

Embed, reference, or switch engines? A decision matrix

The third column is the section vendor pages won’t write: if every screen needs three lookups and referential integrity keeps you up at night, the data is asking for tables — document models win when access patterns are known and hierarchical, not as a universal replacement.

Limitations and trade-offs

• Denormalization is a debt instrument. Duplicated fields kill joins but must be repaid on update — fan-out writes, staleness windows, and consistency code (transactions or change-stream triggers) are the interest.
• No foreign keys, no safety net. References don’t enforce existence; deleting an author orphans book references silently unless your platform or code cleans up.
• Lookups don’t optimize. No join reordering, no hash strategies — pipeline order is your query plan.
• Migrations between shapes are real projects. Embedded-to-referenced (the growing-array rescue) means backfilling collections and rewriting queries — model for tomorrow’s cardinality, not today’s.
• The 16MB cap is a cliff, not a warning. Growth patterns that approach it degrade performance long before they hit it.

Relational queries on Back4app

Back4app is an open-source Backend-as-a-Service (BaaS) platform that combines a managed database, auto-generated REST and GraphQL APIs, authentication, file storage, and Cloud Code serverless functions. Its document database speaks a relational vocabulary by design: Pointers declare one-to-many edges, Relations handle many-to-many, and include() walks the edges in a single request — the N+1 exit built into the SDK, as the code tabs above show. The auto-generated GraphQL API nests related objects in one query for free, and deletes can cascade through Cloud Code triggers — the referential-integrity safety net document engines leave out.