MongoDB Data Connector

MongoDB is an open-source NoSQL database that stores data in flexible, JSON-like documents, supporting dynamic schemas and easy scalability.

The MongoDB Data Connector enables federated/accelerated SQL queries on data stored in MongoDB databases.

datasets:
  - from: mongodb:mytable
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_pool_min: 1
      mongodb_pool_max: 10

Configuration

from

The from field takes the form mongodb:{table_name} where table_name is the table identifer in the MongoDB server to read from.

datasets:
  - from: mongodb:mytable
    name: my_dataset
    params:
      mongodb_db: my_database
      ...

name

The dataset name. This will be used as the table name within Spice.

Example:

datasets:
  - from: mongodb:my_dataset
    name: cool_dataset
    params: ...

SELECT COUNT(*) FROM cool_dataset;

+----------+
| count(*) |
+----------+
| 6001215  |
+----------+

The dataset name cannot be a reserved keyword

params

The MongoDB data connector can be configured by providing the following params. Use the secret replacement syntax to load the secret from a secret store, e.g. ${secrets:my_mongodb_conn_string}.

Types

The table below shows the MongoDB data types supported, along with the type mapping to Apache Arrow types in Spice.

Unnesting

Consider the following document:

{
  "a": 1,
  "b": {
    "x": 2,
    "y": {
      "z": 3
    }
  }
}

Using mongodb_unnest_depth you can control the unnesting behavior. Here are the examples:

mongodb_unnest_depth: 0

sql> select * from test_table;
+-----------+---------------------+
| a (Int32) | b (Utf8)            |
+-----------+---------------------+
| 1         | {"x":2,"y":{"z":3}} |
+---+-----------------------------+

mongodb_unnest_depth: 1

sql> select * from test_table;
+-----------+-------------+------------+
| a (Int32) | b.x (Int32) | b.y (Utf8) |
+-----------+-------------+------------+
| 1         | 2           | {"z":3}    | 
+-----------+-------------+------------+

mongodb_unnest_depth: 2

sql> select * from test_table;
+-----------+-------------+---------------+
| a (Int32) | b.x (Int32) | b.y.z (Int32) |
+-----------+-------------+---------------+
| 1         | 2           | 3             |
+-----------+-------------+---------------+

JSON Nesting

When a MongoDB collection has many fields but you only need a few as discrete columns, you can consolidate the rest into a single JSON column using the json_object metadata option. Declare the fields you want as top-level columns explicitly in the columns list, then add a "catch-all" column with json_object: "*" metadata: every field not otherwise listed is nested into it as a JSON object. This applies to both the query (scan) path and the Change Streams (CDC) path.

datasets:
  - from: mongodb:orders
    name: orders
    params:
      mongodb_host: localhost
      mongodb_db: shop
    columns:
      - name: _id
      - name: status
      - name: data_json
        metadata:
          json_object: '*'

Any field other than _id, status, and data_json is folded into data_json as a JSON object. The _id field must be declared explicitly and cannot be folded into the catch-all column.

Query Pushdown

Spice sends the selected columns, the supported WHERE conditions, and a LIMIT to MongoDB, so MongoDB returns only the documents and fields that the query needs. A condition is sent only in a form that selects the same documents as SQL. MongoDB's $ne and $nin operators match a document whose field is missing or null, while SQL's age <> 30 and age NOT IN (25, 30) exclude it, so Spice adds type checks to the filter it sends. For an age column of type Int32, WHERE age <> 30 becomes:

{"age":{"$type":["int","long"],"$ne":30,"$gte":-2147483648,"$lte":2147483647,"$not":{"$type":"array"}}}

When MongoDB can compare values differently from SQL, for example a string comparison such as s > 'Z' over a field that some documents store as another BSON type, Spice sends a wider filter and evaluates the condition again on the documents MongoDB returns. The result matches a query that sends no filter to MongoDB.

Spice evaluates ORDER BY itself. It sorts the documents MongoDB returns, so ORDER BY ... LIMIT reads every document that matches the filter.

Run EXPLAIN to see what is sent: the MongoDBExec node lists the projection, the filter as filters=[...], and the limit as limit=[...].

Examples

Connecting using username and password and custom auth table

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_auth_source: admin

Connecting using SSL

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_sslmode: preferred
      mongodb_sslrootcert: ./custom_cert.pem

Connecting using a Connection String

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_connection_string: mongodb://${secrets:my_user}:${secrets:my_password}@localhost:27017/my_db?authSource=admin

Connecting to MongoDB Atlas (SRV)

