CODEWITHSUNDEEP
mongodbaggregation-framework
bork
bork

Reputation: 1674

MongoDB: Conditional select from one collection based on another collection

I'm fairly new to MongoDB and need help doing a select, or perhaps some sort of left join, on one collection based on another collection's data.

I have two collections, animals and meals, and I want to get the animal(s) that has had it's last registered meal after a certain date (let's say 20171001) to determine if the animal is still active.

collection animals:
{
    name: mr floof,
    id: 12345,
    lastMeal: abcdef
},
{
    name: pluto,
    id: 6789,
    lastMeal: ghijkl
}

collection meals:
{
    id: abcdef,
    created: 20171008,
    name: carrots
},
{
    id: ghijkl,
    created: 20170918,
    name: lettuce
}

So the expected output of the query in this case would be:

{
    name: mr floof,
    id: 12345,
    lastMeal: abcdef
}

As Mr Floof has had his last meal 20171008, i.e. after 20171001.

Hope I was clear enough, but if not, don't hesitate to ask.

Upvotes: 3

Views: 2891

Answers (2)

s7vr
s7vr

Reputation: 75934

You can try below aggregation query.

db.animals.aggregate([ [
  {
    "$lookup": {
      "from": "meals",
      "localField": "lastMeal",
      "foreignField": "id",
      "as": "last_meal"
    }
  },
  {
    "$unwind": "$last_meal"
  },
  {
    "$match": {
      "last_meal.created": {
        "$gt": 20171001
      }
    }
  }
])

More info here.

You can use $project with exclusion after $match stage to format the response to exclude joined fields. Something like { $project: {"last_meal":0} }

Upvotes: 6

gajju_15
gajju_15

Reputation: 527

MongoDB supports joins with $lookup , In your case you can use query like:-

    db.animals.aggregate([
    {
      $lookup:
        {
          from: "meals",
          localField: "lastMeal",
          foreignField: "id",
          as: "last_meal"
        }
   },
  {
   $match: { 
          "created" : {
                $gt: "date" //your date format
          }
       } 
 }
])

thanks !

Upvotes: 1

Related Questions