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.