CODEWITHSUNDEEP
mongodbaggregation-framework
Turzo
Turzo

Reputation: 600

MongoDB use array field's element to $set a new field of the document

In the database, I have documents like the following

Ticket {
    "eventHistory": [
        {
            "event": "CREATED",
            "timestamp": "aa-bb-cccc"
        },
        {
            "event": "ASSIGNED",
            "timestamp": "ii-jj-kkkk"
        },
        ...
        {
            "event": "CLOSED",
            "timestamp": "xx-yy-zzzz"
        }
    ]
}

I would like to add a closedAt field to the relevant Tickets, getting the value from the eventHistory array's last element. The resultant document would look like the following

Ticket {
    "eventHistory": [
        {
            "event": "CREATED",
            "timestamp": "aa-bb-cccc"
        },
        {
            "event": "ASSIGNED",
            "timestamp": "ii-jj-kkkk"
        },
        ...
        {
            "event": "CLOSED",
            "timestamp": "xx-yy-zzzz"
        }
    ],
    "closedAt": "xx-yy-zzzz"
}

The following pipeline allows me to use the entire object that's present as the eventHistory array's last element.

db.collection.updateMany(
<query>,
[
    "$set": {
        "closedAt": {
            "$arrayElemAt": [
                "$eventHistory",
                -1
            ]
        }
    }
]
...
)

But I want to use only the timestamp field; not the entire object.

Please help me adjust (and/or improve) the pipeline.

Upvotes: 0

Views: 34

Answers (1)

nimrod serok
nimrod serok

Reputation: 16033

One option to fix your query is:

db.collection.updateMany(
<query>,
[
  {
    $set: {
      "Ticket.closedAt": {
        $last: "$Ticket.eventHistory.timestamp"
      }
    }
  }
])

See how it works on the playground example

But note that you assume that last item is a closing one. Is this necessarily the case? Otherwise you can validate it.

Upvotes: 1

Related Questions