Export MongoDB Data

Introduction

In this lab, you will learn how to export MongoDB data in both JSON and CSV formats using the mongoexport utility. You will explore the step-by-step process of exporting data, selecting specific fields, and formatting the exported data. Additionally, you will learn how to verify the exported files to ensure the data has been successfully exported.

The lab covers the following key steps: exporting to JSON, exporting to CSV, selecting export fields, formatting export data, and verifying the exported files. By the end of this lab, you will have a solid understanding of how to efficiently export MongoDB data in various formats to meet your data management needs.

Skills Graph

Export to JSON

In this step, you'll learn how to export MongoDB data to JSON format using the mongoexport utility. JSON (JavaScript Object Notation) is a lightweight, human-readable data interchange format that's widely used for data storage and transfer.

First, let's ensure we have some data to export. We'll start by launching the MongoDB shell and creating a sample collection:

mongosh

Once in the MongoDB shell, create a database and add some sample data:

use exportlab
db.users.insertMany([
    { name: "Alice", age: 28, email: "[email protected]" },
    { name: "Bob", age: 35, email: "[email protected]" },
    { name: "Charlie", age: 42, email: "[email protected]" }
])
exit

Now, let's export this data to a JSON file. We'll use the mongoexport command in the terminal:

mongoexport --db=exportlab --collection=users --out=~/project/users.json

Let's verify the exported file:

cat ~/project/users.json

Example output:

{"_id":{"$oid":"..."},"name":"Alice","age":28,"email":"[email protected]"}
{"_id":{"$oid":"..."},"name":"Bob","age":35,"email":"[email protected]"}
{"_id":{"$oid":"..."},"name":"Charlie","age":42,"email":"[email protected]"}

Breaking down the mongoexport command:

• --db: Specifies the database name
• --collection: Specifies the collection to export
• --out: Defines the output file path

Export to CSV

In this step, you'll learn how to export MongoDB data to CSV (Comma-Separated Values) format using the mongoexport utility. CSV is a simple file format used to store tabular data, making it easy to open in spreadsheet applications like Excel.

We'll continue using the database and collection we created in the previous step. First, let's verify our existing data:

mongosh

In the MongoDB shell, switch to our existing database and check the collection:

use exportlab
db.users.find()
exit

Now, let's export the users collection to a CSV file. The key difference from JSON export is the --type=csv flag:

mongoexport --db=exportlab --collection=users --type=csv --fields=name,age,email --out=~/project/users.csv

Let's view the contents of the exported CSV file:

cat ~/project/users.csv

Example output:

name,age,email
Alice,28,[email protected]
Bob,35,[email protected]
Charlie,42,[email protected]

Breaking down the mongoexport command:

• --type=csv: Specifies CSV export format
• --fields: Lists the specific fields to export (order matters)
• --out: Defines the output file path

Note the key differences from JSON export:

• We explicitly specify which fields to export
• The output is a comma-separated format
• The first line contains column headers

Select Export Fields

In this step, you'll learn how to selectively export specific fields from your MongoDB collection. This is useful when you want to extract only certain information from your documents.

First, let's add more detailed documents to our collection to demonstrate selective exporting:

mongosh

In the MongoDB shell, add more comprehensive user documents:

use exportlab
db.users.drop() // Clear previous data
db.users.insertMany([
    {
        name: "Alice",
        age: 28,
        email: "[email protected]",
        address: {
            city: "New York",
            country: "USA"
        },
        skills: ["Python", "MongoDB", "Data Analysis"]
    },
    {
        name: "Bob",
        age: 35,
        email: "[email protected]",
        address: {
            city: "San Francisco",
            country: "USA"
        },
        skills: ["JavaScript", "React", "Node.js"]
    }
])
exit

Now, let's export only specific fields using different export methods:

1. Export only name and email:

mongoexport --db=exportlab --collection=users --type=csv --fields=name,email --out=~/project/users_contact.csv

2. Export with JSON to preserve nested structures:

mongoexport --db=exportlab --collection=users --type=json --fields=name,address --out=~/project/users_address.json

Let's verify the exported files:

echo "CSV Contact Export:"
cat ~/project/users_contact.csv
echo -e "\nJSON Address Export:"
cat ~/project/users_address.json

Example output for CSV:

name,email
Alice,[email protected]
Bob,[email protected]

