I have a data model like this picture below and all of them are in the same collection, but of course are different in data structure. I used the manual references in document movies and document studios. For example, like the code below:
For document people
{ "_id": 100600, "first_name": "Becka", "last_name": "Battson", "birth_day": "2001-10-03" }
For document movies
{ "_id": 1100, "title": "Tom and Jerry", "director_id": 100100, "release_year": 2018, "imdb": { "rating": 4.8, "votes": 100 }, "actors": [ { "person_id": 100300, "as": "Tom" }, { "person_id": 100400, "as": "Jerry" }, { "person_id": 100500, "as": "Nibbles" } ] }
For document studios:
{ "_id": 9991000, "name": "Walt Disney", "year_founded": 1923, "movies": [ 1100, 1200 ], "headquarters": { "address": "1375 E Buena Vista Dr", "city": "New York", "state": "New York", "country": "US" } }
I have some tasks that need join between 2 documents together to get data. For example, "Indicates that the movies have the actor has the last name "Battson" . But I know that MongoDB doesn't support join documents like joining 2 tables in RDBMS.
I have tried to used this code below in MongoDB shell but it doesn't make sense. I think I need to get the values from the _id in the result query, then push them into the array array_idActor so that the second code can run:
const array_idActor = db.getCollection('movies_subject').find({last_name: /Battson/})
db.getCollection('movies_subject').find({actors:{$elemMatch:{person_id:{$in:array_idActor}}}}).pretty()
Please help me to fix this. Thank you so much.
join two collections
db.people.aggregate([
{
"$match": {
"last_name": "Battson"
}
},
{
"$lookup": {
"from": "movies",
"localField": "_id",
"foreignField": "actors.person_id",
"as": "movie_docs"
}
}
])
join three collections
db.people.aggregate([
{
"$match": {
"last_name": "Battson"
}
},
{
"$lookup": {
"from": "movies",
"let": {
"people_id": "$_id"
},
"pipeline": [
{
"$match": {
$expr: {
"$in": [
"$$people_id",
"$actors.person_id"
]
}
}
},
{
"$lookup": {
"from": "studios",
"localField": "_id",
"foreignField": "movies",
"as": "studio_docs"
}
}
],
"as": "movie_docs"
}
}
])
same collection
db.collection.aggregate([
{
"$match": {
"last_name": "Battson"
}
},
{
"$lookup": {
"from": "collection",
"localField": "_id",
"foreignField": "actors.person_id",
"as": "movie_docs"
}
}
])