Mongodb聚合查找連接兩個物件欄位的集合陣列匹配物件索引欄位的總和

我有兩個集合“資料集”和“用戶”。我試圖查找兩個集合的物件陣列。

我想加入“datasets.stateHistory.date”欄位和“users.prices.date”欄位。獲取資料集集合的結果我想要“users.prices.price”總和值的總和

資料集 json 資料：

"datasets": [
    {
      "colorDescription": "braun, rose gold",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493 00:00"
        }
      ]
    },
    {
      "colorDescription": "beige, silber",
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493 00:00"
        }
      ]
    }
  ]

用戶 json 資料：

"users": [
    {
      "name": "Aravinth",
      "prices": [
        {
          "date": "2022-02-16T13:06:13.493 00:00",
          "price": 45
        },
        {
          "date": "2022-03-22T13:06:13.493 00:00",
          "price": 55
        }
      ]
    },
    {
      "name": "Raja",
      "prices": [
        {
          "date": "2022-02-24T13:06:13.493 00:00",
          "price": 75
        },
        {
          "date": "2022-03-23T13:06:13.493 00:00",
          "price": 85
        }
      ]
    }
  ]

預期結果 json 資料：

[
    {
      "colorDescription": "braun, rose gold",
      "cgPrices: 45,
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-02-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-02-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-02-16T13:06:13.493 00:00"
        }
      ]
    },
    {
      "colorDescription": "beige, silber",
      "cgPrices: 0,
      "stateHistory": [
        {
          "state": "scanning",
          "date": "2022-03-22T13:06:13.493 00:00"
        },
        {
          "state": "scanned",
          "date": "2022-03-18T13:06:13.493 00:00"
        },
        {
          "state": "reconstructing",
          "date": "2022-03-16T13:06:13.493 00:00"
        }
      ]
    }
  ]

“cgPrice”欄位我需要將匹配的價格與添加的兩個集合的日期相加。

我的代碼：

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      "localField": "stateHistory.date",
      "foreignField": "prices.date"
    }
  },
  {
    "$project": {
      color: "$details.colorDescription",
      prices: "$details"
    }
  }
])

如何加入查找并獲取匹配欄位的價格添加附加欄位“cgPrice”計數總和。

mongo游樂場鏈接：https ://mongoplayground.net/p/vv8R3DlEDYo

uj5u.com熱心網友回復：

您只需要進行大量重組，這里是一個使用$map,$filter和$reduce運算子的示例：

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      "localField": "stateHistory.date",
      "foreignField": "prices.date"
    }
  },
  {
    "$project": {
      colorDescription: 1,
      stateHistory: 1,
      prices: {
        $sum: {
          $map: {
            input: {
              $filter: {
                input: {
                  $reduce: {
                    input: {
                      $map: {
                        input: "$details",
                        in: "$$this.prices"
                      }
                    },
                    initialValue: [],
                    in: {
                      "$concatArrays": [
                        "$$this",
                        "$$value"
                      ]
                    }
                  }
                },
                cond: {
                  $in: [
                    "$$this.date",
                    "$stateHistory.date"
                  ]
                }
              }
            },
            in: "$$this.price"
          }
        }
      }
    }
  }
])

蒙戈游樂場

標籤：mongodb 猫鼬 mongodb查询 聚合框架

上一篇：無法在mongodb中創建嵌套模式

下一篇：聚合以過濾MongoDB中的參考