MongoDB $lookup

Question

我有一个连接集合--我想把人们的数据和他们的父母一起拉回来……我怎么才能做到-

人民

[
    {
        "_id" : ObjectId("3a9ccf7de6348936d88b3601"),
        "first_name" : "John",
        "last_name" : "Doe"
    },
    {
        "_id" : ObjectId("3a9ccf7de6348936d88b3602"),
        "first_name" : "Jane",
        "last_name" : "Doe"
    },
    {
        "_id" : ObjectId("3a9ccf7de6348936d88b3603"),
        "first_name" : "Bobby",
        "last_name" : "Doe"
    }
]

关系

[
    {
        "_id" : ObjectId("5aa9a283e40f140014485116"),
        "person_id" : ObjectId("3a9ccf7de6348936d88b3603"),
        "parent_id" : ObjectId("3a9ccf7de6348936d88b3601"),
        "position": "father"
    },
    {
        "_id" : ObjectId("5aa9a283e40f140014485116"),
        "person_id" : ObjectId("3a9ccf7de6348936d88b3603"),
        "parent_id" : ObjectId("3a9ccf7de6348936d88b3602"),
        "position": "mother"
    }
]

我想要这样的东西：

[
     {
        "_id" : ObjectId("3a9ccf7de6348936d88b3603"),
        "first_name" : "Bobby",
        "last_name" : "Doe",
        "relations: : [
            {
                "_id" : ObjectId("3a9ccf7de6348936d88b3602"),
                "first_name" : "Jane",
                "last_name" : "Doe",
                "position": "mother"
            },
            {
                "_id" : ObjectId("3a9ccf7de6348936d88b3601"),
                "first_name" : "John",
                "last_name" : "Doe",
                "position": "father"
            }
        ]
    }
]

我知道我需要aggregate和$lookup。但是我不能通过最基本的

db.getCollection('people')
  .aggregate([
    { $lookup: {
         from: 'relations',
         localField: 'person_id',
         foreignField: '_id',
         as: 'relations'
       }
     }
    ])

Answer 1

您需要运行两次$lookup，第二次应该将people作为"from“值：

db.people.aggregate([
    {
        $lookup: {
            from: "relations",
            localField: "_id",
            foreignField: "person_id",
            as: "relations"
        }
    },
    {
        $lookup: {
            from: "People",
            localField: "relations._id",
            foreignField: "_id",
            as: "relations"
        }
    }
])

Mongo Playground

Answer 2

这里是使用嵌套查找/子管道的另一种方法。也显示了关系的位置。

db.people.aggregate(
[
  {
    $lookup:
        {
            from: 'relations',
            let: { person_id: '$_id' },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$person_id", "$$person_id"] }
                    }
                },
                {
                    $lookup: {
                        from: "people",
                        localField: "parent_id",
                        foreignField: "_id",
                        as: "person"
                    }
                },
                {
                    $replaceWith: {
                        $mergeObjects: [{ $arrayElemAt: ["$person", 0] }, "$$ROOT"]
                    }
                },
                {
                    $project: {
                        _id: "$parent_id",
                        position: 1,
                        first_name: 1,
                        last_name: 1
                    }
                }

            ],
            as: 'relations'
        }
  }
])

https://mongoplayground.net/p/EJB-1WfanuY