Moving Between JSON and MongoDB Documents Without Loss

Data moves between JSON files and MongoDB all the time: seed data for tests, exports for analysts, migrations from and to other systems, backups. The move looks trivial because MongoDB documents are written like JSON, and it is trivial for a document of strings and small integers. For everything else the two formats differ, and the differences decide whether what comes out is what went in: dates become strings or wrapper objects, a 64-bit integer becomes a double that has lost its last digits, an array file is refused, a repeated import doubles the data. This guide follows the data in both directions, with the settings that keep it intact, and then covers what you do with documents once they are in: flatten them into tables, query nested fields, enforce a shape, and decide whether MongoDB or PostgreSQL's jsonb is the better place for them.

Every number comes from runs against MongoDB 8 and PostgreSQL 18 in containers, on files described in the notes. The shorter articles linked from each section have the detail.

What a document is that JSON is not

MongoDB stores BSON, a binary format that extends JSON with types and keeps key order. The differences that matter in a move:

The consequence: a JSON file carries no type information beyond string, number, boolean, null, array and object, and every tool that loads it must choose a BSON type for each value. Reading BSON back as JSON has the opposite problem: types that JSON lacks must be written as something. MongoDB's answer, Extended JSON, wraps them in objects with dollar keys. The move works when both ends agree on these choices, and goes wrong when they do not (how MongoDB BSON differs from JSON in types and size).

JSON into MongoDB

The tool is mongoimport, and its defaults assume JSON Lines. I imported plain JSON with a range of numbers and looked at the stored types:

1 -> int      5000000000 -> long      1.5 -> double      1e3 -> double
12345678901234567890 -> double (digits lost)       "2026-10-02T01:01:00Z" -> string

The type follows the value. A number too large for 64 bits is stored as a double and loses digits, so identifiers of that size belong in strings. Date strings stay strings: to get real dates, write them as {"$date": "..."} in the file, which the importer converts, and the same for {"$oid": ...}, {"$numberLong": ...} and {"$numberDecimal": ...}. A field with a plain ISO date string sorts and filters correctly as text and fails every date operator, so convert on load or with an update pipeline afterwards.

File shapes. A JSON Lines file is the default (20,000 orders in 0.35 s). A JSON array needs --jsonArray (0.48 s), and without the flag the error is Failed: cannot decode array into a D. The reverse mistake says found no opening bracket '['. A top-level wrapper such as {"posts": [...]} imports as one document with an array inside, and a wrapper over 16 MiB fails with object to insert too large. The cure for all of these is a look at the first byte and a jq unwrap (jq -c '.posts[]'), which converted a 25 MB array to 200,000 lines in 0.87 s (fixing mongoimport errors on JSON arrays).

Repeating an import. mongoimport adds documents and generates a fresh _id for each one that lacks it, so the same file twice gave 40,000 documents from 20,000, without any error. Three ways to prevent it: --drop (reload from scratch, 0.41 s), a unique index on your own key (the repeat then reports E11000 duplicate key per document and adds nothing), or --mode upsert --upsertFields id. The upsert needs the index too: 20,000 documents took 64.3 s without an index on id and 0.94 s with one, because each upsert is otherwise a collection scan (importing a JSON file with mongoimport without duplicates).

Bad data. One malformed line stopped the import at that document: error processing document #2: invalid character 'o' in literal, with the earlier documents already stored. A trailing comma in an array fails the same way. Validate the file before you import, and import into an empty collection or one with a unique index so that a retry is harmless.

Speed. One million events took 13.9 s with one worker, becoming 192 MB of BSON and 55 MB on disk after compression. --numInsertionWorkers 4 took 200,000 events from 3.4 s to 2.1 s. For comparison PostgreSQL loaded the same events with COPY in 3.95 s.

MongoDB into JSON

mongoexport writes one Extended JSON document per line. In the default relaxed mode _id becomes {"$oid": "..."}, dates {"$date": "2026-10-02T01:01:00Z"}, decimals {"$numberDecimal": "19.99"}, and numbers are bare. In canonical mode every number carries its type ({"$numberInt": "1"}) and dates are milliseconds. Query and field selection narrow the export (--query, --fields), CSV needs a field list and writes arrays and objects as JSON text in a quoted cell, and the million events exported in 9.6 s to a 205 MB file. An indexed query on user.id exported its 50 documents in 9 ms where the unindexed one scanned the collection in 249 ms (exporting MongoDB documents to JSON with mongoexport).

