Import data from csv and create relationships

Hi everyone, I’m new to mongodb and I’m trying to upload a big CSV file stored fictitious call records.
The some fields of this CSV file are:

ID FULL_NAME CALLING_NBR CALLED_NBR START_DATE CITY ADDRESS STATE CELL_SITE

I want to create 2 collection in the database: City and Person

The relationships are:

  • Embedded for documents about City
  • Linked for documents for Person

Precisely, I want a document like this inside the City collection:

    { 
       city
       state
       cell_site
       calls : [ 
                    {  id 
                       calling_nbr 
                       called_nbr 
                       start_date 
                       address
                     },
                     {...},
      ]
    }

Where attribute city is the index of the document.
And a document for Person collection like:

    {
      full_name
      phone_number
    }

Where the phone_number is the index of the document.

How can I do that ?
Thank you!

I’d do it in Python.

  1. Use Python library mod csv to read in fields
  2. Use mod pymongo to write out the collection(s) as you please.

Hi @GIANLUCA_CARBONE,

I would recommend you to do this in 2 stages:

  1. Load the csv file into a staging collection using mongoimport or any other preferred method as is to have each row as a document:
    https://docs.mongodb.com/manual/reference/program/mongoimport/
  2. Use $group aggregation with $out stage in the end to form city and person collections based on the staging aggregation.
    https://docs.mongodb.com/manual/reference/operator/aggregation/group/

https://docs.mongodb.com/manual/reference/operator/aggregation/out/

So for city aggregation I would go with something like :

db.staging.aggregate([{ $group : { _id : { city : "$city",
       state : "$state",
       cell_site: "$call_site"},
      calls : { $push : { ... }}
       },
      {$project : { ... }},
     {$out :  ... }]);

Thanks
Pavel

1 Like