时间戳

首页 > 解决方案 > 如何在嵌套的对象数组中查找 $lookup

mongodb - 如何在嵌套的对象数组中查找 $lookup

问题描述

我必须有两个集合，一个是旅游，另一个是目的地，所以在旅游中我有一个位置数组，其中有一个带有 id 的目标对象，并且该 id 属于另一个目的地集合，但问题是我无法查找详细信息位置数组中的目的地。每一个在这里也尝试了很多查询搜索。但没有得到预期的结果。

旅游：

{
    "_id" : ObjectId("5f3122f4d8d57e3b9650e5b4"),
    "title" : "tour 1",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae9037ea99f20a79071a"
            },
            "services" : {
                "hotel" : true
            }
        }, 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8c"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}
{
    "_id" : ObjectId("5f2d65e68bc6e9155310d147"),
    "title" : "tour 2",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ecf994435c3a6025d5bf126"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}
{
    "_id" : ObjectId("5f2d66398bc6e9155310d161"),
    "title" : "tour 3",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8d"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}

目的地：

{
    "_id" : ObjectId("5ec5ae9037ea99f20a79071a"),
    "name" : "dest 1",
    "country" : "country name"
}
{
    "_id" : ObjectId("5ec5ae8e37ea99f20a78ef8c"),
    "name" : "dest 2",
    "country" : "country name"
}
{
    "_id" : ObjectId("5ec5ae8e37ea99f20a78ef8d"),
    "name" : "dest 3",
    "country" : "country name"
}
{
    "_id" : ObjectId("5ecf994435c3a6025d5bf126"),
    "name" : "dest 4",
    "country" : "country name"
}

预期结果 ：

{
    "_id" : ObjectId("5f3122f4d8d57e3b9650e5b4"),
    "title" : "tour 1",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae9037ea99f20a79071a",
                "name" : "dest 1",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }, 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8c",
                "name" : "dest 2",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
},
{
    "_id" : ObjectId("5f2d65e68bc6e9155310d147"),
    "title" : "tour 2",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ecf994435c3a6025d5bf126",
                "name" : "dest 4",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
},
{
    "_id" : ObjectId("5f2d66398bc6e9155310d161"),
    "title" : "tour 3",
    "locations" : [ 
        {
            "destination" : {
                "id" : "5ec5ae8e37ea99f20a78ef8d",
                "name" : "dest 3",
                "country" : "country name"
            },
            "services" : {
                "hotel" : true
            }
        }
    ]
}

尝试查询：

db.tours.aggregate([
  { 
    "$addFields": {
      "locations": {
        "$map": {
          "input": "$locations",
          "in": {
            "$mergeObjects": [
              "$$this",
              {
                "dest_oid": {
                  "$toObjectId": "$$this.destination.id"
                }
              }
            ]
          }
        }
      }
    }
  },
  { "$unwind": "$locations" },

  { "$lookup": {
    "from": "destinations",
    "localField": "locations.dest_oid",
    "foreignField": "_id",
    "as": "locations.dest",
  }},
  { "$unwind": "$locations.dest" },
  { "$group": {
    "_id": "$_id",
    "locations": { "$push": "$locations" }
  }}
])

即使我 在嵌套文档上尝试过这个 MongoDB $lookup

标签： mongodbmongoosemongodb-query

解决方案

快速修复，

  • $unwind locations数组放在第一位，因为需要将 id 转换为对象 id
db.tours.aggregate([
  { $unwind: "$locations" },
  • 如果您已经转换了字符串 id t 对象 id，则跳过这部分
  • $addFields替换locations.destination.id为对象 id 将您的逻辑过滤为简短，这里不需要 $map 和 $mergeObjects 选项
  {
    $addFields: {
      "locations.destination.id": {
        $toObjectId: "$locations.destination.id"
      }
    }
  },
  • $lookup你已经做了，但改变为locations.destination
  {
    $lookup: {
      from: "destinations",
      as: "locations.destination",
      localField: "locations.destination.id",
      foreignField: "_id"
    }
  },
  • $unwindlocations.destination因为它的数组，我们需要对象
  {
    $unwind: {
      path: "$locations.destination"
    }
  },
  • $group您已经做过的，少量更改，在位置推送第一个目的地和服务并添加第一个标题
  {
    $group: {
      _id: "$_id",
      locations: { $push: "$locations" },
      title: { $first: "$title" }
    }
  }
])

游乐场：https ://mongoplayground.net/p/yaTCij7NRUj

推荐阅读