The part that surprises people is the dates. Relaxed mode writes ISO strings only for dates in 1970 to 9999; a date from 1969 came out as {"$date": {"$numberLong": "-14182980000"}}. So a consumer needs to accept both shapes. And a 64-bit value is a bare number in relaxed output, which a JavaScript parser rounds. When the export goes to a system that reads plain JSON, unwrap it: a jq walk that replaces the wrapper objects by their contents did it, with a branch for the long date form, in one command, and DuckDB reads the export as structs (created."$date"). PyMongo's json_util writes the same two modes, and a default function can write the plain form you choose (Extended JSON and why dates look strange in exports).

Does a round trip preserve the document?

I built one document with 19 fields of different types and moved it through both modes: ObjectId, int, long above 2^53, double, decimal, a date with milliseconds, a date from 1969, strings including an ISO date string, boolean, null, a mixed array, binary data, a regular expression, NaN, a timestamp, MinKey and a 5,000,000,000 long. Export, import into a new collection, and compare the BSON of both documents:

relaxed round trip    identical, 19 of 19 fields
canonical round trip  identical, 19 of 19 fields

Every type came back, including the ones with no JSON counterpart, because the importer reads the wrappers. The two cases that differ show on narrower tests. A long that holds a small value, such as 1, is written as a bare 1 in relaxed output and comes back as an int; in canonical output it is {"$numberLong": "1"} and stays a long. A double holding 1 is written 1.0 in relaxed mode, which is why it returned as a double. The 5,000,000,000 and the 9,007,199,254,740,993 stayed longs in relaxed mode because they do not fit in 32 bits. The rule: relaxed keeps values and, mostly, types; canonical keeps every type. For a backup or a copy between environments, use canonical, or mongodump, which is binary and exact (the events dumped to a 191.8 MB BSON file). For exchange with systems that read JSON, use relaxed and then unwrap.

Documents into relational tables

Flattening is the same exercise as for any nested JSON, with the aggregation pipeline as the tool (converting MongoDB documents to relational tables safely). A census with $objectToArray lists the fields and BSON types: in the posts, draft on 714 of 5,000, author.twitter on 454. Then one pipeline per array:

$unwind comments                            9,183 rows
$unwind comments, keep empty arrays        10,795 rows
$unwind tags then comments (both)          23,217 rows, likes sum 196,691
the true likes sum, comments alone                91,540

Unwinding two arrays in one pipeline multiplied the rows and inflated the sum by 2.15 times, the same trap as in SQL (why nested JSON does not fit a table). With one pipeline per array, $out writes a flat collection, mongoexport --type=csv writes it out, and PostgreSQL loaded the 9,183 comment rows with the likes total 91,540 and 3,388 distinct posts, equal to the source. Authors, embedded in every post, were regrouped with $group and $max on the optional twitter, giving 40 authors with a handle, where only 454 of the 5,000 copies showed one.

Querying nested fields

Dot notation reaches into objects and arrays ({"author.name": "Author 7"}), and the traps are specific. A query written in the shape of a subdocument matches only that exact field order: 120 posts in stored order and none with the same fields reordered. Two conditions on an array of objects match across different elements unless you use $elemMatch: 100 posts without it, 28 with it. {draft: null} matches both null and missing (4,286 posts), where $exists: true matched 714. And an index on a nested path turned an equality on user.id over 1,000,000 events from 1,000,000 documents examined in 398 ms to 50 in 2 ms (querying nested fields in MongoDB with dot notation).

Enforcing a shape

MongoDB accepts anything unless you add a validator, and $jsonSchema lets you write one with a subset of JSON Schema and bsonType. It rejected missing fields, wrong types, bad enum values, extra keys in a closed object, duplicate array items and a string where a number was required, each with Document failed validation, and it refused the keywords format, $ref, const, $schema, definitions and default. Adding a validator does not check existing documents; {$nor: [{$jsonSchema: schema}]} finds them (167,333 in the million events). Imported into a validated collection, 200,000 events loaded 166,533 and rejected 33,467, the same count that Python's jsonschema, Ajv and fastjsonschema found (using JSON Schema validation in MongoDB collections, and validating JSON with JSON Schema in real projects for the standard).

Where should the documents live?

If the JSON sits next to relational data and needs joins and constraints, PostgreSQL's jsonb is a good home; if the document is the unit of work, MongoDB is. On the same million events: PostgreSQL loaded in 3.95 s, MongoDB in 13.9 s; MongoDB held them in 55 MB on disk against 277 MB; four aggregations ran in 0.3 to 1.2 s in PostgreSQL and 0.18 to 0.26 s in MongoDB; a join to a users table was a single query in PostgreSQL (choosing between JSONB in PostgreSQL and MongoDB in practice). The data and the team decide more than the benchmarks.

