Migration of Semi-Structured JSON and XML Data: in MongoDB using Python

Introduction

When working with data, you often come across different formats like JSON and XML. These formats are widely used for storing and transmitting data, especially in web services and APIs. MongoDB, a popular NoSQL database, is perfect for storing this kind of semi-structured data.

In this article, I’ll walk you through the process of reading JSON and XML files from folders and storing them in separate collections in MongoDB. Don’t worry if you’re new to this—I’ll explain everything in simple terms!

Step 1: Setting Up MongoDB

Before we start working with data, we need to set up a MongoDB database. If you don’t have MongoDB installed, you can download it from MongoDB’s official website.

pip install pymongo xmltodict

Once MongoDB is installed, open your terminal and start MongoDB by running:

mongod

This command starts the MongoDB server on your local machine.

Step 2: Installing Python and Required Libraries

We’ll use Python to read our files and interact with MongoDB. Make sure you have Python installed on your system. You can download Python from the official Python website.

Next, install the required Python libraries by running:

pip install pymongo xmltodict

• pymongo: A library to connect Python with MongoDB.
• xmltodict: A library to convert XML files into a format Python can easily work with (a dictionary).

Step 3: Connecting to MongoDB with Python

Let’s start by connecting to MongoDB from our Python script. We’ll also create separate collections for storing JSON and XML data.

from pymongo import MongoClient

# Connecting to MongoDB
client = MongoClient("mongodb://localhost:27017/")
db = client["car_db"]  # Replace with your database name

# Creating separate collections for JSON and XML data
json_collection = db["cars_raw1"]
xml_collection = db["cars_raw2"]

print("Connected to MongoDB and ready to store data!")

Step 4: Processing and Storing JSON Files

Now that we’re connected to MongoDB, let’s read the JSON files from a folder and store them in the cars_raw1 collection.

import os
import json

json_folder = "/path/to/json_folder"  # Replace with the path to your JSON folder

print(f"Processing JSON files in folder: {json_folder}")
for file_name in os.listdir(json_folder):
    if file_name.endswith('.json'):
        file_path = os.path.join(json_folder, file_name)
        print(f"Reading JSON file: {file_path}")
        with open(file_path, 'r') as file:
            data = json.load(file)  # Convert JSON file to a Python dictionary
            json_collection.insert_one(data)  # Insert the data into the JSON collection
            print(f"Inserted {file_name} into JSON collection")

Step 5: Processing and Storing XML Files

Next, we’ll read XML files from another folder and store them in the cars_raw2 collection.

import xmltodict

xml_folder = "/path/to/xml_folder"  # Replace with the path to your XML folder

print(f"Processing XML files in folder: {xml_folder}")
for file_name in os.listdir(xml_folder):
    if file_name.endswith('.xml'):
        file_path = os.path.join(xml_folder, file_name)
        print(f"Reading XML file: {file_path}")
        with open(file_path, 'r') as file:
            data = xmltodict.parse(file.read())  # Convert XML file to a Python dictionary
            xml_collection.insert_one(data)  # Insert the data into the XML collection
            print(f"Inserted {file_name} into XML collection")

Step 6: Running the Script

Now that we have written the script, you can run it to process and store all the JSON and XML files in their respective MongoDB collections.

Just ensure that the folders json_folder and xml_folder contain the JSON and XML files, respectively. Then, run the script using:

python your_script_name.py

The script will read each file, convert it to a format that MongoDB can understand, and store it in the appropriate collection.

Conclusion

And that’s it! You’ve successfully stored JSON and XML files in MongoDB using Python. This process can be easily adapted to handle different types of data and more complex workflows. By storing your data in MongoDB, you can efficiently manage and query your data whenever needed.

I hope this guide was helpful. Feel free to experiment with the script and see what else you can do with MongoDB and Python!