Mongodb copy field to another collection

Question

lets say I have collection orders

{
   "id":"1",
   "items":{
      "itemId":"1000",
      "name":"Item 1",
      "status":"STATUS"
   }
}
{
   "id":"2",
   "items":{
      "itemId":"1000",
      "name":"Item 1",
      "status":"ANOTHER_STATUS"
   }
}

I have also collection item_projections which is

{
   "id":"1",
   "itemId":"1000",
   "name":"Item 1",
   "orderId":"1"
}
{
   "id":"1",
   "itemId":"1000",
   "name":"Item 1",
   "orderId":"2"
}

For every item from collection orders I would like to copy field status to projection to matched on order id and itemId to have

{
   "id":"1",
   "itemId":"1000",
   "name":"Item 1",
   "orderId":"1",
   "status":"STATUS"
}
{
   "id":"1",
   "itemId":"1000",
   "name":"Item 1",
   "orderId":"2",
   "status":"ANOTHER_STATUS"
}

Is it possible to do it with aggregation lookup and merge pipelines?

user_8017690 · Answer

Solution 1

$lookup - item_projections collection (key: orderId) join orders collection (key: id) and return orders array field with the document(s) of status field only.
$replaceRoot - Replace input document with new document.

2.1. $mergeObjects - Merge root document with the first document of orders.
$unset - Remove orders field.

db.item_projections.aggregate([
  {
    "$lookup": {
      "from": "orders",
      "localField": "orderId",
      "foreignField": "id",
      "pipeline": [
        {
          $project: {
            status: "$items.status"
          }
        }
      ],
      "as": "orders"
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$$ROOT",
          {
            $first: "$orders"
          }
        ]
      }
    }
  },
  {
    $unset: "orders"
  }
])

Sample Mongo Playground (Solution 1)

Solution 2

Or you can replace the 2nd and 3rd stages with $project.

{
  $project: {
    "id": 1,
    "itemId": 1,
    "name": 1,
    "orderId": 1,
    "status": {
      $first: "$orders.status"
    }
  }
}

Sample Mongo Playground (Solution 2)

Mongodb copy field to another collection

1 Answers