0

I'm trying to use the aggregate function for getting a particular value from the referenced collection. But it's only selecting the records when the main collection is having the field that's used for reference acting like JOIN in RDBMS as like follows,

movies collection structure:

{
    "_id" : ObjectId("5cd9624c57fda41d20f925a4"),
    "name" : "Game over"
}
{
    "_id" : ObjectId("5cf3808e096bc30017e1ffae"),
    "name" : "Kolaigaran"
}
{
    "_id" : ObjectId("5cf229902b18930dba0488ee"),
    "name" : "Devi 2"
}

events collection structure:

{
    "_id" : ObjectId("5d0a1c9c568edb055ff850bd"),
    "movieId" : ObjectId("5cd9624c57fda41d20f925a4"),
    "resources" : {
        "posterLinks" : [ 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/e8brnxqxb7e6djvpvnrd.jpg", 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/vgsfwnwhykaqhov5zg2b.jpg"
        ],
        "youtubeLinks" : [ 
            "https://www.youtube.com/watch?v=ocnzgwjXdLw", 
            "https://www.youtube.com/watch?v=oLRHNYQ-Dno"
        ]
    },
}
{
    "_id" : ObjectId("5d0a201b568edb055ff850c0"),
    "movieId" : ObjectId("5cd9624c57fda41d20f925a4"),
    "resources" : {
        "posterLinks" : [ 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/qivg8ft6riul8rpfwygy.jpg"
        ]
    },
}
{
    "_id" : ObjectId("5d0a3a50568edb055ff850c1"),
    "movieId" : ObjectId("5cf3808e096bc30017e1ffae"),
    "resources" : {
        "posterLinks" : [ 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/egefgqzddu0jgj0wyagh.jpg", 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/qlnxslo7zobtm3tiqgor.jpg"
        ]
    },
}

Here's the query I'm using,

db.getCollection('movies').aggregate([
    {
       $lookup:
         {
            from: "events",
            let: { movieId: "$_id" },
            pipeline: [ 
                { $match: { $expr: { $eq: ["$movieId", "$$movieId"] } } },
                { $project: { resources: { $objectToArray: "$resources" } } },
                { $unwind: "$resources" },
                { $unwind: "$resources.v" },
                { $group: { _id:"$resources.k", "v": { $addToSet:"$resources.v" } } },
                { $group: { _id:null, resources: { $push: { k:"$_id", v:"$v" } } } },
                { $project: { resources: { $arrayToObject: "$resources" } } },
                { $replaceRoot: { newRoot:"$resources" } }
            ],
            as: "resources"
         }
    },
    { $unwind: "$resources" }
]);

This returns all the documents in the movies collection where it has a relation in the events collection as follows,

{
    "_id" : ObjectId("5cd9624c57fda41d20f925a4"),
    "name" : "Game over",
    "resources" : {
        "youtubeLinks" : [ 
            "https://www.youtube.com/watch?v=oLRHNYQ-Dno", 
            "https://www.youtube.com/watch?v=ocnzgwjXdLw"
        ],
        "posterLinks" : [ 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/qivg8ft6riul8rpfwygy.jpg", 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/vgsfwnwhykaqhov5zg2b.jpg", 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/e8brnxqxb7e6djvpvnrd.jpg"
        ]
    }
}
{
    "_id" : ObjectId("5cf3808e096bc30017e1ffae"),
    "name" : "Kolaigaran",
    "resources" : {
        "posterLinks" : [ 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/qlnxslo7zobtm3tiqgor.jpg", 
            "https://res.cloudinary.com/beinspired/image/upload/run-up/posters/egefgqzddu0jgj0wyagh.jpg"
        ]
    }
}

As you can see, it doesn't have the third document in the movies as it doesn't have any relation in the events collection.

Here is what I've tried by modifying $match part in the query like follows but not working,

{ $match: { $or: [ { $expr: { $eq: ["$movieId", "$$movieId"] } }, { resources: { $exists: false } } ] } }

I'm not sure if $match is the exact part that should be changed to include the other results.

Any help on this would be greatly useful and much appreciated. Thanks in advance.

Stranger
  • 10,332
  • 18
  • 78
  • 115
  • 1
    I think you could use the regular way of joining `{ $lookup: { from: '', foreignField: '', localField: '', as: '$data' } }` then you can make the `$project` to `$data` like you make in let this will en light things – Shorbagy Jun 20 '19 at 02:01
  • @AlexBlex Thanks, I got it – Stranger Jun 20 '19 at 09:39

0 Answers0