$lookup with same collection

Question

I am new to MongoDB so I am not sure if I have phrased my question correctly.

I have a collection in which data looks like this:

{ 
    "_id" : ObjectId("66666"), 
    "Id" : 994, 
    "PostType" : 1, 
    "AnswerId" : 334, 
    "CreationDate" : ISODate("1994-09-05T09:34:36.177+0000"), 
    "Tags" : "test", 
    "Parent" : null, 
}

{ 
    "_id" : ObjectId("8888"), 
    "Id" : 334, 
    "PostTypeId" : 2, 
    "AnswerId" : NaN, 
    "CreationDate" : ISODate("20005-08-03T11:29:42.880+0000"), 
    "ParentId" : 994
}

Both documents are in the same collection, im trying to use the AnswerId from a document with PostType:1, and using that value as the main Id to extract its CreationDate.

Here's the logic im trying to implement, but it doesnt seem to be working:

db.collection.aggregate([
    {$project:{_id:"$Id", Title:"$Title", Answerid:"$AnswerId", QuestionDate:"$CreationDate"}},
    {$match:{Id:"$Answerid"}},
    {$project:{AnswerDate:"$CreationDate"}}
])

I am open to any suggestion.

Curculio

Could you explain what you want to achieve? – Beaverbrook

I want to get the CreationDate from a document and use its AnswerId to get another CreationDate from a second document(using AnswerId as identifier) and then do some Date calculations. So for example, using the data above, I would get creation date for Id:2627 then use its AnswerId: 2629 as the new Id to get CreationDate of second document. – Curculio

Answer 1

You can try below aggregation in mongodb 3.6 and above

db.collection.aggregate([
  { "$match": { "Id": 2627 }},
  { "$lookup": {
    "from": "collection",
    "let": { "answerId": "$AnswerId" },
    "pipeline": [{ "$match": { "$expr": { "$eq": ["$Id", "$$answerId"] }}}],
    "as": "dates"
  }}
])

Or with the 3.4 and above

db.collection.aggregate([
  { "$match": { "Id": 2627 }},
  { "$lookup": {
    "from": "collection",
    "localField": "AnswerId",
    "foreignField": "Id",
    "as": "dates"
  }}
])

Beaverbrook

thank you. I didnt even know $lookup could be used within the same collection!! – Curculio