Example output for JSON:

{"name":"Alice","address":{"city":"New York","country":"USA"}}
{"name":"Bob","address":{"city":"San Francisco","country":"USA"}}

Key points about field selection:

• Use --fields to specify exact fields you want to export
• For nested documents, the entire nested structure is preserved
• CSV exports flatten nested structures
• JSON exports maintain the original document structure

Format Export Data

In this step, you'll learn how to format and customize your MongoDB data exports using additional mongoexport options. We'll explore how to control the output format, limit exported documents, and apply filters.

First, let's add some more diverse data to our collection:

mongosh

In the MongoDB shell, update our users collection:

use exportlab
db.users.drop() // Clear previous data
db.users.insertMany([
    {
        name: "Alice",
        age: 28,
        department: "Engineering",
        salary: 85000,
        active: true
    },
    {
        name: "Bob",
        age: 35,
        department: "Marketing",
        salary: 72000,
        active: true
    },
    {
        name: "Charlie",
        age: 42,
        department: "Engineering",
        salary: 95000,
        active: false
    }
])
exit

Now, let's explore different export formatting techniques:

1. Export with Pretty Formatting (JSON):

mongoexport --db=exportlab --collection=users --type=json --pretty --out=~/project/users_pretty.json

2. Export Limited Number of Documents:

mongoexport --db=exportlab --collection=users --type=csv --fields=name,age,department --limit=2 --out=~/project/users_limited.csv

3. Export with Query Filter:

mongoexport --db=exportlab --collection=users --type=json --query='{"department":"Engineering"}' --out=~/project/engineering_users.json

Let's verify the exported files:

echo "Pretty JSON Export:"
cat ~/project/users_pretty.json

echo -e "\nLimited CSV Export:"
cat ~/project/users_limited.csv

echo -e "\nEngineering Users Export:"
cat ~/project/engineering_users.json

Key mongoexport formatting options:

• --pretty: Formats JSON with indentation for readability
• --limit: Restricts the number of documents exported
• --query: Filters documents based on specific conditions
• --fields: Selects specific fields to export

Example output for Pretty JSON:

{
    "_id": ObjectId("..."),
    "name": "Alice",
    "age": 28,
    "department": "Engineering",
    "salary": 85000,
    "active": true
}

Verify Exported Files

In this final step, you'll learn how to verify and validate the exported MongoDB files to ensure data integrity and correct export process. We'll use various Linux and MongoDB tools to check our exported files.

Let's start by reviewing the files we've exported in previous steps:

ls ~/project/

You should see files like:

• users.json
• users.csv
• users_contact.csv
• users_pretty.json
• users_limited.csv
• engineering_users.json

Now, let's verify these files using different methods:

1. Check File Size and Line Count:

echo "File Sizes:"
du -h ~/project/users*

echo -e "\nLine Counts:"
wc -l ~/project/users*

2. Validate JSON Syntax:

python3 -m json.tool ~/project/users_pretty.json > /dev/null

If the command runs without errors, the JSON is valid.

3. Count Exported Documents:

## Count documents in JSON files
echo "JSON Document Counts:"
jq -s 'length' ~/project/users.json
jq -s 'length' ~/project/engineering_users.json

4. Verify CSV Structure:

echo "CSV File Structure:"
head -n 2 ~/project/users_contact.csv

5. MongoDB Validation:

mongosh

In the MongoDB shell, let's compare exported data with original collection:

use exportlab
db.users.count()
exit

Compare this count with the number of documents in your exported files.

Example Verification Output:

File Sizes:
4.0K    /home/labex/project/users.json
...

Line Counts:
3 /home/labex/project/users.json
...

JSON Document Counts:
3
2

CSV File Structure:
name,email
Alice,[email protected]

Key Verification Techniques:

• Check file sizes
• Count document lines
• Validate JSON syntax
• Compare document counts
• Inspect file contents

Summary

In this lab, you learned how to export MongoDB data to both JSON and CSV formats using the mongoexport utility. For the JSON export, you created a sample database and collection, then used the mongoexport command to export the data to a JSON file. You also verified the exported file's contents. For the CSV export, you followed a similar process, exporting the same data to a CSV file, which can be easily opened in spreadsheet applications. The key steps covered were selecting the database and collection, defining the output file path, and understanding the various options available in the mongoexport command.