Relations
Link documents across databases and connections, where MongoDB's $lookup can't reach. Mongo Studio joins them for you, page by page, and shows every document's related documents: counts, sums and orphans included.
What it is
Real systems split their data. Orders live in one database, the shipments for them in another, often behind another connection with its own credentials. MongoDB's $lookup only joins collections of the same database, so answering "which orders were shipped twice?" or "which orders never shipped?" usually means exporting both sides and matching them by hand.
A relation is a saved link between two collections: "an order's shipments are the shipments whose orderRef is the order's orderId". Once it's saved, Mongo Studio joins the two sides itself, on the connections you already use, and shows the result where you work:
- in a document's Related section, with the related documents one click away;
- as Related columns in the grid (counts, conditional counts, sums), with totals for the page;
- as a filter for orphans: the rows that have none, or not exactly one.
It works both ways: from an order you see its shipments, and from a shipment its order.
The running example
This page uses one setup throughout: a store platform where each store keeps its own orders, and a separate logistics service ships them.
- Shop (prod), a connection to the
shopdatabase. Each store has its own collection:berlin_orders,lisbon_orders, and hundreds more. An order has anorderIdand acustomerId, both UUIDs stored as BSON binary, plus itsstatusandtotal. - Logistics (prod), another connection, to the
logisticsdatabase on the same cluster. One sharedshipmentscollection holds every store's shipments, with astorefield and the order's ids as text:orderRefandcustomerRef. When an order ships in several parcels, the extra ones get a suffix:"<uuid>-2". Shipments from before those fields existed only havelegacyRef: "<customerId>-<orderId>".
Different databases, different connections, ids stored as binary on one side and text on the other, a suffix sometimes, an older format for old rows. All of it fits in one relation.
Concepts
| Term | What it means |
|---|---|
| From and To | The two sides. The From side is where you usually start (orders); the To side is what you look up (shipments). Each side is a connection, a database and a collection. |
{tenant} pattern | A collection name with a varying part: {tenant}_orders covers berlin_orders, lisbon_orders and the rest. The part that varies is the tenant (here, the store), and can be used in filters. |
| Link | A pair of fields that must match: orderId on the From side, orderRef on the To side. A link can also be built from several fields, like {customerId}-{orderId}. |
| Operator | equals; starts with, when the To value can carry a suffix (<uuid>-2); is in the array, when the From field is a list of ids. |
| Compare as | How values of different types meet: As stored, UUID (Binary 4) ↔ text, GUID .NET (Binary 3) ↔ text, ObjectId ↔ text, Number ↔ text. |
| Rule | A set of links that must all match. A relation can have several rules: they're tried in order, and a later rule only runs for the documents earlier rules found nothing for. That's how old and new formats live in one relation. |
| Tenant filter | A condition that always applies to the To side, like store = {tenant}: from berlin_orders, only Berlin's shipments can match. Going back, it's how a shipment finds its collection. |
| Names | What each side calls the other: from an order, "shipments"; from a shipment, "order". They label the Related section and the columns. |
| Draft | A relation an AI agent proposed over MCP. Nothing uses it until you open it and save it. |
How the join works
Nothing is copied and nothing is precomputed. When a page of documents is on screen, Mongo Studio finds their related documents with a few targeted queries:
- The page. Only the documents on screen are joined: 50 rows means 50 rows' worth of lookups, however big the collections are.
- The tenant comes from the collection's name, matched against the pattern.
- The other connection is the one you picked for this connection, or the one to the same cluster. See Connections and clusters.
- The other collection follows from the pattern and the tenant. It's never searched for: with hundreds or thousands of collections, the app doesn't list them to find it.
- Rule 1 runs as one query for the whole page, values converted as the links say (UUIDs to text here), the tenant filter included. It has a 15-second limit and reads at most 5,000 documents.
- Rule 2 runs only for the rows rule 1 found nothing for, and so on for each rule.
- The results are matched back to each row by the values they hold, and counted, summed or listed for the views.
Create a relation
Start wherever you notice the link:
- Right-click a collection in the Explorer › Relate to another collection…
- Right-click a field in a document › Relate “field” to another collection… That field is ready to link.
- In the results, Related › Relate to another collection…
- The Relations tool window (the link icon on the left edge) › +, or New relation in the command palette (Ctrl K).
It opens in its own tab, with the collection you started from as the From side. When other collections share its name's ending, it becomes a pattern: starting from berlin_orders gives {tenant}_orders.
- Pick the To side in Connections: the database and collection (both searchable), and which connection it lives on, usually The one on the same cluster.
- Link the fields. Click a field on the left, then its partner on the right. Or press Link on a suggestion: Mongo Studio compares samples of both sides and proposes the pairs that match, with how many sampled values did.
- Check each wire's chip. It shows the operator and the comparison; click it to change either, or to remove the link.
- Add the tenant filter under Tenant › Add a filter, if the To side is shared between tenants:
store = {tenant}. - Read the preview. Every sample document shows what it finds. None found and Several filter it to the surprises.
- Add a rule for an older format, under Rules › Add an alternative rule. A link's From side can be a template like
{customerId}-{orderId}. - Name the two sides under Names, run the Check, and Save.
The editor
- The sample document from the From side. Its collection is searchable among every tenant the pattern matches; the arrows step through the sample.
- The document it finds on the To side, and how many it found. With several, the arrows step through them. Fields the tenant filter covers show its value in green.
- The wires join linked fields. Each chip shows how they compare; click it to change it.
- Suggestions, from samples of both sides. Link adds one.
- The preview: every sample document and what it finds, with the rule that found it when it isn't the first.
- The settings, one line each until opened: Connections (and pairs), Tenant, Rules, Names, and Check.
- Save, and Delete; a draft has Save relation and Discard draft, with the agent's reason in a banner.
The check
Check › Test samples both sides (a few tenants of a pattern, a couple hundred documents) and reports how many documents each rule finds, how many find nothing in each direction, and whether the fields are indexed:
✓ Sampled 61 orders in the 5 busiest of 20 tenants picked at random (of 340):
rule 1 matched 39 · rule 2 matched 18 more · 4 have none (1–3 each, avg 1.3)
✓ Sampled 200 shipments in 5 tenants: 197 have an order · 3 don't
✓ Index store_1_customerRef_1 on shipments (rule 1)
! No index on shipments.legacyRef: rule 2 scans the collection.A missing index comes with Create index…. It opens a console with the createIndex script, for a pattern a loop over its collections, and runs nothing until you press Run.
Use it
The Related section
Select a document in the grid: under its fields, Related lists each relation it takes part in, with the count and where the other side lives. Expand one to see the related documents, then Open all in a tab to get them as a filtered query on the other connection.
Related columns
The Related button above the results adds columns, joined for the rows on the page:
- Count of shipments, and Count where status = DELIVERED for any value it sees;
- Sum of weightKg for a numeric field;
- Whether it has any shipments; and, for a relation that finds one document, any of its fields (order · status).
The footer totals each column for the page: for instance 58 shipments, 55 delivered, 3 returned, 47 orders shipped.
Orphans
Click a column's chip above the grid to show only the rows where it isn't 1, or, for an exists column, where the other side is missing. From the orders, shipments · exists filtered this way lists the orders that never shipped; from the shipments, order · exists lists the shipments without an order.
Connections and clusters
The To side's connection is set in Connections:
- The one on the same cluster: a connection whose connection string has the same hosts as the From one. No folders or naming needed:
Shop (prod)andLogistics (prod)pair because both point at the same servers. With several candidates, the one whose default database is the To database wins; a connection alone on its cluster pairs with itself. - The From connection itself, when both databases are on one connection.
- A fixed connection, picked by name.
Pairs overrides it per From connection: pick the To connection by hand, or None to skip one. If the other connection isn't open, the Related section offers Connect; nothing is read until it is.
Relations over MCP
An AI agent connected through the MCP server often knows how two systems link, from their code. It can propose the relation; you decide whether it's kept.
| Tool | What the agent gets |
|---|---|
list_relations, get_relation | Your relations and drafts, in short or in full. |
suggest_relation_links | Field pairs that link two collections, with match rates from samples. No document values. |
create_relation, update_relation | Save or change a draft, with a reason you read in the banner. Agents only change drafts. |
test_relation | The check report. |
find_related | Joins through a relation: per document, the count, the rule that found them, aggregates and up to five related documents. |
delete_relation | Deletes a draft an agent created. |
A relation as the agent sends it, for the running example:
{
"name": "Order shipments",
"from": { "conn": "any", "db": "shop", "coll": "{tenant}_orders" },
"to": { "conn": "cluster", "db": "logistics", "coll": "shipments" },
"rules": [
[
{ "from": "customerId", "op": "eq", "to": "customerRef", "as": "uuid-string" },
{ "from": "orderId", "op": "prefix", "to": "orderRef", "as": "uuid-string" }
],
[
{ "from": "{customerId}-{orderId}", "op": "eq", "to": "legacyRef", "as": "uuid-string" }
]
],
"ruleNotes": ["", "Shipments created before orderRef and customerRef existed."],
"scope": [{ "to": "store", "value": "{tenant}" }],
"fromName": "shipments",
"toName": "order"
}Things to ask an agent:
- "Look at the shop and logistics services' code and propose a relation from orders to shipments in Mongo Studio."
- "Using the Order shipments relation, which of Berlin's orders from last week were shipped in more than one parcel?"
- "List the shipments in logistics that have no order, for the store lisbon."
- "For yesterday's paid orders, sum the shipped weight per store."
The capabilities are See relations and find related documents and Propose and test relations, in Settings › MCP server. find_related also needs Read documents and Read access to both connections.
Performance and safety
- Read-only. Relations never write to your databases. Create index… only opens a script.
- Page-sized. Joins cover the rows on screen, one query per rule, with a 15-second limit and at most 5,000 documents; the editor's preview says when a page hit that cap.
- Samples for everything else. Suggestions and the check use
$samplewith short time limits. A pattern reads at most five tenant collections, the busiest of twenty drawn at random, and collection names are listed once, by name only. - Indexes matter. A rule whose first To field isn't indexed scans the To collection: fine for thousands of documents, slow for millions. The check tells you, and offers the index.
- Stored in Mongo Studio. Relations live in the app's settings folder, next to your connections, not in your databases.
Troubleshooting
"No connection pairs with …"
No other saved connection has the same hosts. Pick the To connection in Connections › Pairs, or choose The From connection itself if it can read both databases.
Nothing found, but there should be
- Check each wire's Compare as: a UUID stored as binary on one side and text on the other needs UUID ↔ text.
- Values with a suffix need starts with.
- The tenant filter may not match: check the field and its value under Tenant.
- Old documents may use another format: add a rule for it.
The preview says "capped"
The page's documents found more than 5,000 related documents. Counts and sums may be low; use a smaller page or a narrower filter.
The sample collection is a tiny tenant
Click its name in the From card and search for a busier tenant; the editor keeps sampling it.