Mongo DB Join array in 2 different collections

Question

I am new to Mongo DB and queries. It will be highly appreciated if someone can help me querying the relevant data. I have 2 collections: Order & ReturnRequest. Order has an array of OrderItems and ReturnRequest has reference to orderID and an array of returnItems. I need to get all the returnItems , but the price of item is in OrderItem array of Order Collection The Order Collection is as below

    {
    "_Orderid": "d991e92a-766c-054e-9ad8-1c902acc6efc",
    "OrderItems": {
       "id": "d991e92a46831",
       "ProductID": "abc.",
       "VendorID": "abv",
       "Quantity": "5",
       "UnitPrice": "220",
       "Currency": "USD"
       }
    }

The ReturnRequest collection is as below

    {
    "_returnid": "1qqwerda-766c-054e-9ad8-1c902acc6efc",
    "vendorID": "abv",
    "orderId": "d991e92a-766c-054e-9ad8-1c902acc6efc"
    "ReturnItems": {
        "returnItemid": "AAARTY92a46831",
        "ProductID": "abc",
        "Quantity": "2",
       }
    }

I need to get the result as

    {
    "Orderid": "1qqwerda-766c-054e-9ad8-1c902acc6efc",
    "vendorID": "abv",
    "Currency":"USD",
    "ReturnRequestid": "1qqwerda-766c-054e-9ad8-1c902acc6efc",
    "ReturnItemid": "AAARTY92a46831",
    "ProductID": "abc",
    "Quantity": "2",
    "Price" :"220"
    }

Conditions are to join VendorID, OrderID, ProductID between the said collections

swapnil bandiwadekar · Accepted Answer · 2020-12-15 13:16:05Z

1

Use MongoDB aggregate for this purpose.

ReturnRequest.aggregate([
  {
    $lookup: {
      from: "Order",
      localField:"orderId",
      foreignField: "_id",
      as: "orderDetails"
  },
  {
    $unwind: { 
      path: "orderDetails",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: "$_id",       //ReturnRequest will have _id. if not then you can put $_returnid here instead of $_id.
      "Orderid": { $first: "$_returnid" },
      "vendorID": { $first: "$vendorID" },
      "Currency": { $first: "$orderDetails.OrderItems.Currency" },
      "ReturnRequestid": { $first: "$_returnid" },
      "ReturnItemid": { $first: "$ReturnItems.returnItemid" },
      "ProductID": { $first: "$ReturnItems.ProductID" },
      "Quantity": { $first: "$ReturnItems.Quantity" },
      "Price": { $first: "$orderDetails.OrderItems.UnitPrice" }
    }
  }
]);

This is considering that ReturnRequest has single Order document matching. If there are more than one Order documents matching for a ReturnRequest, then consider grouping based on orderId than _returnid.

answered Dec 15, 2020 at 13:16

swapnil bandiwadekar

17811 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Samson Thomas Over a year ago

Sorry Swapnil. Can you please convert this to LINQ for better understanding. That would be highly helpful. -Thanks

Collectives™ on Stack Overflow

Mongo DB Join array in 2 different collections

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related