Migrating Oracle to MongoDB: Turning Foreign Keys into Embedded Documents

The Under-Served Direction

Almost every migration tool moves data one way: relational to relational, usually to PostgreSQL. Going the other way — relational to MongoDB — is harder and far less served, and the naive version fools people into thinking it’s solved. Copy each table to a collection, each row to a document, done. Except that isn’t a document model — it’s your relational schema wearing a MongoDB costume, still joined by hand with $lookup on every read.

Doing it properly means two things the naive copy skips: preserving types (a migration tool that turns money into a string has failed), and reshaping relationships the way documents are meant to hold them. Here’s how DBMigrateAIPro does both — and the traps that quietly wreck the migration if you don’t.

Typed Documents, Not Stringified Rows

The most common silent failure is routing every value through CSV, so numbers, dates and binary all land as strings. For a migration tool that’s unacceptable: a NUMBER(12,2) salary must not become a lossy float, let alone the text "12500.25". Each row becomes a typed BSON document:

Oracle / PostgreSQL          MongoDB (BSON)
─────────────────────────    ──────────────────────
NUMBER(p,s)              →    Decimal128   (exact — no float drift)
NUMBER / INTEGER         →    int / long
DATE / TIMESTAMP         →    datetime
VARCHAR / CLOB / NCLOB   →    string
BLOB / RAW               →    Binary
BOOLEAN                  →    bool
NULL                     →    null   (not the string "null")

The Decimal128 path matters more than it looks. Oracle’s driver hands back NUMBER(p,s) as a Python float by default, so a careless pipeline quantises money to binary floating point before it ever reaches Mongo. The fix is to carry an exact decimal all the way through — a lesson a live run against real hardware taught us, not the unit tests.

Indexes From Your Keys, and Counts You Can Trust

MongoDB has no DDL, so a relational schema’s keys can’t travel as ALTER TABLE. Instead they become real indexes: the primary key and every unique constraint become unique indexes, and secondary indexes carry over. Then every collection is count-validated against the source — so if a document is rejected (say it blew past the 16MB limit), you see the shortfall immediately instead of discovering it in production.

The Main Event: Embedding 1:N From Foreign Keys

This is what makes it a document model. A customers table and its orders can migrate as two disconnected collections — or orders can be folded into each customer as a nested array, auto-detected from the foreign key:

{
  "_id": 1,
  "name": "Ann",
  "orders": [
    { "_id": 10, "total": { "$numberDecimal": "50.00" } },
    { "_id": 11, "total": { "$numberDecimal": "70.00" } }
  ]
}

No aggregation pipeline to hand-write, no export/transform/re-import. The tool reads the source’s foreign keys, works out which tables are 1:N children, and nests them. In the CLI it’s a post-load pass; in the desktop app it’s a single checkbox — “Embed related tables (1:N)”.

# after a flat table→collection migration into MongoDB:
python main.py mongo-embed \
  --source oracle --source-host db --source-db PROD --source-schema SALES \
  --target-db sales --tables customers,orders,order_items

What Must NOT Embed

Embedding everything is how you corrupt a migration. The rule is deliberately conservative — a child is embedded into a parent only when it’s an unambiguous, single-owner 1:N leaf:

• Junction / M:N tables (two foreign keys, e.g. enrollments) stay standalone — embedding into one parent would double-count the rows.
• Tables referenced by others (a hub like orders, which order_items points at) stay collections, so those references still resolve.
• Self-referential tables (an employees.manager_id) stay standalone.

Keeping the mapping single-valued isn’t pedantry — it’s what makes the count validation meaningful. Every child row lands in exactly one place, so “5,000 order-items in, 5,000 embedded” is a real guarantee.

Three Traps, Handled

The 16MB document limit. A parent with an enormous child set can exceed MongoDB’s per-document ceiling. The load is resilient — an oversized document is reported by count validation rather than aborting the whole job, so you learn exactly which relationship needs a different shape (referencing instead of embedding).

Dangling foreign keys. What about a child row whose parent doesn’t exist? Dropping it would lose data; embedding it is impossible. So the embedded rows are removed from the child collection and the orphans are left behind in it — nothing duplicated, nothing lost, and the leftover collection is exactly the set of rows that need attention.

Oracle’s hidden index columns. Function-based indexes are backed by auto-generated SYS_NC…$ columns that are never real document fields. Left alone they’d produce dead indexes on keys that don’t exist, so they’re detected and skipped.

The Point

A relational → MongoDB migration should leave you with a database shaped the way MongoDB is meant to be queried — typed values, indexes from your keys, related data embedded where it belongs — and the counts to prove nothing was lost along the way. That’s the difference between moving data and modelling it.