MongoDB is a non-relational database and the target database platform is relational – PostgreSQL. Is there a simple approach to migrating data from MongoDB to PostgreSQL?

First problem – defining a target schema .

Second problem – tweaking the target schema for any changes made by the developers on the MongoDB side aka Evolving Schema. As a one-off migration – probably not such a major problem – but if your basing a DIY ETL process on mongoexport – than it’s something you’ll need to consider

There is a tool provided by MongoDB called mongoexport and it’s capable of exporting csv files.

mongoexport is a command-line tool that produces a JSON or CSV export of data stored in a MongoDB instance.  Make sure you execute mongoexport as a command line utility not as a mongo shell utility

In summary

Step 1) `mongoexport —type=csv` data to csv-file

Step 2 )Create DDL of  table in PostgreSql and “copy” data to it

An example of the mongoexport command line statement and the messages

mongoexport –uri “mongodb://my_usr:my_pwd@mymongoserver.net:27017/testdb” –collection tes –type csv –fields _id,class,status –out /tmp/collection_export.csv

2019-09-11T16:39:56.693+0100 connected to: localhost
2019-09-11T16:39:56.693+0100 exported 77 records

An example of loading the data into a PostgreSQL database table

`COPY myTable(ID,class,status)

FROM ‘/tmp/collection_export.csv’ DELIMITER ‘,’ CSV HEADER;`

How to export subfields in a sub document? A common problem is exporting sub fields in a sub document and how use the –fields parameter.     Extending the mongoexport example above, you’ll notice I’ve added company.address and manager.surname , as examples of sub fields in a sub document

mongoexport –uri “mongodb://my_usr:my_pwd@mymongoserver.net:27017/testdb” –collection tes –type csv –fields _id,class,status,company.address,manager.surname –out /tmp/collection_export.csv

Author: Jack Vamvas