MongoDB → RedDB: the queries that change shape

A MongoDB-to-RedDB migration is not the war story people expect. The replication is straightforward, the dual-write window is small, the cutover is boring. What is interesting is what happens to your queries — most of them get shorter, a few get longer, and three patterns change shape entirely. This post is a translation table for the ones that change shape.

We are not going to argue MongoDB versus relational here. We are going to assume you have already decided to move and want to know what your aggregation pipeline looks like on the other side. Every translation below has been pulled from a real migration we have shipped — names changed, shapes preserved.

The mental model shift

Before any single query: in Mongo you push computation up into the pipeline, and the document is the unit of co-location. In RedDB you push computation down into the query planner, and the row (with its JSONB columns) is the unit of co-location. The good news is that RedDB’s JSONB support is rich enough that you do not have to flatten everything — keep the long tail of optional fields as JSONB and lift only the columns you query or index on.

The rule of thumb we use: if you filter, sort, or join on a field in more than one place, promote it to a column. Otherwise leave it in JSONB. Migrations that try to fully normalise every Mongo document on day one tend to stall around week four. Migrations that promote the hot fields and leave the long tail in JSONB tend to ship in a fortnight.

Translation 1: simple find with projection

The cheapest case. Mongo:

db.orders.find(
  { customer_id: "c_42", status: "open" },
  { _id: 1, total: 1, created_at: 1 }
).sort({ created_at: -1 }).limit(20)

RedDB:

SELECT id, total, created_at
FROM orders
WHERE customer_id = 'c_42' AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Index on (customer_id, status, created_at DESC) and you are done. If status is one of a small set, a partial index WHERE status = 'open' is often cheaper. Nothing exotic.

Translation 2: aggregation pipeline → SQL with JSONB

This is where most of the work lives. A typical revenue rollup in Mongo:

db.orders.aggregate([
  { $match: { created_at: { $gte: ISODate("2026-01-01") }, status: "paid" } },
  { $unwind: "$items" },
  { $group: {
      _id: { sku: "$items.sku", month: { $dateToString: { format: "%Y-%m", date: "$created_at" } } },
      revenue: { $sum: { $multiply: ["$items.qty", "$items.unit_price"] } },
      orders:  { $addToSet: "$_id" }
  }},
  { $project: {
      sku: "$_id.sku",
      month: "$_id.month",
      revenue: 1,
      order_count: { $size: "$orders" },
      _id: 0
  }},
  { $sort: { month: 1, revenue: -1 } }
])

The RedDB version uses jsonb_array_elements for the unwind and ordinary GROUP BY:

SELECT
  item->>'sku'                                   AS sku,
  to_char(created_at, 'YYYY-MM')                 AS month,
  SUM((item->>'qty')::numeric * (item->>'unit_price')::numeric) AS revenue,
  COUNT(DISTINCT id)                             AS order_count
FROM orders, jsonb_array_elements(items) AS item
WHERE created_at >= '2026-01-01' AND status = 'paid'
GROUP BY sku, month
ORDER BY month, revenue DESC;

Three things to notice:

$unwind is jsonb_array_elements in a FROM clause. It produces one row per array element, which is exactly what the SQL aggregation expects.
$addToSet + $size is COUNT(DISTINCT) — shorter and faster, because the planner does not have to materialise the set.
$dateToString is to_char. If you do this rollup often, store to_char(created_at, 'YYYY-MM') as a generated column and index it.

For pipelines deeper than three stages we usually rewrite as a CTE chain, one CTE per $match/$group stage. The pipeline shape survives — only the syntax changes.

Translation 3: `$lookup` → `JOIN`

$lookup is a join with extra steps. The literal translation almost always works and is faster than the Mongo version.

Mongo:

db.orders.aggregate([
  { $match: { status: "open" } },
  { $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "_id",
      as: "customer"
  }},
  { $unwind: "$customer" },
  { $project: { _id: 1, total: 1, "customer.email": 1, "customer.tier": 1 } }
])

RedDB:

SELECT o.id, o.total, c.email, c.tier
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open';

The Mongo $lookup requires an index on customers._id (free) and traverses one document at a time. The SQL version is hash-joined or merge-joined in one pass. We have not yet seen a $lookup whose SQL equivalent was slower.

Translation 4: change streams → logical replication or triggers

This is one of the three shape-changers.

A Mongo change stream is a tailing cursor on the oplog:

