Appendix A: MongoDB to PostgreSQL Mapping

The Waiter of Gold Lapel · Published Apr 19, 2026 · 7 min

The translation dictionary. The first reference a developer reaches for mid-migration, organised so that you may keep it open in another tab and answer one question at a time. Every concept is named in MongoDB's idiom on the left and given its PostgreSQL equivalent on the right, with a short note where the equivalence is approximate rather than exact.

MongoDB is excellent software. The shape of this appendix is a translation, not a critique. Where the two systems differ in a way worth understanding, the difference is named. Where they meet at the same place by different roads, the meeting is recorded.

Storage and Identity

MongoDB	PostgreSQL	Notes
Database	Database	One-to-one. A PostgreSQL database holds collections (tables), schemas, and extensions.
Collection	Table	One collection becomes one table with a `data JSONB` column. Auto-created by `doc_insert`.
Document	JSONB row	The full document lives in `data JSONB`. `_id` and `created_at` sit alongside as columns.
Field	JSONB key	Accessed by `data->'field'` (jsonb) or `data->>'field'` (text).
Embedded document	Nested JSONB object	`data->'address'->>'city'`. Dot notation in filters expands automatically.
Embedded array	JSONB array	`data->'tags'`. Unnested via `jsonb_array_elements` or `jsonb_array_elements_text`.
`_id` (ObjectId)	`_id UUID DEFAULT gen_random_uuid()`	Outside the document as a first-class column; B-tree indexed and joinable.
ObjectId (12-byte)	UUID (16-byte)	Mint fresh UUIDs at migration time; keep the 24-char hex as `legacy_id text` with a unique index.
BSON	JSONB	Binary JSON with sorted keys, offset table, and direct field access. Available since PostgreSQL 9.4 (2014).

Extended JSON Types

MongoDB Extended JSON v2 (relaxed mode) wraps types JSON cannot represent. The five wrappers your loader will encounter, and their PostgreSQL homes:

Extended JSON	Example	PostgreSQL equivalent	Loader action
`$oid`	`{"$oid": "6512..."}`	`text` (`legacy_id`) and a fresh `uuid` for `_id`	Unwrap the hex string into `legacy_id`; mint a UUID for the new `_id`.
`$date`	`{"$date": "2025-09-27T10:30:00Z"}`	`timestamptz`	PostgreSQL parses ISO-8601 directly. MongoDB stores ms; PostgreSQL stores µs — the migration is lossless, the round-trip is not.
`$numberLong`	`{"$numberLong": "9007199254740993"}`	`bigint`	Unwrap the string and cast. JavaScript's `Number` loses precision above 2^53; PostgreSQL does not.
`$numberDecimal`	`{"$numberDecimal": "42.50"}`	`numeric`	Unwrap as a string, store as `numeric` for exact arithmetic. Never round-trip through JS `Number`.
`$binary`	`{"$binary": {"base64": "...", "subType": "00"}}`	`bytea`	Decode the base64 string into `bytea`; carry the subType in a sibling column if the application uses it.

In relaxed mode, ordinary 32-bit integers and finite floats arrive as plain JSON numbers and need no special handling. The five wrappers above are the entire migration vocabulary.

Indexes

MongoDB	PostgreSQL	Notes
Single-field `{email: 1}`	B-tree expression index on `((data->>'email'))`	Or on a generated `STORED` column.
Single-field unique `{email: 1}` unique	`CREATE UNIQUE INDEX ON users ((data->>'email'))`	Same semantics.
Compound `{tenant_id: 1, created_at: -1}`	Composite expression B-tree, column order matches the query	`((data->>'tenant_id'), ((data->>'created_at')::timestamptz) DESC)`.
Multikey (array fields)	GIN on `data jsonb_path_ops`	`jsonb_path_ops` is smaller and faster for `@>`, `@?`, `@@`; choose `jsonb_ops` if you need `?`, `?\|`, `?&`.
Text	GIN on `to_tsvector('english', data->>'body')`	Configurable per language.
2dsphere (geo)	GiST on a `geography` generated column	Requires the PostGIS extension.
Hashed	Hash index, or B-tree on `md5(data->>'field')`	Hash indexes are crash-safe since PostgreSQL 10.
TTL `{expireAfterSeconds: N}`	Expression index plus scheduled `DELETE` (`pg_cron`), or partition drop	PostgreSQL has no native TTL thread. Gold Lapel ships `doc_create_ttl_index`.
Partial `{partialFilterExpression: ...}`	`CREATE INDEX ... WHERE <expr>`	Smaller, faster index over the rows you actually query.
Sparse	Partial index `WHERE data ? 'field'`	Same intent expressed as a partial index.
Wildcard `{"$**": 1}`	GIN on `data` (default `jsonb_ops`)	Indexes every key/value pair in every document.
`createIndex(...)`	`CREATE INDEX` (or `CREATE INDEX CONCURRENTLY`)	Gold Lapel exposes both via `doc_create_index`; the proxy auto-creates GIN indexes from observed query patterns.

