MongoDB lookup gives correct output but doesn't update collection-CodePudding

I have the following collections:

phones:

{"_id": {
    "$oid": "61d376c0b9887d4e736e6acb"
},
"brand": "Nokia",
"name": "Nokia 3210",
"picture": "https://fdn2.gsmarena.com/vv/bigpic/no3210b.gif",
"phoneId": "1" }

reviews:

{"_id": {
    "$oid": "61d333d0ac2d25f88d0bc8fa"
},
"phoneId": "1",
"rating": "3",
"dateOfReview": {
    "$date": "2008-11-18T00:00:00.000Z"
},
"title": "Ok phone to tide me over",
"userId": "47599" }

I'm running the following aggregation both on MongoCompass and MongoShell and gives me the expected result:

db.phones.aggregate([{$lookup: {
 from: 'reviews',
 localField: 'phoneId',
 foreignField: 'phoneId',
 as: 'reviews'
}}])

{ _id: ObjectId("61d376c0b9887d4e736e6acb"),
  brand: 'Nokia',
  name: 'Nokia 3210',
  picture: 'https://fdn2.gsmarena.com/vv/bigpic/no3210b.gif',
  phoneId: '1',
  reviews: 
   [ { _id: ObjectId("61d333d0ac2d25f88d0bc8fa"),
       phoneId: '1',
       rating: '3',
       dateOfReview: 2008-11-18T00:00:00.000Z,
       title: 'Ok phone to tide me over',
       userId: '47599' } ] }

But when I check the collection there is no field reviews, how can I do to add it to the collection permanently? Since I have a lot of reviews for each phone I would also like to add to the reviews' array in phones only the 20 most recent ones that match the phoneId, is it possible?

CodePudding user response：

You can use the $merge and $out pipeline stages to write data back to your database.

Note that these stages have to be the last stage in your pipeline.

E.g.

db.phones.aggregate([
  {
    $lookup: {
      from: 'reviews',
      localField: 'phoneId',
      foreignField: 'phoneId',
      as: 'reviews'
    }
  },
  {
    $merge: {
      into: 'phones', // collection-name
      on: 'phoneId',  // the identifier, used to identify the document to merge into
    }
  }
])

CodePudding user response：

Since for each phone # reviews is >> 20, you might wish to consider going after the reviews first, then doing a $lookup into phones. For a single known phone lookup the following will work; it will not work for >1 phone because the $limit cannot reference data fields (i.e. phoneId)

db.Xreviews.aggregate([
    {$match: {"phoneId":"2"}}
    ,{$sort: {"dateOfReview":-1}} // no getting around the desc sort..
    ,{$limit: 20}  // but we now limit to ONLY 20 reviews.

    // Put them "back together" as an array called "reviews"
    ,{$group: {_id:"$phoneId", reviews: {$push: "$$CURRENT"}}}

    // ... and pull in the phone data:
    ,{$lookup: {from: "Xphones", localField: "_id", foreignField: "phoneId", as: "X" }}
]);

The following will work across 1 or more phone or all of them BUT the consideration is the reviews array could be very large before being passed to the $slice operator to cut it back to 20:

db.Xreviews.aggregate([
    // $match for anything or nothing here; then:
    {$sort: {"dateOfReview":-1}}

    //  The order of the _id is not deterministic BUT the docs will be
    //  pushed onto the reviews array correctly in desc order:
    ,{$group: {_id:"$phoneId", reviews: {$push: "$$CURRENT"}}}

    //  Now simply overwrite the reviews array a shortened version:
    ,{$addFields: {reviews: {$slice: ["$reviews",20] }}}

 
    ,{$lookup: {from: "Xphones", localField: "_id", foreignField: "phoneId", as: "X" }}
]);

These two solutions end up with the phone details being stored in field 'X' which is an array of 1 item. Since we know the phoneID is 1:1, if we wish to get fancy, we can add this after the $lookup:

   // Pull item[0] out and merge with reviews AND make that the new
   // document:
   ,{$replaceRoot: { newRoot: {$mergeObjects: [ "$$CURRENT", {$arrayElemAt:["$X",0]} ]} }}
    ,{$unset: "X"}   // drop X completely