MONGODB - cast type of every object in array of objects

Question

I have MongoDB Collection where some documents have arrays of objects. One of the fields of this objects is timestamp.

The problem is that historically some of timestamp values are Strings (e.g. '2018-02-25T13:33:56.675000') or Date and some of them are Double (e.g. 1528108521726.26).

I have to convert all of them to Double.

I've built the query to get all the documents with the problematic type:

db.getCollection('Cases').find({sent_messages: {$elemMatch:{timestamp: {$type:[2, 9]}}}})

And I also know how to convert Date-string to double using JS:

new Date("2018-02-18T06:39:20.797Z").getTime()
> 1518935960797

But I can't build the proper query to perform the update.

Here is an example of such a document:

{
    "_id" : ObjectId("6c88f656532aab00050dc023"),
    "created_at" : ISODate("2018-05-18T03:43:18.986Z"),
    "updated_at" : ISODate("2018-05-18T06:39:20.798Z"),
    "sent_messages" : [ 
        {
            "timestamp" : ISODate("2018-02-18T06:39:20.797Z"),
            "text" : "Hey",
            "sender" : "me"
        }
    ],
    "status" : 1
}

After the update it should be:

{
    "_id" : ObjectId("6c88f656532aab00050dc023"),
    "created_at" : ISODate("2018-05-18T03:43:18.986Z"),
    "updated_at" : ISODate("2018-05-18T06:39:20.798Z"),
    "sent_messages" : [ 
        {
            "timestamp" : 1518935960797.00,
            "text" : "Hey",
            "sender" : "me"
        }
    ],
    "status" : 1
}

You cannot do it in a single query. Iterate all matching documents and bulk.update them from application. — Alex Blex, Jul 17 '18 at 11:52
@AlexBlex It is possible to update in a single query with a `foreach`. Perhaps @Alexander, you can take inspiration from this answer here to find a solution for your use case: https://stackoverflow.com/a/14423151/4473762 — airudah, Jul 17 '18 at 12:07
Dear @RobertUdah, mongo shell is an interactive application. `foreach` is a javascript function available there. It iterates over the cursor clientside and issues 1 update request per document in the example you referred to. It is not a single request, even tho you can write it in a single line. — Alex Blex, Jul 17 '18 at 12:16
Dear @AlexBlex, Okay so the example I provided uses a javascript `foreach` but my point still stands. You can perform the operation with a single query by using `updateMany` or the `aggregate` function — airudah, Jul 17 '18 at 13:37
@RobertUdah, sorry mate, no cigar again. `aggregate` is read-only and `update` requires constant values to set, see https://jira.mongodb.org/browse/SERVER-11345. Really appreciate your enthusiasm tho. Beer on me if you prove me wrong. — Alex Blex, Jul 17 '18 at 14:01
@AlexBlex I'll look into this in more detail tomorrow as i'm pretty sure is possible with the `$out` operand in the aggregation function: https://docs.mongodb.com/manual/reference/operator/aggregation/out/ — airudah, Jul 17 '18 at 16:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/176172/discussion-between-alex-blex-and-robert-udah). — Alex Blex, Jul 17 '18 at 17:04

Hardik Shah · Answer 1 · 2018-07-17T12:29:29.037

0

As per your question, you are trying to fetch the record first.

db.getCollection('Cases').find({sent_messages: {$elemMatch:{timestamp: {$type:[2, 9]}}}})

Then convert date in JS:

new Date("2018-02-18T06:39:20.797Z").getTime()

And then this is an update query:

db.getCollection('Cases').updateOne({_id:ObjectId("6c88f656532aab00050dc023")}, { $set: { "sent_messages.$.timestamp" : "218392712937.0" }})

And if you want to update all records then you should write some forEach mechanism. I think you have already this implemented.

Hope this may help you.

edited Jul 17 '18 at 12:29

answered Jul 17 '18 at 12:17

Hardik Shah

4,042
2
20
41

What if I have thousands of documents to fix? I can't do these steps manually. – Alexander Jul 17 '18 at 12:29
Yes, then must we need to use `forEach` as you mentioned in your answer. This helps me too as well. :) – Hardik Shah Jul 17 '18 at 12:34

score 0 · Answer 2 · answered Jul 17 '18 at 12:31

Finally I just do it with JS code that can be run in mongo console:

db.getCollection('Cases').find({sent_messages: {$elemMatch:{timestamp: {$type:[2, 9]}}}}).forEach(function(doc) {
  print('=================');
  print(JSON.stringify(doc));
  doc.sent_messages.forEach(function(msg){
    var dbl = new Date(msg.timestamp).getTime();
    print(dbl);
    msg.timestamp = dbl;
      });
   print(JSON.stringify(doc))
   db.Cases.save(doc);
} )

Thanks all for your help!

MONGODB - cast type of every object in array of objects

2 Answers2