Conversion of ObjectIds in Subarray to Strings

Working with Data
1

Hi MongoDB-Team,

I am desperately searching for an easy solution to convert Elements of type ObjectId to String.

Since following command works:

db.g.update({ "ug.Id_ref": { $type: 7 } },
 [{ $set: { "ug.Id_ref": { $toString: "ug.Id_ref" } } }],
 {multi:true})`

I thought of something like this

    db.g.update({ "ug.Id_ref": { $type: 7 } },
 [{ $set: { "ug.Id_ref": { $toString: "$ug.$Id_ref" } } }], // Ok, this want never work
 {multi:true})

but no chance. I cant believe that is so difficult to execute such an simple type-conversion in MongoDB.
I looked at some forum entries on the topic (like 117177 or 110582
) and couldn’t find a correct solution on it, why? I do not get it.
Such a great part of software and so much secrecy about a touch of nothing.
It would be nice if you could provide me with a simple example.

// collection: g
{
...
	"ug" : [
		{
			"_id" : ObjectId("6166fe35d46756c8344c56d4"),
			"Id_ref" : ObjectId("6166f7b1d46756c8344c3f62"),
			"DateCreated" : ISODate("2021-10-13T17:14:14.413+02:00")
		},
		{
			"_id" : ObjectId("61671ef0d46756c8344cf18b"),
			"Id_ref" : ObjectId("616701d0d46756c8344c828e"),
			"DateCreated" : ISODate("2021-10-13T17:57:04.699+02:00")
		},
		{
			"_id" : ObjectId("61671ef0d46756c8344cf18c"),
			"Id_ref" : ObjectId("616702cbd46756c8344c8e00"),
			"DateCreated" : ISODate("2021-10-13T18:01:15.631+02:00")
		},
		
		
// after update: g
{
...
	"ug" : [
		{
			"_id" : ObjectId("6166fe35d46756c8344c56d4"),
			"Id_ref" : "6166f7b1d46756c8344c3f62",
			"DateCreated" : ISODate("2021-10-13T17:14:14.413+02:00")
		},
		{
			"_id" : ObjectId("61671ef0d46756c8344cf18b"),
			"Id_ref" : "616701d0d46756c8344c828e",
			"DateCreated" : ISODate("2021-10-13T17:57:04.699+02:00")
		},
		{
			"_id" : ObjectId("61671ef0d46756c8344cf18c"),
			"Id_ref" : "616702cbd46756c8344c8e00",
			"DateCreated" : ISODate("2021-10-13T18:01:15.631+02:00")
		},
2

I suspect that the issue is related to your 2nd $ in $ug.$Id_ref.

If you could provide your sample documents enclosed in 2 lines of triple back ticks we would be able to test.

3

If it ever works, I would think twice before doing this conversion.

  1. The string representation of an ObjectId takes more space
  2. You might need to do the reverse conversion every time you $lookup
  3. The conversion of point 2 above might make it impossible to use the index of the looked up collection with localField and foreignField since you will need to use $expr to make the match
1 Like
4

Sorry, unfortunately I didnt find to edit my entry after commit.
I hope this helps:

db.g.update({ 
    "ug.Id_ref": { $type: 7 } },
    [        {             $set:   { "ug.Id_ref":   { $toString: "$ug.Id_ref" }  } 
        }], {multi:true}) 

// collection: ug
{
	"_id" : ObjectId("61683b501b05ea31c0c1a391"),
	"ug" : [
		{
			"_id" : ObjectId("6166fe35d46756c8344c56d4"),
			"Id_ref" : ObjectId("6166f7b1d46756c8344c3f62"),
			"DateCreated" : ISODate("2021-10-13T17:14:14.413+02:00")
		},
		{
			"_id" : ObjectId("61671ef0d46756c8344cf18b"),
			"Id_ref" : ObjectId("616701d0d46756c8344c828e"),
			"DateCreated" : ISODate("2021-10-13T17:57:04.699+02:00")
		},
		{
			"_id" : ObjectId("61671ef0d46756c8344cf18c"),
			"Id_ref" : ObjectId("616702cbd46756c8344c8e00"),
			"DateCreated" : ISODate("2021-10-13T18:01:15.631+02:00")
		}
	]
}
5

Thanks for your fast response.

1 + 2. Thanks for these important pointers, unfortunately, it is my team’s decision to change ObjectId to String type, so I’m going to put pressure on to ditch this smelly thing.
3. What if we need to change the type of a date field or something else in a subarray of an existing MongoDB. Is there a simple solution with array filter or elemMatch?

And again, thank you very much for this great support! :+1: :+1: :+1:

6

See Formatting code and log snippets in posts. It shows how to publish code and data.

Also look at Bulk Edit Document Data from String to Float - #3 by steevej to see why I recommend to do bulk conversion into a temporary collection rather than in situ.

Converting date to string is also a bad idea. The space consideration is the same. Comparing date as string is also slower than date as date. Comparing 2 dates to be the same is a few hardware low-level comparisons. Doing it with string might require up to 10 character per character comparisons when dates are within the same year and month such as 2021-10-13 and 2021-10-14.

7

Ok, thanks, very useful hints.

I would have liked to have corrected and formatted the created posts later, but that obviously doesn’t work. All that remains is to delete them, but I leave that decision to you.

8

I prefer that all the posts of a thread remain. A reply without the context of the original post is often useless and might be misleading or impossible to understand. If you still want us to investigate just make sure you publish your sample documents within 2 lines of triple back ticks like:

```
// put your documents here and they will usable by all of us
{
  "ug" : [
    {
       "_id" : ObjectId( "6166fe35d46756c8344c56d4" )
    }
  ]
}
```
9

Take a look at this response to another thread from @turivishal.

I have not try it with your situation but I am pretty sure something based on the same logic can be used.

See How to rename a field name inside nested array? - #2 by turivishal