Operations and Topology

MongoDB	PostgreSQL	Notes
Replica set	Streaming replication (primary + standbys)	Same mental model. Synchronous replicas via `synchronous_commit = remote_apply`.
Primary / Secondary	Primary / Replica	Read replicas via `--replica` routing in Gold Lapel. Read-after-write protection built in.
Oplog	Write-Ahead Log (WAL)	Both are append-only change journals. WAL drives streaming replication and logical replication.
Sharding (`mongos`, config servers, balancer)	Citus (distributed tables, coordinator + workers)	Citus is excellent for most sharded workloads; native MongoDB sharding is more mature past 50 nodes.
Shard key	Distribution column	`SELECT create_distributed_table('events', 'tenant_id');`
Zone sharding	Citus reference tables + tenant routing	Approximate. Geographic placement requires more orchestration in PostgreSQL.
Change streams	Row trigger + `LISTEN`/`NOTIFY`	Gold Lapel exposes `doc_watch` / `doc_unwatch`. Same event shape, different transport.
GridFS	Object storage (S3, GCS, etc.) + JSONB metadata row	Cleaner separation; PostgreSQL is not the right home for binary blobs at scale.
Atlas	Managed PostgreSQL provider	RDS, Cloud SQL, Azure Database for PostgreSQL, Crunchy Bridge, Supabase, Neon, Aiven.
Compass	psql, DBeaver, pgAdmin, TablePlus	Compass remains the finest GUI for inspecting MongoDB data; the PostgreSQL ecosystem has many capable equivalents.
Atlas Charts	Metabase, Superset, Grafana, Looker	Charts is excellent in its niche; PostgreSQL connects to every BI tool in existence.
Atlas Search	`tsvector` + GIN, `pg_trgm`, `pgvector`, `fuzzystrmatch`	Treated in full in You Don't Need Elasticsearch.

Transactions and Consistency

