0

First of all, I am new to MongoDB, so this question may be quite simple to answer for some of you. I am trying to create an array or embedded document that includes existing fields from my collection. In order words, let's say I have a collection with the fields "borough", "street" and "zipcode". I would like to create an embedded document named, for example, "Location" and move to that document these three fields. Is this possible? The following is one of the many different ways I tried to achieve this:

db.cooking.aggregate([{$set:{"borough":"$borough", "street":"$street", "zipcode":"$zipcode"}},{$out:"Location"}])

Where I would then use the db.Location.copyTo(cooking) expression to add the data from the newly created aggregate collection "Location" to the main collection "cooking". Of course I would have to remove the existing three fields from the cooking collection since I have the same information in the embedded document Location to avoid having duplicate data.

eurojourney
  • 71
  • 1
  • 9
  • Originally I tried using this expression db.cooking.updateMany({},{$push:{"Area":null, "Address":null, "Zip":null}}) to create the array and then I tried db.cooking.updateMany({},{$rename: {"borough": "Location.Area"}}) to change the names of the fields but had no luck either. – eurojourney Dec 13 '19 at 01:42
  • I posted an answer which overwrites/updates the collection with an embedded document. Hope it is useful. – prasad_ Dec 13 '19 at 04:09

1 Answers1

0

You can create an embedded document with the existing fields, using one of the approaches:

Assume you have a collection with the documents like this:

{ _id: 1, fld1: 12, fld2: "red" },
{ _id: 2, fld1: 9, fld2: "blue" },
{ _id: 3, fld1: 34, fld2: "green" }

and you want to create an embedded document named location with the fields fld1 and fld2; the following aggregation will do that:

db.test.aggregate( [
  { $project: { "location.fld1": "$fld1", "location.fld2": "$fld2" } },
  { $out: "test" }
] )

Note that the original collection test will be overwritten and will be like this:

{ "_id" : 1, "location" : { "fld1" : 12, "fld2" : "red" } }
{ "_id" : 2, "location" : { "fld1" : 9, "fld2" : "blue" } }
{ "_id" : 3, "location" : { "fld1" : 34, "fld2" : "green" } }


The second approach:

This requires that you are using the MongoDB version 4.2. This update query just modifies the existing documents in the same collection (with the same result as above):