A worked move: blog documents to PostgreSQL

Here is the whole path for one realistic task: the blog export, 5,000 posts with an embedded author, a list of tags and a list of comments, has to land in PostgreSQL for reporting.

Step one is the file. head -c 20 blog.json shows {"exported": ..., a wrapper object, so the posts are under a key. jq -c '.posts[]' blog.json > posts.ndjson unwraps it in 0.06 s into 5,000 lines. Step two is the import into MongoDB, only because the source system lives there in this story: mongoimport --db blog --collection posts --file posts.ndjson, and countDocuments() says 5,000, equal to wc -l. A wrapper imported directly would have said 1.

Step three is the census, because the tables are not designed yet. The $objectToArray pipeline shows author and tags and comments on every post, draft on 714, twitter inside author on 454. Step four is a pipeline per child: comments unwound into 9,183 rows with post_id, tags unwound into 9,964 links, authors regrouped into 40 rows with $max on the optional handle. Each goes to a flat collection with $out, each exports with mongoexport --type=csv, each loads with \copy.

Step five is the check, and the numbers carry it. In PostgreSQL the comments table has 9,183 rows and sum(likes) is 91,540, the same as the sum computed in MongoDB over the unwound comments; 3,388 posts have comments; the junction table has 9,964 rows over 10 tags; there are 40 authors, all with a handle. If an unwind had been applied to both arrays in one pass, the comment count would have been 23,217 and the sum 196,691, and the check would have failed on its first line.

Step six is what stays out: draft becomes a boolean column with a default of false on the posts table, and the absent-versus-null difference that the CSV loses is a decision written next to it. The raw export stays in object storage, in case a mapping needs to be redone. The whole path, from a wrapper file to checked tables, is a short script, and every step has a count to compare, which is what makes it repeatable.

Doing it from code instead of the tools

The command-line tools are the right choice for files. For anything with logic in it, a driver gives you control that flags do not. With PyMongo, loading the 200,000 events:

insert_one in a loop (first 5,000 only)            1.09 s   (about 4,600 documents a second)
insert_many, batches of 1,000, ordered             1.35 s   (200,000 documents)
insert_many, batches of 1,000, unordered           1.36 s
bulk_write upsert on an indexed key, first run     7.33 s
bulk_write upsert on an indexed key, second run    5.44 s   (no change in the data)
find all, batch_size 5000                          0.36 s
export to JSON Lines with bson.json_util           3.88 s

Batched inserts loaded 200,000 documents in 1.35 s, faster than mongoimport with one worker (3.4 s) and with four (2.1 s) for the same events, because the driver was already connected and parsed the file once in Python. A loop of single inserts is a trap: 5,000 documents took 1.09 s, so the full 200,000 would take about 45 s, 33 times slower than batching. Unordered batches did not matter here; they let the server continue past a failed document and report the failures at the end, which is what you want for dirty data. Upserts cost more than inserts, 7.33 s for the first pass and 5.44 s for a second pass that changed nothing, because every operation looks up its key (the index on id makes that cheap) and rewrites matching documents. Reading is fast (0.36 s for all 200,000 documents, in batches of 5,000), and writing them out through json_util took 3.88 s, since the conversion to Extended JSON text dominates.

The code route is also where you apply the type decisions in one place: convert dates and ids as you read the file, hold the decimals as Decimal128, reject records that fail your own checks and write them to a reject file. A file that goes through mongoimport gets types by value; one that goes through your code gets the types you chose.

Fixing types after the import

If a collection already holds strings where dates should be, an update with an aggregation pipeline converts them on the server:

db.events.updateMany({}, [{$set: {ts: {$toDate: "$ts"}}}])

On 200,000 documents it took 2.3 s and every ts became a date. $toDate fails the whole update if one value cannot be converted; $convert takes onError and onNull values and lets you choose. A test of $convert to double over six strings gave 12.0, 7.5, the marker for "abc", the null marker, 1000.0 for "1e3", and the error marker for " 5 ": leading and trailing spaces are not accepted, so trim first ($trim). Run a conversion against a copy before the real collection, and count what fell into the error branch afterwards.

The rule for each kind of field, in short:

Practical details of running the tools

The tools talk to the server through a connection string (--uri "mongodb://user:password@host:27017/db"), which belongs in a file or environment variable and not on a command line that others can see in a process list. In a container, paths are the container's: a file on your host is not visible until you copy it or mount the folder, which is the reason for most no such file or directory errors. --quiet removes the progress bar; leave it off in scripts that you want to log. Exports of large collections should run against a secondary or a restored backup, not a busy primary, and long imports are best done into a new collection that you rename into place when it is complete (renameCollection is atomic), so that readers never see a half-loaded set. Keep the 16 MiB limit in mind for anything you build by unwinding or grouping into one document. And record, next to every dataset, the tool versions and the options used, since the Extended JSON defaults changed across versions and an old file may be in a form that a new tool reads differently.

Cross-checking the engines

When the same data lives in two places, make them answer the same question and compare. On the million events, MongoDB's aggregation pipeline, PostgreSQL's jsonb and DuckDB over the file gave the same answers to the same questions: 250,000 events of each of the four types, 20,000 distinct users, /p/0 as the top page with 5,000 hits, and the same sums of amount for the three plans in MongoDB and PostgreSQL, to the cent: 4,171,528.70 for free, 4,181,350.29 for pro and 4,163,664.64 for team. The match is the evidence that nothing was lost in either load, and it is cheap: four queries per engine. When two engines disagree, the explanation is usually in this guide: a number stored as a double in one and a decimal in the other, a string date compared as text, a missing field counted as null in one system and left out in the other, or a duplicated import.

A single "canary" document is the other cheap test. Keep a document that holds one value of every type you use, including the awkward ones (a date before 1970, a long above 2^53, a decimal, an empty array, a null, a key with a dot), and send it through every route your data takes. The 19-field document came back identical from both Extended JSON modes. Run it after every tool upgrade, because defaults change, and before relying on a new export path. If the canary changes, you know which type and which step.

Keep a log of the move

A move you can repeat is a move you can audit, and the log is a few lines per run: the source file with its size and checksum, the tool and its version, the options, the number of records read, imported, rejected, and the first reject with its position, and the checks that passed, with their values (document count, a control total, the distinct key count). Write it to a table or a text file next to the data. When someone asks six months later why a count differs from the source system, the log answers whether it differed on the day, and which step introduced it. It also makes rollback concrete: a collection loaded under a name that includes the run, swapped in at the end, can be dropped without touching anything else. The few minutes it takes to write the log are the cheapest part of a migration, and the part most often skipped.

When two systems disagree about meaning

Mechanical differences are the easy part. The harder ones are in meaning. A date with no zone is a local time to one team and UTC to another; BSON dates are UTC instants, and a string date is whatever its writer meant. A decimal with two places is money in one system and a float approximation in another; carry money as Decimal128 or as integer cents, and say which. An identifier is a number to the source, a string to the destination, and the leading zeros matter to the people who read it. Null and missing are separate in MongoDB and JSON, and merged in CSV and in most SQL results; if "never set" and "set to nothing" differ for your business, keep them apart through the whole route. Key order is data inside a MongoDB subdocument and nothing in a JSON object. Settle each of these with the other team before the first import, write the answer in the field list, and the technical move becomes the easy part.

Keep a field dictionary next to the data: name, type in the file, BSON type, SQL type, whether it can be absent, whether null is meaningful, and the unit or zone. It is the document that the census, the validator, the SQL table and the export filter all derive from, and it is cheaper to keep right than to reconstruct after an incident.

A checklist for a move

1. Look at the file: first byte, whole-file parse, record count.

2. Decide the types you want for dates, ids and money, and how the file will say so.

3. Import into an empty collection or one with a unique index; index upsert keys first.

4. Compare counts with the file, and sample a few documents with $type on the fields that matter.

5. For export, pick the form by the reader: canonical for exact copies, relaxed then unwrapped for plain JSON consumers.

6. For analysis, flatten one array at a time and compare counts and a total with the documents.

7. Add a validator once the shape is settled, in moderate level first.

When the move is not worth making

The data is already where it is used. Moving a collection to a relational database for the sake of SQL is a project; querying in place with the aggregation framework, or exporting to Parquet for analysis, is often enough.

The two systems disagree about meaning. Time zones, decimal scales and identifiers are the usual places. Agree on them first, in writing.

You need continuous sync. A one-off move with files is not a replication system; use change streams or a connector for ongoing needs.

The reference documents are the MongoDB manual on Extended JSON and the Database Tools pages for mongoimport and mongoexport. For a document on screen, use the JSON formatter.