MongoDB-查找與字串物件的條件陣列匹配

我有兩個集合“資料集”和“用戶”。

我試圖查找datasets.assignedTo = users.id作業正常。另外，我想將匹配datasets.firstBillable >= users.prices.beginDate日期欄位的欄位進行匹配以獲取當前的索引價格值。并且還檢查users.prices.endDate小于或等于users.prices.beginDate。

例如：

cg價格：45

https://mongoplayground.net/p/YQps9EozlAL

收藏：

db={
  users: [
    {
      id: 1,
      name: "Aravinth",
      prices: [
        {
          beginDate: "2022-08-24T07:29:01.639Z",
          endDate: "2022-08-31T07:29:01.639Z",
          price: 45
        }
      ]
    },
    {
      id: 2,
      name: "Raja",
      prices: [
        {
          beginDate: "2022-07-25T07:29:01.639Z",
          endDate: "2022-07-30T07:29:01.639Z",
          price: 55
        }
      ]
    }
  ],
  datasets: [
    {
      color: "braun, rose gold",
      firstBillable: "2022-08-24T07:29:01.639Z",
      assignedTo: 1
    },
    {
      color: "beige, silber",
      firstBillable: "2022-07-25T07:29:01.639Z",
      assignedTo: 2
    }
  ]
}

我目前的實作：

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      let: {
        assigned_to: "$assignedTo",
        first_billable: "$firstBillable"
      },
      pipeline: [
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$eq": [
                    "$id",
                    "$$assigned_to"
                  ]
                },
                {
                  "$gte": [
                    "$first_billable",
                    "$details.prices.beginDate"
                  ]
                },
                {
                  "$lte": [
                    "$first_billable",
                    "$details.prices.endDate"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "details": 0,
      "cg": {
        $first: {
          "$first": "$details.prices.price"
        }
      }
    }
  }
])

我需要的輸出：

[
  {
    "_id": ObjectId("5a934e000102030405000000"),
    "assignedTo": 1,
    "cg": 45,
    "color": "braun, rose gold",
    "details": 0,
    "firstBillable": "2022-08-24T07:29:01.639Z"
  },
  {
    "_id": ObjectId("5a934e000102030405000001"),
    "assignedTo": 2,
    "cg": 55,
    "color": "beige, silber",
    "details": 0,
    "firstBillable": "2022-07-25T07:29:01.639Z"
  }
] 

https://mongoplayground.net/p/YQps9EozlAL

uj5u.com熱心網友回復：

關注點：

1. 您應該比較日期Date而不是字串，因此您需要Date在比較之前將日期字串轉換為。

2. 在users集合中，prices是一個陣列。您需要先將陣列解構為多個檔案，然后再比較price.

查詢應該是：

db.datasets.aggregate([
  {
    "$lookup": {
      "from": "users",
      "as": "details",
      let: {
        assigned_to: "$assignedTo",
        first_billable: {
          $toDate: "$firstBillable"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$id",
                "$$assigned_to"
              ]
            }
          }
        },
        {
          $unwind: "$prices"
        },
        {
          "$match": {
            $expr: {
              "$and": [
                {
                  "$gte": [
                    "$$first_billable",
                    {
                      $toDate: "$prices.beginDate"
                    }
                  ]
                },
                {
                  "$lte": [
                    "$$first_billable",
                    {
                      $toDate: "$prices.endDate"
                    }
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    "$addFields": {
      "details": 0,
      "cg": {
        $first: "$details.prices.price"
      }
    }
  }
])

演示@Mongo Playground

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

上一篇：MongoDB將一個檔案的資料添加到陣列中，并與另一檔案上的一個欄位值進行比較

下一篇：NodeJSmongoose在開頭插入資料