Export MongoDB collection/table and import into MariaDB

In this example, I will briefly show you how all MongoDB tables can be exported as JSON. A .json file (as a table) is then specifically selected and transferred to CSV format so that the CSV file can later be conveniently imported into MariaDB via phpMyAdmin. The script in this example can of course be used directly on the host system, even if MongoDB is running in a Docker container.

Install the tools:

sudo apt install -y mongodb-database-tools
sudo apt-get install jq

Export all tables of the database admin via export.sh:

#!/bin/bash

DB="admin"
AUTH="mongodb://user:pass@localhost:27017/$DB"
EXPORT_DIR="/root/mongo_exports"

mkdir -p "$EXPORT_DIR"

collections=$(mongosh "$AUTH" --quiet --eval "db.getCollectionNames().join(' ')" )

for col in $collections; do
    echo "Exporting $col..."
    mongoexport --uri="$AUTH" --collection="$col" --out="$EXPORT_DIR/${col}.json" --jsonArray
done

copy if necessary:

mv /root/mongo_exports/ /var/www/vhosts/example.com/
cd /var/www/vhosts/example.com/

convert json to csv:

jq -r '(.[0] | keys_unsorted) as $cols | $cols, map([.[ $cols[] ]])[] | @csv' table.json > table.csv

If there are nested objects, an error like “jq: error (at table.json:1): object ({“$oid”: “68…) is not valid in a csv row” will appear – solution:

jq -r '
  (.[0] | keys_unsorted) as $cols |
  $cols,
  map(
    [ $cols[] as $c |
      if (.[ $c ] | type) == "object" then
        (.[ $c ] | tojson)
      else
        .[ $c ]
      end
    ]
  )[] | @csv
' table.json > table.csv

View the header row and create the corresponding CREATE statements, e.g:

CREATE TABLE EXAMPLETABLE (
  _id VARCHAR(255) PRIMARY KEY,
  userId VARCHAR(255),
  name VARCHAR(255),
  email VARCHAR(255),
  phone VARCHAR(50),
  createdAt DATETIME
);

and execute in phpmyadmin as SQL. Then select the phpmyadmin table and import the csv file. Done 🙂