MongoDB update document field based on other documents' field-CodePudding

These are the documents in the collection.

[
  {
    "_id": ObjectId("62f45101c6b6654eafcf8859"),
    "parentId": null,
    "status": 1
  },
  {
    "_id": ObjectId("62f45e6c74f4f1d1cf4bc73a"),
    "parentId": ObjectId("62f45101c6b6654eafcf8859"),
    "status": 4
  },
  {
    "_id": ObjectId("62f45e6c74f4f1d1cf4bc73b"),
    "parentId": ObjectId("62f45101c6b6654eafcf8859"),
    "status": 3
  }
]

I want to update the status of the first document (which is the parent of the other two documents) with the minimum value of its children's status field i.e. min(4, 3) = 3.

I have tried using aggregate pipeline and this is what I have achieved so far.

db.collName.aggregate([
    {
        $match: {
            parentId: ObjectId("62f45101c6b6654eafcf8859")
        }
    },
    {
        $group: {
            _id: ObjectId("62f45101c6b6654eafcf8859"),
            status: {
                $min: "$status"
            }
        }
    }
])

This returns me the following.

{ "_id" : ObjectId("62f45101c6b6654eafcf8859"), "status" : 3 }

I am not sure how to update the parent i.e. the first document's status field with this result.

CodePudding user response：

You should perform a join into the same collection using $lookup, then calculate the status, value for parent documents, and then update the collection, using $merge.

db.collection.aggregate([
  {
    $lookup: {
      from: "collection",
      localField: "_id",
      foreignField: "parentId",
      as: "children"
    }
  },
  {
    "$project": {
      status: {
        "$cond": {
          "if": {
            "$eq": [
              "$parentId",
              null
            ]
          },
          "then": {
            "$min": "$children.status"
          },
          "else": "$status"
        }
      },
      parentId: 1
    }
  },
  {
    "$merge": {
      "into": "collection",
      "on": "_id",
      "whenMatched": "replace",
      
    }
  }
])

Here's the playground link.