mongodb aggregate get values from another query-CodePudding

I have two collections

A collection to save following users
A collection for user stories

I want to see only the stories of the users I have followed

This is an example of my database

db = {
  "follow": [
    {
      "_id": 1,
      "start": "user1",
      "end": "user2",
      
    },
    {
      "_id": 2,
      "start": "user2",
      "end": "user3",
      
    }
  ],
  "story": [
    {
      "_id": 1,
      "owner_id": "user2",
      "updated_at": 1638090000,
      "deleted_at": null
    },
    {
      "_id": 2,
      "owner_id": "user3",
      "updated_at": 1638080000,
      "deleted_at": null
    }
  ]
}

I want such a query. Of course, this is wrong:

db.story.aggregate([
  {
    "$match": {
      "deleted_at": null,
      "updated_at": {
        $gte: 1638081000
      },
      "owner_id": {
        $in: [
          db.follow.find({"start": "user1"})
        ]
      }
    }
  }
])

Note that I want all of this to be done in one query

query in mongo playground : https://mongoplayground.net/p/ysRUDW4hRzh

CodePudding user response：

You should use $lookup here

db.follow.aggregate([
  {
    "$match": {
      "start": "user2"
    }
  },
  {
    $lookup: {
      from: "story",
      localField: "end",
      foreignField: "owner_id",
      pipeline: [],
      as: "stories",
    }
  },
  
])

this query lookups story database where localField (end in this case) equals foreignField (owner_id in this case) then puts on a stories field

result: https://mongoplayground.net/p/0M6YxYWAQ2n