CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate
j-p
j-p

Reputation: 3818

MongoDB $lookup

I have a join collection - and I want to pull back PEOPLE data with their Parents... How can I do this -

PEOPLE

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

RELATIONS

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

I want something like this:

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

I know I need aggregate and $lookup. but I cant get past the most basic

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

Upvotes: 2

Views: 85

Answers (2)

Dĵ ΝιΓΞΗΛψΚ
Dĵ ΝιΓΞΗΛψΚ

Reputation: 5669

here's another way to do it using a nested lookup/sub-pipeline. also shows the position of relations.

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

Upvotes: 0

mickl
mickl

Reputation: 49945

You need to run $lookup twice and second one should have people as a "from" value:

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

Upvotes: 1

Related Questions