Is this a complex aggregation?

As I work through M121 I realized I have a practical problem that I can solve with Mongo, but I am not sure I am framing the problem correctly such that I can find a solution. It seems simple, yet after searching and searching I still haven’t found the way to accomplish this. Here is the problem:

I have imported several csv’s represented by four different schemas into a collection. The only meaningful field that all four schemas share is a submissionID string. Within the data set each submissionID has one duplicate. I would like to find the documents that I can pair using this field and then output a new single document merging the fields unique to each document in the pair.

Is this possible? Have I just not reached the point in the course that shows me how to do this?

Thanks ahead for the help.

Tim

Hi @Timothy_Buelow, and welcome to the MongoDB Community forums! :wave:

Most likely, but we would need to see sample documents and the expected output to be sure.

If you’re trying to join data from different collections, then you’ll be using $lookup at some stage.

Thanks. Here’s an example of the schemas:

{
    "_id": {
      "$oid": "633dfb9fbc62a8315cd94d49"
    },
    "id": "ch_3Lkfh6EfIU9g9SME0DyAcCLk",
    "Created (UTC)": {
      "$date": {
        "$numberLong": "1663833780000"
      }
    },
    "Amount": {
      "$numberDecimal": "10.00"
    },
    "Fee": {
      "$numberDecimal": "0.53"
    },
    "Customer Email": "email2nidhis@gmail.com",
    "formID (metadata)": 222493708482160,
    "submissionID": 5396249997442000000
  }
 {
    "_id": {
      "$oid": "633dfaf7bc62a8315cd94b93"
    },
    "Date": {
      "$date": {
        "$numberLong": "1663045200000"
      }
    },
    "Description": "Class Parties Contribution  (1 student/class) (Amount: 10.00 USD) \nTotal: $10.00Transaction ID: pi_3LhfemEfIU9g9SME1dIse3oQ",
    "Classroom#/Teacher 1": "RM 104 - Karen Bell",
    "First Name": "Aarush",
    "Last Name": "Piya",
    "Email": "piyagagan@yahoo.com",
    "Are you interested in volunteering to help with classroom parties?": "No",
    "SubmissionID": 5389098710772900000
  }

Ideally, when paired by a matching submissionID number the output would look like:

  {
    "_id": {
      "$oid": "633dfaf7bc62a8315cd94b93"
    },
    "Date": {
      "$date": {
        "$numberLong": "1663045200000"
      }
    },
    "Amount": {
        "$numberDecimal": "10.00"
      },
      "Fee": {
        "$numberDecimal": "0.53"
      },
    "Description": "Class Parties Contribution  (1 student/class) (Amount: 10.00 USD) \nTotal: $10.00Transaction ID: pi_3LhfemEfIU9g9SME1dIse3oQ",
    "Classroom#/Teacher 1": "RM 104 - Karen Bell",
    "First Name": "Aarush",
    "Last Name": "Piya",
    "Email": "piyagagan@yahoo.com",
    "Are you interested in volunteering to help with classroom parties?": "No",
    "SubmissionID": 5389098710772900000
  }

Essentially, I would be appending the “Amount” and “Fee” fields to the resulting document.