CODEWITHSUNDEEP
mongodb
Tyrionus
Tyrionus

Reputation: 23

MongoDB Aggregation from Array

Hi I am very new to mongoDB, and I am looking to aggregate some data from one collection to another that I will create with $out. Basically I have a collection of data that has information stored inside an array, and I want to put data into the new collection, if that same data has been inserted into the array at least once. What would be the best function to do this. I was looking at $filter and $elemMatch.

Basically if any of the truck's registration number appears in any of the user type mechanic maintenances arrays at least once, then I want to save that information in a new collection like {"registration":"truck-registration"}, with no duplicates.

What would be the best way to do this?

These are just a few of many database inserts (there are many users and many more trucks).

Trucks

db.transport.insert ( {"_id":"GFT008", "TRUCK": { "registration":"GFT008", "capacity":40000, "weight":15000, "status":"AVAILABLE"} });
db.transport.insert ( {"_id":"PKR008", "TRUCK": { "registration":"PKR008", "capacity":22000, "weight":8800, "status":"AVAILABLE"} });  
db.transport.insert ( {"_id":"QRT834", "TRUCK": { "registration":"QRT834", "capacity":5550, "weight":400, "status":"USED"} } );

Employees

db.transport.insert ( {"_id":"6", "EMPLOYEE": { "e#":"6", "name":"Michael Jones", "dob":"05-OCT-65", "address":"23 Waterloo Ave. Surry Hills, NSW 2502", "hiredate":"12-JAN-93","position":"mechanic","licence":"7773","status":"ON_LEAVE","experience":"STANDARD","maintenances":[{"registration":"QRT834","time":"40","maintenance date":"12-JUN-99"},{"registration":"QRT834","time":"40","maintenance date":"15-JUN-98"},{"registration":"SYF777","time":"30","maintenance date":"01-AUG-98"},{"registration":"SYF777","time":"30","maintenance date":"05-AUG-95"},{"registration":"SYF777","time":"30","maintenance date":"06-AUG-00"},{"registration":"LUCY01","time":"200","maintenance date":"12-MAR-97"},{"registration":"SYF777","time":"30","maintenance date":"02-AUG-00"},{"registration":"PKR768","time":"200","maintenance date":"12-AUG-00"},{"registration":"QRT834","time":"200","maintenance date":"30-JUN-00"},{"registration":"SYF777","time":"300","maintenance date":"02-AUG-02"},{"registration":"PKR768","time":"460","maintenance date":"12-AUG-02"},{"registration":"LUCY01","time":"40","maintenance date":"29-JUL-02"},{"registration":"QRT834","time":"40","maintenance date":"30-JUN-02"}]} } );

Upvotes: 0

Views: 67

Answers (1)

Anton
Anton

Reputation: 4052

Take a look:

Live demo at MongoPlayground

db.employee.aggregate([
  {
    $unwind: "$EMPLOYEE.maintenances"
  },
  {
    $lookup: {
      from: "transport",
      localField: "EMPLOYEE.maintenances.registration",
      foreignField: "TRUCK.registration",
      as: "matches"
    }
  },
  {
    $match: {
      "matches": {
        $size: 1
      }
    }
  },
  {
    $group: {
      _id: "$EMPLOYEE.maintenances.registration",

    }
  }
])

It's a multi-stage aggregation: - Unwind array - Join unwinded stage with transport records - Filter records with exactly one(?) match - and finally - remove duplicates by grouping

Result:

[{"_id": "QRT834"}]

Upvotes: 1

Related Questions