When mongodb_host ends with .mongodb.net, mongodb_srv is automatically enabled and mongodb_port is ignored: mongodb+srv:// discovers host/port via DNS SRV records.

datasets:
  - from: mongodb:my_collection
    name: my_dataset
    params:
      mongodb_host: cluster0.abc123.mongodb.net
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}

Set mongodb_srv: true explicitly for non-Atlas hosts that are configured with SRV records:

datasets:
  - from: mongodb:my_collection
    name: my_dataset
    params:
      mongodb_srv: true
      mongodb_host: mongo.example.com
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}

With custom connection pool settings

datasets:
  - from: mongodb:my_dataset
    name: my_dataset
    params:
      mongodb_host: localhost
      mongodb_port: 27017
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
      mongodb_pool_min: 5
      mongodb_pool_max: 10

Using MongoDB Change Streams

Spice supports real-time Change Data Capture (CDC) from MongoDB using native MongoDB Change Streams. This streams inserts, updates, replacements, deletes, and collection-level invalidation events from MongoDB collections directly into Spice accelerators.

How it works

On startup, Spice opens a Change Stream on the source collection (fullDocument=updateLookup), emits a CDC TRUNCATE, applies a full snapshot of the collection as upsert rows, signals readiness, then processes Change Stream events in batches. Opening the Change Stream before the snapshot prevents gaps between the snapshot and the live stream.

File-accelerated datasets persist resume tokens and resume from the last committed token on restart. In-memory accelerators re-bootstrap from a fresh snapshot.

Prerequisites

• MongoDB 4.0+ with Change Streams enabled. MongoDB requires a replica set or sharded cluster for Change Streams.
• The MongoDB user must have changeStream privileges.
• The accelerator must support upsert behavior. Use duckdb, sqlite, postgres, turso, or cayenne.
• acceleration.primary_key: _id is required. Delete events only include the document key, so Spice needs _id to route deletes.
• On engines other than cayenne, acceleration.on_conflict must specify upsert on _id so update and replace events overwrite existing rows. Cayenne keeps one row per _id without it.

Minimal configuration

datasets:
  - from: mongodb:users
    name: users
    params:
      mongodb_host: localhost
      mongodb_port: '27017'
      mongodb_db: my_database
      mongodb_user: my_user
      mongodb_pass: ${secrets:mongodb_pass}
    acceleration:
      enabled: true
      engine: duckdb
      refresh_mode: changes
      primary_key: _id
      on_conflict:
        _id: upsert

Change Stream parameters

These optional runtime parameters live under dataset params:. The first four are not prefixed with mongodb_.

The existing mongodb_unnest_depth parameter also applies to Change Stream documents, so nested BSON is flattened the same way as normal MongoDB reads.

Event mapping

• insert: create/upsert, using fullDocument.
• update: update/upsert, using fullDocument from fullDocument=updateLookup.
• replace: update/upsert, using fullDocument.
• delete: delete, using documentKey; non-key columns are null.
• drop, rename, dropDatabase, invalidate: truncate, because collection continuity is no longer guaranteed.

If MongoDB omits fullDocument for an update or replace event (for example, the document was deleted before the fullDocument=updateLookup post-image could be read), Spice substitutes a synthetic delete keyed on documentKey and logs a warning; if documentKey is also unavailable, the event is skipped with a warning. An insert event missing fullDocument, or a delete event missing documentKey, still fails the stream.

Resumability across restarts

For file-accelerated datasets (acceleration mode: file / file_create / file_update, or engine: postgres), Spice persists the most recent Change Stream resume token in a sidecar table named spice_sys_mongodb, stored alongside the accelerator data. The token is committed only after the downstream accelerator write succeeds (at-least-once semantics).

On restart with a persisted token, Spice resumes the Change Stream from that token and skips the collection snapshot. If MongoDB rejects the token (typical codes ChangeStreamHistoryLost 286 or ChangeStreamFatalError 280, e.g. when the oplog window has rolled past the token's position), the behavior is governed by mongodb_resume_token_invalid_behavior above. Re-snapshotting a large collection is opt-in by default.

Datasets that are not file-accelerated (in-memory Arrow, etc.) do not get a sidecar row; restarts re-bootstrap from a fresh snapshot.

Secrets

Spice integrates with multiple secret stores to help manage sensitive data securely. For detailed information on supported secret stores, refer to the secret stores documentation. Additionally, learn how to use referenced secrets in component parameters by visiting the using referenced secrets guide.

Cookbook

• A cookbook recipe to configure MongoDB as a data connector in Spice. MongoDB Data Connector
• A cookbook recipe to stream changes from a MongoDB collection into Spice with Change Streams. MongoDB Change Streams