Mongo Aggregate - Match condition only on second collection but provide all documents from the first-CodePudding

Basically, I have 2 collections in my Mongo DB -> 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)

CodePudding user response：

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 the SCORES array.
$unset - Remove SCORES array.

db.Books.aggregate([
  {
    "$lookup": {
      "from": "Scores",
      "pipeline": [
        {
          "$match": {
            "BOOK_CATEGORY": "Kids",
            "BOOK_SCORE": "6",
            
          }
        }
      ],
      "localField": "BOOK_ID",
      "foreignField": "BOOK_ID",
      "as": "SCORES"
    }
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            $first: "$$ROOT.SCORES"
          }
        ]
      }
    }
  },
  {
    $unset: "SCORES"
  }
])

Sample Mongo Playground

CodePudding user response：

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:

db.Books.aggregate([
  {
    "$lookup": {
      "from": "Scores",
      "pipeline": [
        {
          "$match": {
            "BOOK_CATEGORY": "kids",
            "BOOK_SCORE": "6"
          }
        }
      ],
      "localField": "BOOK_ID",
      "foreignField": "BOOK_ID",
      "as": "SCORES"
    }
  },
  {
    $addFields: {
      SCORES: "$$REMOVE",
      "BOOK_SCORE": {
        $cond: [
          {
            "$ifNull": [
              {
                "$arrayElemAt": [
                  "$SCORES",
                  0
                ]
              },
              false
            ]
          },
          {
            $getField: {
              field: "BOOK_SCORE",
              input: {
                "$arrayElemAt": [
                  "$SCORES",
                  0
                ]
              }
            }
          },
          "$$REMOVE"
        ]
      },
      "BOOK_CATEGORY": {
        $cond: [
          {
            "$ifNull": [
              {
                "$arrayElemAt": [
                  "$SCORES",
                  0
                ]
              },
              false
            ]
          },
          {
            $getField: {
              field: "BOOK_CATEGORY",
              input: {
                "$arrayElemAt": [
                  "$SCORES",
                  0
                ]
              }
            }
          },
          "$$REMOVE"
        ]
      },
      
    }
  }
])

Mongo Playground