MongoDB Aggregation from Array

Question

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"}]} } );

Please list your documents, you can use $unwind to extract array into objects — noone
– noone, Commented Jun 7, 2019 at 14:24

Anton · Accepted Answer · 2019-06-07 15:56:16Z

1

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"}]

answered Jun 7, 2019 at 15:56

Anton

4,0902 gold badges15 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tyrionus Over a year ago

Thanks for your assistance. If I wanted to put this acquired data into a new collection would I add {"$out":"new-collection-name"} at the end? And if I wanted it to gather data with instances of 'at least one match' (one to many) not 'with only one match', would it be { $match: { "matches": { $size: {$gte: 1 }}}}

Anton Over a year ago

You are right about $out stage. Unfortunately, you can't see the result in the playground. mongoplayground.net/p/zTdAE6RNYA3 As for the size - your $size - based version would generate an error, but you can try something like { $match: { "matches.0" : { $exists : true }}} - try it out in the playground. However, since the transport._id seems to be the same as TRUCK.registration, there is no way you'll get more than one match here.

Anton Over a year ago

Here is the updated mongo playground mongoplayground.net/p/pKLtzVLZimC with {"registration":vid} and $size $gte condition just for completion.

Collectives™ on Stack Overflow

MongoDB Aggregation from Array

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related