CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
ketrab321
ketrab321

Reputation: 591

MongoDB - Copy field to another collection

Let's 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 another 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 the field status to projection with matching 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?

Upvotes: 2

Views: 429

Answers (1)

Yong Shun
Yong Shun

Reputation: 51240

Solution 1

  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.

  2. $replaceRoot - Replace input document with new document.

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

  3. $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)

Upvotes: 2

Related Questions