How to Build a Nightly MongoDB to MySQL Reporting Sync

Run a workflow every night that aggregates documents from MongoDB, reshapes the results, and upserts them into MySQL reporting tables so your BI tooling always reads fresh, query-ready numbers.

What This Integration Does

Operational data usually lives in a document store like MongoDB, but the dashboards your team trusts run on a SQL warehouse. Querying raw MongoDB collections directly from BI tooling is slow and awkward, and recomputing rollups on every dashboard load is wasteful. This Spojit workflow does the heavy lifting once per night: it summarizes your MongoDB documents into the metrics you actually report on (daily orders, revenue by channel, active users, and so on), then writes those summaries into flat MySQL tables that BI tools can read with a simple SELECT.

The run model is a nightly Schedule trigger. On each run the workflow executes a MongoDB aggregate pipeline scoped to the previous day, reshapes the result into rows with a Transform node, then upserts those rows into a MySQL reporting table using the MySQL connector. Because the write is an upsert keyed on a date (and any dimension you report by), re-running the workflow for the same day overwrites the existing rows instead of duplicating them. That makes the sync idempotent: a failed or repeated run leaves the reporting table in the same correct state, and you can safely backfill a missed night.

Prerequisites

A MongoDB connection added in Connections with read access to the source database and collection you want to summarize.
A MySQL connection added in Connections with write access to the reporting database.
A destination reporting table in MySQL with a unique key on the columns you upsert by (for example a unique index on report_date, or on (report_date, channel)). The upsert in this tutorial depends on that key existing.
A clear definition of the metrics you report on and which MongoDB fields they come from (for example an orders collection with createdAt, totalPrice, and channel fields).
A timezone decision for the schedule and the date boundaries (this tutorial uses Australia/Sydney; pick the one your reporting day is measured in).

Step 1: Add a Schedule trigger for the nightly run

Start a new workflow and set the Trigger node type to Schedule. Add a 5-field Unix cron expression and an IANA timezone. To run at 1:30 AM every day in Sydney, use:

30 1 * * *
Australia/Sydney

Running after midnight means the previous calendar day is complete, so your daily rollup covers whole days. A Schedule trigger emits {{ scheduledAt }}, which you will use to compute the day to aggregate. A single trigger can hold multiple schedules if you later want a second run, but one nightly schedule is enough here.

Step 2: Compute yesterday's date window

Add a Connector node in Direct mode using the date connector so the aggregation always targets the right day rather than a hardcoded value. Pick the subtract tool to step back one day from the scheduled time, then the format tool (or the start-of and end-of tools) to produce the boundaries your MongoDB documents are stored in. Map the input from {{ scheduledAt }}.

If your MongoDB date fields are stored as ISO strings or millisecond timestamps, produce a start and end bound for the day (for example 2026-06-19T00:00:00.000Z through 2026-06-20T00:00:00.000Z) and a short report_date string like 2026-06-19 for the reporting key. Set the node's output variable to window so later steps can reference {{ window.start }}, {{ window.end }}, and {{ window.reportDate }}.

Step 3: Aggregate the MongoDB documents

Add a Connector node in Direct mode for the MongoDB connector and choose the aggregate tool. Set collection to your source collection (for example orders) and provide a pipeline that filters to yesterday's window and groups into the metrics you report on. A revenue-by-channel rollup looks like this:

[
  { "$match": {
      "createdAt": { "$gte": "{{ window.start }}", "$lt": "{{ window.end }}" }
  }},
  { "$group": {
      "_id": "$channel",
      "orderCount": { "$sum": 1 },
      "revenue": { "$sum": "$totalPrice" }
  }},
  { "$sort": { "revenue": -1 } }
]

The aggregate tool supports all standard pipeline stages ($match, $group, $sort, $project, $lookup, and more), so you can shape exactly the columns your reporting table needs. Set the output variable to agg; the result documents are available as {{ agg.documents }}. Push the date filter into $match at the top of the pipeline so the database does the narrowing, not the workflow.

Step 4: Reshape the result into reporting rows

Add a Transform node to turn the aggregation output into a flat array of rows whose keys match your MySQL columns exactly. Map each aggregation document into a row, folding in the report_date from your date window so every row carries the upsert key:

