MongoDB汇总 - 仅在第二个集合上进行匹配条件，但提供了第一个文档

Question

基本上，我的 mongodb 数据库中有2个集合 - ＆gt;书籍，得分。

书籍

{
    "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",
}

---

分数

{
    "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",
}

---

预期输出： 使用books_category =“ kids”和`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",
}

，请注意，对于所有可用分数的书籍，它们都会附加。如果一本书与任何分数没有任何相关性，那么结果仍然存在。

我尝试过的内容？

我尝试使用$ 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)

，还通过阅读$ lookup subquery docs，（ https> https：https：// www。 mongodb.com/docs/manual/reference/operator/aggregation/lookup/#join-conditions-and-subqueries-on-a-joined-collection ） 我感觉到我期望的可能是不可能的。 谁能帮助我执行此类查询？ （我使用pymongo btw）

Answer 1

对于最后两个阶段：

1. $替换 - 通过将当前文档与该文档合并为分数数组。

2. $ unset - 删除得分 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"
  }
])

示例mongo playground

Answer 2

您可以使用条件$ addfields，如果存在$ lookup值，则可以填充值，否则使用$$ remove像这样的领域：

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