db.test.updateMany(
  { },
  [
      { $set: { "location.fld1": "$fld1", "location.fld2": "$fld2" } },
      { $unset: [ "fld1", "fld2" ] }
  ]
)
prasad_
  • 12,755
  • 2
  • 24
  • 36
  • Thank you for your answer, looks logical BUT I get this error message: Failed to execute script. Error: the update operation document must contain atomic operators Details: DBCollection.prototype.updateMany@src/mongo/shell/crud_api.js:625:1 – eurojourney Dec 13 '19 at 09:58
  • The `updateMany` works with MongoDB version 4.2 or higher only (The second approach). – prasad_ Dec 13 '19 at 10:00
  • The aggregation creates another collection, now I am trying to figure out how to merge that collection with my main collection. It seems db.collection.copyTo(newCollection) was deprecated so that's out of the question. I am trying to figure out a way to do it using $push. Ultimately I want to have the embedded documents in my main collection and delete the fields in the main collection that already exist in the embedded document. – eurojourney Dec 13 '19 at 10:53
  • Oh, btw, I have Mondodb 4.2.1 yet the updateMany approach did not work as indicated before. It seems such a better way to go about it than having to merge collections and all that... but it did not work for whatever reason. – eurojourney Dec 13 '19 at 10:55
  • Did you run the query from what client? I tried with Mongo Shell. What about you, what tool did you use? – prasad_ Dec 13 '19 at 11:07
  • _"The aggregation creates another collection,..."_. No it can or it can replace (overwrite) the existing collection; depends upon what name you provide in the `$out` stage. – prasad_ Dec 13 '19 at 11:12
  • Also, if you have _MongoDB Version 4.2_ you can use [$merge](https://docs.mongodb.com/manual/reference/operator/aggregation/merge/index.html) instead of the `$out` in the aggregation. – prasad_ Dec 13 '19 at 11:15
  • My $out collection was a different collection than the main collection. Hence my desire to merge that new collection with the main collection to preserve all the fields and in essence just moving the fields "borough", "street" and "zipcode" into the embedded document "Location". I will look into the $merge command now. I assume that it would be {$merge: "mainCollection"}? Thank you for your replies, very much appreciated!!! – eurojourney Dec 13 '19 at 11:24
  • You can use the `addFields` instead of `$project` in the aggregation to preserve the _old_ fields in the documents. – prasad_ Dec 13 '19 at 11:27
  • Well, unfortunately, it did not work. This is the error message I got: Failed to execute script. Error: command failed: { "ok" : 0, "errmsg" : "$merge is not supported when the output collection is the same as the aggregation collection", "code" : 51188, "codeName" : "Location51188" } : aggregate failed Details: – eurojourney Dec 13 '19 at 11:28
  • It gave me another error, wow, this is quite frustrating and I thought it was going to be simple. This is the error I got: Error: TypeError: db.cooking.addFields is not a function Details: @(shell):1:1 – eurojourney Dec 13 '19 at 11:29
  • Again `$merge` requires MongoDB version 4.2. – prasad_ Dec 13 '19 at 11:33
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/204194/discussion-between-prasad-and-eurojourney). – prasad_ Dec 13 '19 at 11:33
  • Sorry, I take that back... the $addFields did work, I missed something in my code. Now I just need to delete the repeated fields and voila! Thank you, I think I got it now. – eurojourney Dec 13 '19 at 11:34
  • It seems that when I went to delete the fields that were repeated (meaning not in the embedded document), it also deleted the embedded document, I presume is because the embedded document is dependent on those fields? This is what I did: db.cooking.updateMany({}, {$unset:{"borough":{}, "street":{}, "zipcode":{}}}) – eurojourney Dec 13 '19 at 12:20
  • _No_, the embedded document fields are not dependent on the original fields.The method usage looks right. The fields dont have any _relationship_. – prasad_ Dec 13 '19 at 12:51
  • But then, why would the embedded document disappear when I executed the expression db.cooking.updateMany({}, {$unset:{"borough":{}, "street":{}, "zipcode":{}}}), is there a better way to delete these fields? – eurojourney Dec 13 '19 at 15:51
  • I think the syntax to unset (remove) the fields is: `$unset:{"borough": "" }`. May be there was no embedded document at all. Verify what the document was _before_ running the delete command (and _after_) for _each_ run. The code I had provided is accurate. – prasad_ Dec 14 '19 at 02:17
  • I am confused and since I am new to MongoDB this confusion may come across as silly to some of you, but, I thought that the aggregate command does not modify the table/collection permanently, but just returns the desired arrangement of data based on what it is asked, in this case I am rearranging the data to create an embedded document. However, that change is not permanent in the collection. If I see my collection in, say, Mongo Atlas, after the aggregate command is executed in Robo 3T, the collection looks the same. That would explain why when I do the $unset, it actually deletes de fields. – eurojourney Dec 16 '19 at 11:00
  • _"... I thought that the aggregate command does not modify the table/collection permanently, but just returns the desired arrangement of data based on what it is asked, in this case I am rearranging the data to create an embedded document. However, that change is not permanent in the collection."_ - this is correct, aggregation query filters (matches) or projects (modifies and shows the desired fields) and you get an output. The collection and its documents are NOT modified, but _presented_ in a modified form. – prasad_ Dec 16 '19 at 13:05
  • _NOTE:_ The `$out` stage of the aggregation query _replaces_ an existing collection, if the collection already exists (effectively you have a modified collection). – prasad_ Dec 16 '19 at 13:05