Sending in a MongoDB Array to get results from a different collection in MongoDB

Question

I have a MongoDB Collection that contains timecards. I have a 2nd collection that contains locations. Normally I would do this if I was looking for anyone that was oncall at the time (you have to have a signout for every signin):

TimeCard.aggregate([
    { $match: {agency_id: req.query.agency_id}},
    { $sort: { user_id: 1, received_time: -1 }},
    { $group: { _id: "$user_id", count_types: { $sum: 1 }, lastTime: { $last: "$received_time" }}},
    { $match: { count_types: { $mod: [2, 1] }}},
    { $lookup: { from: "locations", localField: "_id", foreignField: "user_id", as: "Test" }}
], function(err, docs){
        if(err){console.log(err);}
        else {res.json(docs);}
    });

And this would give me this result:

 [
   {
    "_id": "123-88",
    "count_types": 5,
    "lastTime": "2017-04-20T01:30:18.713Z",
    "Test": [
      {
        "_id": "58fa4021ffa99100116585e0",
        "user_id": "123-88",
        "agency_id": "44",
        "contract_region_id": "contract-region-id-007",
        "department_id": "department-id-42",
        "shift_id": "shift-id-45",
        "unit_id": "unit-id-88",
        "received_time": "2017-04-21T17:23:45.672Z",
        "location": "Science Lab",
        "__v": 0
      },
      {
        "_id": "58fed3efdac1bd00112a914b",
        "user_id": "123-88",
        "agency_id": "44",
        "contract_region_id": "contract-region-id-007",
        "department_id": "department-id-42",
        "shift_id": "shift-id-45",
        "unit_id": "unit-id-88",
        "received_time": "2017-04-25T04:43:27.501Z",
        "location": "Gym",
        "__v": 0
      }
    ]
  }
]

Now I could have multiple users in the array all with their own location data. What I want is only the Last location they were at (based on received_time inside Test array). So my best guess is that I would need to first just get a list of user_ids and then call the second collection and pass in the array to get the results but I am not sure how to do this correctly or if that is even the best way to do this. Thanks again for your help.

I'm sorry, I don't completely understand what you are asking to do. Can you clarify a bit? — mcadio
– mcadio, Commented Apr 25, 2017 at 6:12
Sure. What I want is what is in TEST to only have 1 record (the latest one). So in the example output above I would only see location Gym and not the Science Lab (since Gym is the location where the user was last). — Justin Yanta
– Justin Yanta, Commented Apr 25, 2017 at 6:22

chridam · Accepted Answer · 2017-04-25 08:23:34Z

1

Use the $filter operator to get the only element in the Test array with the received_time key matching the aggregated lastTime field. You can apply this within an $addFields pipeline step if your MongoDB Server version is 3.4 or greater:

TimeCard.aggregate([
    { $match: {agency_id: req.query.agency_id}},
    { $sort: { user_id: 1, received_time: -1 }},
    { $group: { _id: "$user_id", count_types: { $sum: 1 }, lastTime: { $last: "$received_time" }}},
    { $match: { count_types: { $mod: [2, 1] }}},
    { $lookup: { from: "locations", localField: "_id", foreignField: "user_id", as: "Test" }},
    {
        $addFields: {
            Test: {
                $filter: {
                    input: "$Test",
                    as: "user",
                    cond: { $eq: ["$lastTime", "$$user.received_time"] }
                }
            }
        }
    }
], function(err, docs){
    if(err){console.log(err);}
    else {res.json(docs);}
});

If your MongoDB server does not have support for $addFields then use the $project pipeline instead:

TimeCard.aggregate([
    { $match: {agency_id: req.query.agency_id}},
    { $sort: { user_id: 1, received_time: -1 }},
    { $group: { _id: "$user_id", count_types: { $sum: 1 }, lastTime: { $last: "$received_time" }}},
    { $match: { count_types: { $mod: [2, 1] }}},
    { $lookup: { from: "locations", localField: "_id", foreignField: "user_id", as: "Test" }},
    {
        $project: {
            count_types: 1,
            lastTime: 1,
            Test: {
                $filter: {
                    input: "$Test",
                    as: "user",
                    cond: { $eq: ["$lastTime", "$$user.received_time"] }
                }
            }
        }
    }
], function(err, docs){
    if(err){console.log(err);}
    else {res.json(docs);}
});

answered Apr 25, 2017 at 8:23

chridam

104k26 gold badges246 silver badges243 bronze badges

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

1 Comment

Justin Yanta Over a year ago

We are using MongoDB 3.2 so I am trying the $project. One issue is that the $lastTime will not match the $$user.received_time. The $$user.received_time might be later because if they moved locations and checked but were still oncall they would have a latter time in the user.received_time but the TimeCard $lastime would of not changed.

Collectives™ on Stack Overflow

Sending in a MongoDB Array to get results from a different collection in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related