MongoDb $lookup query with multiple fields from objects array

Question

This question has previously been marked as a duplicate of this question I can with certainty confirm that it is not.

This is not a duplicate of the linked question because the elements in question are not an array but embedded in individual objects of an array as fields. I am fully aware of how the query in the linked question should work, however that scenario is different from mine.

I have a question regarding the $lookup query of MongoDb. My data structure looks as follows:

My "Event" collection contains this single document:

{
   "_id": ObjectId("mongodbobjectid..."),
   "name": "Some Event",
   "attendees": [
        {
           "type": 1,
           "status": 2,
           "contact": ObjectId("mongodbobjectidHEX1")
        },
        {
           "type": 7,
           "status": 4,
           "contact": ObjectId("mongodbobjectidHEX2")
        }
    ]
}

My "Contact" collection contains these documents:

{
    "_id": ObjectId("mongodbobjectidHEX1"),
    "name": "John Doe",
    "age": 35
},
{
    "_id": ObjectId("mongodbobjectidHEX2"),
    "name": "Peter Pan",
    "age": 60
}

What I want to do is perform an aggregate query with the $lookup operator on the "Event" collection and get the following result with full "contact" data:

{
   "_id": ObjectId("mongodbobjectid..."),
   "name": "Some Event",
   "attendees": [
        {
           "type": 1,
           "status": 2,
           "contact": {
              "_id": ObjectId("mongodbobjectidHEX1"),
              "name": "John Doe",
              "age": 35
           }
        },
        {
           "type": 7,
           "status": 4,
           "contact": {
              "_id": ObjectId("mongodbobjectidHEX2"),
              "name": "Peter Pan",
              "age": 60
           }
        }
    ]
}

I have done the same with single elements of "Contact" referenced in another document but never when embedded in an array. I am unsure of which pipeline arguments to pass to get the above shown result?

I also want to add a $match query to the pipeline to filter the data, but that is not really part of my question.

Just to clarify, I marked it as duplicate because the answers in that dupe still apply to your question; you need to apply the $unwind pipeline to flatten the array of embedded documents for you to apply the $lookup operator on the "attendees.contact" field and then after the $lookup, pipe another $unwind and $group the resulting docs to get your desired output. The other answer also applies if you are using the upcoming MongoDB 3.4 — chridam
– chridam, Commented Nov 15, 2016 at 12:00
Okay, so what you are saying is that I should first {"$unwind": "$attendees"}, then { "$lookup" : {"from" : "Contact", "localField" : "attendees.contact","foreignField": "_id", "as" : "contactlist" }}, then {"$unwind": "$contactlist"} and then $group? — Philipp Jahoda
– Philipp Jahoda, Commented Nov 15, 2016 at 12:07
Correct. A tip on debugging a pipeline; run the aggregation query at each pipeline stage, test whether the results tally for each step. So for example, in the aggregation above, first run db.event.aggregate([ { "$unwind": "$attendees" } ]); , check the result to see if the attendees array is deconstructed properly, add the next pipeline stage db.event.aggregate([ { "$unwind": "$attendees" },{ "$lookup" : {"from" : "Contact", "localField" : "attendees.contact","foreignField": "_id", "as" : "contactlist" }} ]);, run that and repeat the steps till you get to the final pipeline step. — chridam
– chridam, Commented Nov 15, 2016 at 12:14
Thank you very much, I managed to debug it to the last $unwind step and everything seems correct. At that stage the query on the "Events" collection which holds 1 document returns 2 documents (because of 2 attendees), each having a field "contactList" with the data of one contact. Would that be the expected outcome? Now I am unsure how to group this result to get the output described above — Philipp Jahoda
– Philipp Jahoda, Commented Nov 15, 2016 at 12:26
Thank you again for the effort, that is the correct answer. I will create a post and answer below. — Philipp Jahoda
– Philipp Jahoda, Commented Nov 15, 2016 at 13:26

Parshuram Kalvikatte · Accepted Answer · 2016-11-15 14:28:21Z

0

Try this one

 db.getCollection('Event').aggregate([{ "$unwind": "$attendees" },
        { "$lookup" : { "from" : "Contact", "localField" : "attendees.contact", "foreignField": "_id", "as" : "contactlist" } },
        { "$unwind": "$contactlist" },
         { "$project" :{
                    "attendees.type" : 1,
                    "attendees.status" : 1,
                    "attendees.contact" : "$contactlist",
                      "name": 1, "_id": 1
                       }
        },
        {
            "$group" : {
                _id : "$_id" ,
                "name" : { $first : "$name" }, 
                "attendees" : { $push : "$attendees" }
            }
        }
        ])

edited Nov 15, 2016 at 14:28

answered Nov 15, 2016 at 13:03

Parshuram Kalvikatte

1,6465 gold badges23 silver badges45 bronze badges

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

3 Comments

Philipp Jahoda Over a year ago

This query returns the desired output, but the Documents in the returned in the "attendees" array have no fields (contact, type, ... are missing). Do you know what might cause this?

Parshuram Kalvikatte Over a year ago

Can you tell me what you got with this query?

Ehsan Hejazi Over a year ago

you shouldn't unwind contactlist array otherwise you will miss the other parts and it will return the 0 index of array

Collectives™ on Stack Overflow

MongoDb $lookup query with multiple fields from objects array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related