const stream = db.orders.watch([{ $match: { "fullDocument.status": "paid" } }])
for await (const change of stream) {
  await fulfillment.enqueue(change.fullDocument)
}

RedDB offers two replacements, and they are not interchangeable:

Option A: logical replication (use this if you have one or two well-defined consumers that need every change, in order, durably):

CREATE PUBLICATION orders_pub FOR TABLE orders WHERE (status = 'paid');
-- consumer connects via the replication protocol

Option B: a trigger that writes to an outbox table (use this if you want application-level retry control or fan-out to a queue):

CREATE TABLE outbox_orders (
  id          bigserial PRIMARY KEY,
  order_id    text NOT NULL,
  payload     jsonb NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  consumed_at timestamptz
);

CREATE FUNCTION orders_to_outbox() RETURNS trigger AS $$
BEGIN
  IF NEW.status = 'paid' AND (OLD IS NULL OR OLD.status <> 'paid') THEN
    INSERT INTO outbox_orders (order_id, payload)
    VALUES (NEW.id, to_jsonb(NEW));
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_outbox_t
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_to_outbox();

Your fulfillment worker pulls from outbox_orders with FOR UPDATE SKIP LOCKED, processes, then sets consumed_at. The outbox pattern gives you exactly-once-with-effort semantics that watch() did not have — change streams resume on a token, but the resume token is yours to lose, and we have watched several teams discover this the hard way.

The shape change to internalise: change streams are a transport; the outbox-plus-poller pattern is a storage layer with a transport on top. The latter is more code and more correctness.

Translation 5: GridFS → blob columns (or object storage)

GridFS is a workaround for Mongo’s 16MB document limit. RedDB does not have that limit, but you still should not put a 50MB PDF in a row.

The translation depends on file size:

Mongo (GridFS)	RedDB	Use when
Files < 1 MiB	`BYTEA` column on the owning row	Small avatars, signatures
Files 1–8 MiB	`BYTEA` column with `STORAGE EXTERNAL`	Documents, medium attachments
Files > 8 MiB	S3-compatible object store, URL + sha256 row	Videos, large datasets

A typical migration moves the GridFS chunks collection straight to object storage, writes the metadata row to RedDB with a storage_url and sha256, and leaves a one-shot job that lazily pulls each blob the first time it is requested. The big-file branch is the right default — GridFS performance gets pathological once you have a few hundred thousand large files in one collection, and most teams quietly already keep them in S3 anyway.

Translation 6: text search

If you used Mongo’s $text operator, you have one of two situations:

You needed light keyword search on a small set of fields. Use RedDB’s built-in tsvector:

ALTER TABLE products ADD COLUMN search_v tsvector
  GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || coalesce(description, ''))) STORED;
CREATE INDEX products_search_v_idx ON products USING gin (search_v);

SELECT id, name FROM products
WHERE search_v @@ plainto_tsquery('english', 'wireless headphones');

You needed real relevance, faceting, or typo-tolerance. Mongo $text was already not enough. Move to a real search index (Tantivy on RedDB, or an external Elastic/Meili) and pipe updates through the same outbox table from translation 4.

Translation 7: TTL indexes → scheduled DELETE

Mongo TTL indexes are a background job dressed as an index. RedDB ships the job explicitly:

CREATE INDEX sessions_expires_at_idx ON sessions (expires_at)
  WHERE expires_at IS NOT NULL;

-- run from cron, every minute
DELETE FROM sessions WHERE expires_at < now() LIMIT 10000;

The LIMIT matters. The Mongo TTL worker batches; your cron job should too, or a single sweep can lock half the table during peak.

What we left out and why

Two Mongo patterns do not get a translation here because they should not survive the migration:

Schemaless writes from many services. If five services write to the same collection with different shapes, the schemaless freedom was already costing you. The migration is the right moment to define the shape. Promote the shared fields to columns; let the disagreement live in JSONB only where it is genuinely unresolved.
Embedded arrays of unbounded growth. A comments: [...] field that grows for the lifetime of a post is a future operational problem in any database. The migration is the right moment to lift it into its own table.

The honest summary

Most query translations are mechanical and the result is faster, more inspectable code. The three shape-changers — change streams, GridFS, and unbounded embedded arrays — deserve real design time and should not be translated literally. Plan one to two days per shape-changer; the rest of the queries are an afternoon’s typing.

If you are deep in a migration and stuck on a pipeline you cannot translate cleanly, send us the pipeline. We have probably seen its shape before.