{{ agg.documents }} mapped to:
{
  "report_date": "{{ window.reportDate }}",
  "channel":     "{{ item._id }}",
  "order_count": "{{ item.orderCount }}",
  "revenue":     "{{ item.revenue }}"
}

Rename _id to a real column name (here channel), coerce numeric fields to the types your table expects, and drop anything you are not storing. If you prefer to do this reshaping with the json connector instead of a Transform node, the pick, set, and merge tools handle the same job in Direct mode. Set the output variable to rows.

Step 5: Upsert the rows into MySQL

Add a Loop node in ForEach mode over {{ rows }} so each reporting row is written individually and idempotently. Inside the loop, add a Connector node in Direct mode for the MySQL connector and choose the execute-query tool. Use an INSERT ... ON DUPLICATE KEY UPDATE statement with ? placeholders so values are passed safely as parameters rather than concatenated into the SQL:

INSERT INTO daily_channel_revenue
  (report_date, channel, order_count, revenue)
VALUES (?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  order_count = VALUES(order_count),
  revenue     = VALUES(revenue);

Map the params array, in order, to {{ item.report_date }}, {{ item.channel }}, {{ item.order_count }}, and {{ item.revenue }}. The ON DUPLICATE KEY UPDATE clause relies on the unique key you created in the prerequisites: the first nightly run inserts the day's rows, and any re-run for the same day updates them in place. If you would rather confirm the table shape before writing, run the MySQL describe-table tool once against your reporting table to review its columns and the CREATE TABLE statement.

Step 6: Notify on completion or failure

After the loop, add a Send Email node to confirm the sync ran. Set Recipients to your data team address, a templated Subject such as Reporting sync complete for {{ window.reportDate }}, and a Body summarizing how many rows were written. For failures, set If sending fails to Continue anyway so a mail hiccup never masks a successful data write, and rely on Spojit's execution history and retry settings to surface a failed aggregation or upsert. External recipients must be on the org allowlist under Settings > General > Email recipients. Save the workflow, then enable it so the schedule starts firing.

Tips

Keep the MongoDB $match stage first and indexed: an index on the date field your pipeline filters by (for example createdAt) keeps the nightly aggregation fast even as the collection grows.
Upsert on a stable composite key. If you report by multiple dimensions, put a unique index on all of them (for example (report_date, channel)) so the ON DUPLICATE KEY UPDATE matches the exact row.
For very wide rollups, batch the writes by using the MySQL insert-rows tool with a multi-row payload for the initial load, then keep execute-query with the upsert statement for the nightly refresh.
Let Miraxa, the intelligent layer across your automation, scaffold the skeleton for you. A prompt like "Add a Schedule trigger at 1:30 AM Sydney time, a MongoDB aggregate node, a Transform node, and a ForEach Loop that runs a MySQL execute-query upsert" gets the canvas wired up, then fine-tune each node in the properties panel.

Common Pitfalls

Timezone drift: the schedule timezone and your date-window math must agree, or your "daily" rollup will straddle two calendar days. Pick one IANA timezone and use it in both the Schedule trigger and the date connector window.
Missing unique key: without a unique index on the upsert columns, ON DUPLICATE KEY UPDATE silently inserts duplicate rows on every re-run. Confirm the key exists before enabling the schedule.
Type mismatches: MongoDB stores numbers and dates loosely, while MySQL columns are strict. Coerce values in the Transform step so a string revenue or an unexpected null does not get rejected at write time.
Schema drift: if your MongoDB documents add or rename fields, the aggregation keeps working but your reporting columns silently go stale. Re-check the pipeline whenever the source schema changes.

Testing

Before enabling the nightly schedule, validate on a small scope. Temporarily point the date window at a single known day with a handful of documents, then use the Run button to trigger the workflow manually. Inspect the execution history to confirm the MongoDB aggregate step returned the documents you expect, the Transform produced rows whose keys match your MySQL columns, and the loop wrote one row per group. Run it a second time for the same day and verify the row count in MySQL does not increase, which proves the upsert is idempotent. Once a manual run is clean end to end, widen the window back to "yesterday" and enable the schedule.