$lookup nested array in mongodb

Question

I am struggling with the newish (lovely) lookup operator in MongoDB. I have 3 collections:

artists

{ 
    "_id" : ObjectId("5b0d2b2c7ac4792df69a9942"), 
    "name" : "Dream Theater", 
    "started_in" : NumberInt(1985), 
    "active" : true, 
    "country" : "US", 
    "current_members" : [
        ObjectId("5b0d2a7c7ac4792df69a9941")
    ], 
    "previous_members" : [
        ObjectId("5b0d2bf57ac4792df69a9954")
    ], 
    "albums" : [
        ObjectId("5b0d16ee7ac4792df69a9924"), 
        ObjectId("5b0d47667ac4792df69a9994")
    ], 
    "genres" : [
        "prog metal", 
        "prog rock"
    ]
}

Albums

{ 
    "_id" : ObjectId("5b0d16ee7ac4792df69a9924"), 
    "title" : "Images and words", 
    "released" : ISODate("1992-07-07T00:00:00.000+0000"), 
    "songs" : [
        ObjectId("5b0d15ab7ac4792df69a9916"), 
        ObjectId("5b0d15ee7ac4792df69a991e"), 
        ObjectId("5b0d2db37ac4792df69a995d"), 
        ObjectId("5b0d2dbe7ac4792df69a995e"), 
        ObjectId("5b0d2dcb7ac4792df69a995f"), 
        ObjectId("5b0d2dd87ac4792df69a9960"), 
        ObjectId("5b0d2de27ac4792df69a9961"), 
        ObjectId("5b0d2dec7ac4792df69a9962")
    ], 
    "type" : "LP"
}
{ 
    "title" : "Awake", 
    "released" : ISODate("1994-10-04T00:00:00.000+0000"), 
    "songs" : [
        ObjectId("5b0d470d7ac4792df69a9991")
    ], 
    "type" : "LP", 
    "_id" : ObjectId("5b0d47667ac4792df69a9994")
}

Songs

{ 
    "_id" : ObjectId("5b0d15ab7ac4792df69a9916"), 
    "title" : "Pull me under"
}
{ 
    "_id" : ObjectId("5b0d15ee7ac4792df69a991e"), 
    "title" : "Another day"
}
{ 
    "title" : "Take the time", 
    "_id" : ObjectId("5b0d2db37ac4792df69a995d")
}
{ 
    "title" : "Surrounded", 
    "_id" : ObjectId("5b0d2dbe7ac4792df69a995e")
}
{ 
    "title" : "Metropolis - part I", 
    "_id" : ObjectId("5b0d2dcb7ac4792df69a995f")
}
{ 
    "title" : "Under a glass moon", 
    "_id" : ObjectId("5b0d2dd87ac4792df69a9960")
}
{ 
    "title" : "Wait for sleep", 
    "_id" : ObjectId("5b0d2de27ac4792df69a9961")
}
{ 
    "title" : "Learning to live", 
    "_id" : ObjectId("5b0d2dec7ac4792df69a9962")
}
{ 
    "title" : "6:00", 
    "_id" : ObjectId("5b0d470d7ac4792df69a9991")
}

I can easily do an aggregation with $lookup to get the detailed albums array, but how do I get also the detailed songs in the corresponding albums? I would like to extend the following query:

db.artists.aggregate([ {
    $lookup: {
           from: "albums",
           localField: "albums",    
           foreignField: "_id",
           as: "albums"
    }
}]).pretty()

Ashh · Accepted Answer · 2018-06-14 17:00:18Z

29

If you have mongodb version 3.6 then you can try with nested $lookup aggregation...

db.collection.aggregate([
  { "$lookup": {
    "from": Albums.collection.name,
    "let": { "albums": "$albums" },
    "pipeline": [
       { "$match": { "$expr": { "$in": [ "$_id", "$$albums" ] } } },
       { "$lookup": {
         "from": Songs.collection.name,
         "let": { "songs": "$songs" },
         "pipeline": [
           { "$match": { "$expr": { "$in": [ "$_id", "$$songs" ] } } }
         ],
         "as": "songs"
       }}
     ],
     "as": "albums"
  }}
 ])

And for long-winded explanation you can go through $lookup multiple levels without $unwind?

Or If you have mongodb version prior to 3.6

db.collection.aggregate([
  { "$lookup": {
    "from": Albums.collection.name,
    "localField": "albums",
    "foreignField": "_id",
    "as": "albums"
  }},
  { "$unwind": "$albums" },
  { "$lookup": {
    "from": Songs.collection.name,
    "localField": "albums.songs",
    "foreignField": "_id",
    "as": "albums.songs",
  }},
  { "$group": {
    "_id": "$_id",
    "name": { "$first": "$name" },
    "started_in": { "$first": "$started_in" },
    "active": { "$first": "$active" },
    "country": { "$first": "$country" },
    "albums": {
      "$push": {
        "_id": "$albums._id",
        "title": "$albums.title",
        "released": "$albums.released",
        "type": "$albums.type",
        "songs": "$albums.songs"
      }
    }
  }}
])

edited Jun 14, 2018 at 17:00

answered May 29, 2018 at 12:49

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

4 Comments

vicusbass Over a year ago

I have Mongo 3.6 and the first query works great! Is it possible to do in the same query other fields as well? For example current_members which points to Members collection

Ashh Over a year ago

@vicusbass you can define more variable in let expression just like albums and songs

Ashh Over a year ago

@vicusbass I think you need to go through one more $lookup stage for more population

Vinoth Over a year ago

Hi Ashish, can you solve this question stackoverflow.com/questions/50586276/…

Collectives™ on Stack Overflow

$lookup nested array in mongodb

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related