How to migrate MongoDB data to PostgreSQL using mongoexport

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:[email protected]: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:[email protected]:27017/testdb" --collection tes --type csv --fields _id,class,status,company.address,manager.surname --out /tmp/collection_export.csv