$ lookup mongodb中的嵌套数组

问题描述

我正在与MongoDB中新的(可爱的)lookup运算符作斗争.我有3个收藏集:

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

艺术家

{
    "_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"
    ]
}

相册

{
    "_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")
}

歌曲

{
    "_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")
}

我可以轻松地使用$lookup进行聚合以获取详细的albums数组，但是如何在相应的相册中获取详细的songs呢?我想扩展以下查询:

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()

推荐答案

如果您的mongodb版本为 3.6 ，则可以尝试使用嵌套的 $lookup 聚合...

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"
  }}
 ])

对于冗长的解释，您可以通过 $ lookup多个级别而无需$ unwind?

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

或者如果您的Mongodb版本低于 3.6

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"
      }
    }
  }}
])

这篇关于$ lookup mongodb中的嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！