MongoDB	PostgreSQL	Notes
Single-document atomic write	Single-row `UPDATE` (atomic under all isolation levels)	Identical semantics.
Multi-document transaction (`session.startTransaction`)	`BEGIN` / `COMMIT` (since the project's earliest releases; MVCC since 6.5, 1999)	PostgreSQL: every statement runs in a transaction. MongoDB: 4.0 (replica sets), 4.2 (sharded). 60-second default `transactionLifetimeLimitSeconds`.
`findAndModify`	`UPDATE ... RETURNING` in a CTE with `FOR UPDATE`	Atomic select-update-return. `SKIP LOCKED` for work queues.
Read concern `local` / `majority`	Read committed (default) / serializable	PostgreSQL also offers `repeatable read`; `serializable` uses SSI (Serializable Snapshot Isolation).
Write concern `w: 1` / `w: "majority"`	`synchronous_commit = off` / `on` / `remote_apply`	The trade between throughput and durability lives at this knob.
Causal consistency	Strict consistency on the primary; replica lag bounded by streaming replication	PostgreSQL primaries are linearizable by default.
Optimistic concurrency	MVCC with snapshot isolation	PostgreSQL never reads a partially-committed row.

Backup and Recovery

MongoDB	PostgreSQL	Notes
`mongodump` / `mongorestore`	`pg_dump` / `pg_restore`	Logical backup; portable across versions.
Filesystem snapshot	`pg_basebackup`	Physical backup; faster restore, version-locked.
Point-in-time recovery (Atlas)	PITR via WAL archive (every managed provider supports it)	Same capability, different tooling.
Oplog tail for incremental	WAL archiving + log shipping / streaming	Same mechanism, named differently.

Drivers and Languages

The Gold Lapel SDKs ship doc_* methods in every supported language. Method casing follows each language's convention; behaviour is identical across all seven.

Language	MongoDB driver	Gold Lapel SDK	Method casing
Python	`pymongo`, `motor`	`goldlapel`, `goldlapel.asyncio`	`snake_case` (`doc_find`)
Node.js	`mongodb`	`goldlapel`	`camelCase` (`docFind`)
Ruby	`mongo`	`goldlapel`	`snake_case` (`doc_find`)
Java	`mongodb-driver-sync`, `mongodb-driver-reactivestreams`	`goldlapel-jdbc`, `goldlapel-spring-boot`	`camelCase` (`docFind`)
PHP	`mongodb/mongodb`	`goldlapel/goldlapel`	`camelCase` (`docFind`)
Go	`go.mongodb.org/mongo-driver`	`github.com/goldlapel/goldlapel-go`	`PascalCase` (`DocFind`)
.NET	`MongoDB.Driver`	`GoldLapel`	`PascalCase` (`DocFind`)

Aggregation Pipeline

A complete one-line summary of every supported pipeline stage and accumulator. Appendix E gives full syntax, generated SQL, and examples.

MongoDB stage	PostgreSQL equivalent
`$match`	`WHERE` (or `HAVING` after `$group`)
`$group`	`GROUP BY` with aggregate functions
`$sort`	`ORDER BY`
`$limit`	`LIMIT`
`$skip`	`OFFSET` (use keyset pagination for deep pages)
`$project`	`SELECT` with `jsonb_build_object`
`$unwind`	`CROSS JOIN LATERAL jsonb_array_elements(...)`
`$lookup`	Correlated subquery with `jsonb_agg`, or true SQL `JOIN`

MongoDB accumulator	PostgreSQL equivalent
`$sum`	`sum(expr)` (`$sum: 1` short-circuits to `count(*)`)
`$avg`	`avg(expr)`
`$min` / `$max`	`min(expr)` / `max(expr)`
`$push`	`jsonb_agg(expr)`
`$addToSet`	`jsonb_agg(DISTINCT expr)`
`$count`	`count(*)`
`$first` / `$last`	`(array_agg(expr ORDER BY ...))[1]` / window functions

Filter Operators

Appendix D gives the full syntax, generated SQL, and index used. The summary:

MongoDB	PostgreSQL
`{field: value}`	data @> '{`{"field": value}`}' (GIN-indexed)
`$eq`, `$ne`	`data @> ...` / `NOT (data @> ...)`
`$gt`, `$gte`, `$lt`, `$lte`	`(data->>'field')::T > v` (cast-aware)
`$in` / `$nin`	`data->>'field' IN (...)` / `NOT IN (...)` with `IS NULL` guard
`$exists`	`data ? 'field'` (top-level) or `data #> '{path}' IS NOT NULL` (nested)
`$regex`	`~` or `~*` (case-insensitive)
`$or` / `$and` / `$not`	SQL boolean composition
`$elemMatch`	`EXISTS (SELECT 1 FROM jsonb_array_elements(data->'arr') WHERE ...)`
`$text`	`to_tsvector(...) @@ plainto_tsquery(...)`

Update Operators

MongoDB	PostgreSQL
`$set`	`jsonb_set(data, path, value, true)` (or `data \|\| '{...}'::jsonb` for shallow merge)
`$unset`	`data #- '{path}'`
`$inc`	`jsonb_set(data, path, (coalesce((data->>'f')::int, 0) + n)::text::jsonb)`
`$mul`	Same shape as `$inc` with multiplication
`$rename`	`jsonb_set(data #- old, new, data->old)`
`$push`	`jsonb_set(data, path, coalesce(data->path, '[]'::jsonb) \|\| value)`
`$pull`	Subquery over `jsonb_array_elements` filtered and re-aggregated
`$addToSet`	`CASE` on `@>` containment, then `jsonb_set`
`upsert: true`	`INSERT ... ON CONFLICT (_id) DO UPDATE`

Operational Features

MongoDB	PostgreSQL	Gold Lapel API
Change streams	Row trigger + `LISTEN`/`NOTIFY`	`doc_watch` / `doc_unwatch`
TTL index	Expression index + sweep trigger (or `pg_cron` `DELETE`)	`doc_create_ttl_index` / `doc_remove_ttl_index`
Capped collection	Row-count trigger + ordering index	`doc_create_capped` / `doc_remove_cap`
`$jsonSchema` validator	`CHECK (jsonb_matches_schema(...))` via `pg_jsonschema`, or hand-rolled `CHECK` constraints	`doc_create_collection(validator=...)`
Server-side functions	PL/pgSQL, PL/Python, PL/V8	Native PostgreSQL.
Materialized views	Native (`CREATE MATERIALIZED VIEW`, since 9.3)	`doc_aggregate` caches behind a matview keyed by pipeline hash.

Where to Go Next

Appendix B — the 21 methods, full reference per language.
Appendix C — migration checklist, in the order the work happens.
Appendix D — filter operators in full.
Appendix E — aggregation pipeline in full.
Glossary — for any term in the table above that wants definition.

If a row sends you to a chapter, the chapter contains the full treatment. The appendix is the index, not the argument.