Mongo查询更新年份取决于内部文档字段

Question

这是我的数据。我想更改年份，但它应该只对文档数组的第一项有效

    {
        "_id": {
            "$oid": "62053aa8aa1cfbe8c4e72662"
        },
        "school": "Test",
        "reports": [
            {
            "year": "2020",     // This has to be changed to 2019
            "createdAt": {
                "$date": "2022-02-10T17:05:25.682Z"
            },
            "pid": {
                "$oid": "620545d5097761628f32365a"
            },
            "details": {
                "end_date": {
                    "$date": "2020-03-31T00:00:00.000Z"   // when end date is prior to July 01 of the $year mentioned.
                }
            }
        }, {
            "year": "2020",
            "createdAt": {
                "$date": "2022-03-14T19:08:38.125Z"
            },
            "pid": {
                "$oid": "622f92b68a408531d4b784de"
            },
            "details": {
                "end_date": {
                    "$date": "2021-03-31T00:00:00.000Z"
                }
            }
        }
    ]
    }

在上面的数据中，如果details.end_date 早于当前提到的年份的 7 月 1 日，我想将年份减少到上一年。但它应该只更改嵌入数组的第一项。

例如，

If Year is 2020 and details.end_date is prior to 01-July-2020, then change the year to 2019

If Year is 2020 and details.end_date is after 01-July-2020, then do not change the year

If Year is 2021 and details.end_date is prior to 01-July-2021, then change the year to 2020

If Year is 2021 and details.end_date is after 01-July-2021, then do not change the year

Answer 1

您可以在聚合管道中执行以下操作：

1. 使用 $arrayElemAt 隔离 reports 数组的第一个 elem 以便于处理，
2. 使用 $cond 进行派生$month 的年份值。使用 $toInt 和 $toString 进行类型转换
3. ，使用 $concatArrays 将处理后的第一个 Elem 追加回报告数组。使用 $slice $merge 仅保留“尾部”（即没有第一个 elem）
4. 将结果更新回集合

db.collection.aggregate([
  {
    "$addFields": {
      "firstElem": {
        "$arrayElemAt": [
          "$reports",
          0
        ]
      }
    }
  },
  {
    "$addFields": {
      "firstElem.year": {
        "$cond": {
          "if": {
            $lt: [
              {
                "$month": "$firstElem.end_date"
              },
              7
            ]
          },
          "then": {
            "$toString": {
              "$subtract": [
                {
                  "$toInt": "$firstElem.year"
                },
                1
              ]
            }
          },
          "else": "$firstElem.year"
        }
      }
    }
  },
  {
    "$addFields": {
      "reports": {
        "$concatArrays": [
          [
            "$firstElem"
          ],
          {
            "$slice": [
              "$reports",
              1,
              {
                "$subtract": [
                  {
                    "$size": "$reports"
                  },
                  1
                ]
              }
            ]
          }
        ]
      }
    }
  },
  {
    "$project": {
      firstElem: false
    }
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "replace"
    }
  }
])

这是 Mongo Playground 供您参考。