MongoDBAggregate-僅在第二個集合上匹配條件，但提供第一個集合中的所有檔案

基本上，我的MongoDB資料庫中有 2 個集合 -> 書籍、分數。

圖書

{
    "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子查詢檔案，（https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/#join-conditions-and-subqueries-on-a-joined-collection）我得到了感覺我所期待的可能是不可能的。誰能幫我執行這樣的查詢？（我使用 PyMongo 順便說一句）

uj5u.com熱心網友回復：

對于最后兩個階段：

1. $replaceRootSCORES- 通過將當前檔案與作為陣列中第一個檔案的檔案合并，將輸入檔案替換為新檔案。

2. $unset- 洗掉SCORES陣列。

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

uj5u.com熱心網友回復：

您可以通過使用條件來實作這一點$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"
        ]
      },
      
    }
  }
])

蒙戈游樂場

標籤：django mongodb mongodb查询 聚合框架 pymongo

上一篇：如何將AAD組分配給企業應用程式？

下一篇：在MongoDB查詢中查找完全匹配，其中搜索條件應用于串列中的物件級別