Basically, I have 2 collections in my MongoDB database -> Books, Scores.
Books
{
"BOOK_ID" : "100",
"BOOK_NAME" : "Book 1",
"BOOK_DESC" : "abcd",
},
{
"BOOK_ID" : "101",
"BOOK_NAME" : "Book 2",
"BOOK_DESC" : "efgh",
},
{
"BOOK_ID" : "102",
"BOOK_NAME" : "Book 3",
"BOOK_DESC" : "ijkl",
}
Scores
{
"BOOK_ID" : "100",
"BOOK_CATEGORY" : "kids",
"BOOK_SCORE" : "6",
},
{
"BOOK_ID" : "100",
"BOOK_CATEGORY" : "Educational",
"BOOK_SCORE" : "8",
},
{
"BOOK_ID" : "101",
"BOOK_CATEGORY" : "Kids",
"BOOK_SCORE" : "6",
},
{
"BOOK_ID" : "101",
"BOOK_CATEGORY" : "Fantasy",
"BOOK_SCORE" : "7",
}
Expected output :
Searching for all books with BOOKS_CATEGORY="Kids"
and `BOOKS_SCORE=6“
{
"BOOK_ID" : "100",
"BOOK_NAME" : "Book 1",
"BOOK_DESC" : "abcd",
"BOOK_CATEGORY" : "Kids",
"BOOK_SCORE" : 6
},
{
"BOOK_ID" : "101",
"BOOK_NAME" : "Book 2",
"BOOK_DESC" : "efgh",
"BOOK_CATEGORY" : "Kids",
"BOOK_SCORE" : 6
},
{
"BOOK_ID" : "102",
"BOOK_NAME" : "Book 3",
"BOOK_DESC" : "ijkl",
}
Notice that, for all the books to which scores are available, they are appended. If a Book does not have any score associated, it still comes in the result.
What I have tried?
I have tried using $lookup
pipeline = [
{
"$lookup": {
"from": "Scores",
"pipeline":[
{
"$match" : {
"BOOK_CATEGORY" : "Kids",
"BOOK_SCORE" : "6",
}
}
],
"localField": "BOOK_ID",
"foreignField": "BOOK_ID",
"as": "SCORES",
},
},
]
db.Books.aggregate(pipeline)
Also, by reading the $lookup
subquery docs,(https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/#join-conditions-and-subqueries-on-a-joined-collection)
I got the feeling that what I am expecting may not be possible.
Can anyone help me with executing such query? (I use PyMongo btw)
2
Answers
For the last two stages:
$replaceRoot
– Replace the input document(s) with the new document(s) by merging the current document with the document which is the first document from theSCORES
array.$unset
– RemoveSCORES
array.Sample Mongo Playground
You can achieve this by using a conditional
$addFields
, if the$lookup
value exists then populate the values, else use$$REMOVE
to remove the field, like so:Mongo Playground