Skip to main content

MongoDB

MongoDB is a document database. Load collections into MotherDuck with dlt, by exporting newline-delimited JSON, or through a managed change-data-capture connector.

MongoDB stores documents rather than rows, so loading it into MotherDuck is as much a flattening problem as a transfer problem. There is no DuckDB mongodb extension, so collections come across as JSON, either through a tool that handles the schema work or through an export you read yourself.

Load collections with dlt​

dlt has a MongoDB source and a MotherDuck destination, and it does the part you'd otherwise write by hand: it infers a schema from the documents, normalizes nested fields into columns and child tables, and evolves the schema as the documents change.

dlt init mongodb motherduck
pip install -r requirements.txt

Set the MongoDB connection string and your MotherDuck token, then run the pipeline:

export MOTHERDUCK_TOKEN="<motherduck_token>"
export SOURCES__MONGODB__CONNECTION_URL="mongodb+srv://<user>:<password>@<cluster>/"
python mongodb_pipeline.py

Configure which collections to load, and whether to load incrementally, in the generated pipeline script. See the dlt MongoDB source documentation for the source options.

Export JSON and read it​

For a one-time load or a small collection, export with mongoexport and read the file. The default output is one JSON document per line, which DuckDB reads natively:

mongoexport \
  --uri="mongodb+srv://<user>:<password>@<cluster>/<database>" \
  --collection=orders \
  --out=orders.json
CREATE TABLE orders AS
SELECT * FROM read_json('orders.json', format = 'newline_delimited');

DuckDB infers a schema from a sample of the documents, so nested objects become STRUCT columns and arrays become LIST columns. For a large export, write it to object storage and read from there instead of your local machine:

CREATE TABLE orders AS
SELECT * FROM read_json(
    's3://my-bucket/mongo-export/orders/*.json',
    format = 'newline_delimited'
);

Use a managed connector​

If you want a scheduled sync without writing pipeline code, several MotherDuck ingestion partners list MongoDB as a source: Airbyte, Fivetran, Estuary, and Streamkap. Streamkap and Estuary read MongoDB's change stream, so they suit change-data-capture rather than full reloads.

Things to know​

  • Extended JSON leaks into your columns. mongoexport writes BSON types as wrapper objects, so _id arrives as an object with an $oid key and dates as objects with a $date key. Project the values you want out of those wrappers after loading, or restrict the export with --fields to skip the types you don't need. dlt handles this conversion for you.
  • Schema inference samples. read_json infers types from the first documents it sees, so a field that only appears later, or changes type between documents, can be missed. Pass an explicit columns argument for a stable load, or set union_by_name = true when reading many files.
  • Flatten before you query. Querying STRUCT and LIST columns works, but downstream BI tools generally expect flat columns. Unnest the fields you report on into a curated table rather than making every consumer walk the document structure.
  • MongoDB stays the write path. MotherDuck is analytical. Keep application writes in MongoDB and treat MotherDuck as the read side for reporting.

Related content​