How to group documents into an array of other model in mongodb

user_15842870 0
Viewed 85

Since I am new to mongodb and nodejs I am stuck here , I have a collection of claims every claim have an orderId as field , what I want to do is to group all the claims by order id and display them in an array of orders for example I have these claims:

[
    {
        message: "claim1",
        orderId: 123456789
    },
    {
        message: "claim2",
        orderId: 0000000
    },
    {
        message: "claim3",
        orderId: 123456789
    },
    {
        message: "claim4",
        orderId: 0000000
    }
]

What I want to do is to group these claims by order which I find that I can do it using $group while searching on internet, but the difficult part is that I want the result to be an array of orders and every case of this array contain some order fields and an array of claims of this order. This is how I want the result to be :

[
    {
        orderId: 123456789,
        orderNumber: "OrderNumber"
        // i want to get this from the order document in the database,
        claims: [
            {
                message: "claim1"
            },
            {
                message: "claim2"
            }
        ]
    },
    {
        orderId: 00000000,
        orderNumber: "OrderNumber"
        // i want to get this from the order document in the database,
        claims: [
            {
                message: "claim2"
            },
            {
                message: "claim4"
            }
        ]
    }
]

And this an order example :

{
 _id:123456789,
  orderNumber:112,
  orderPrice:14,
  created_at: 2020-04-29T16:34:48.522+00:00
  updated_at: 2020-05-01T14:03:04.844+00:00
}

I was searching for a solution for a long time but I can't find any, I began to think that mabye this is not possible with mongodb aggregation . Thank you for your help

aggregation-framework node.js mongodb grouping
1 Answers

So our strategy will be to first $group on the orderId to gather all the claims, and then we'll $lookup the order number, like so:

db.collection.aggregate([
  {
    $group: {
      _id: "$orderId",
      claims: {
        $push: {
          message: "$message"
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "orders",
      "localField": "_id",
      "foreignField": "_id",
      "as": "orders"
    }
  },
  {
    $unwind: "$orders"
  },
  {
    $project: {
      _id: 0,
      claims: 1,
      orderId: "$_id",
      orderNumber: "$orders.orderNumber"
    }
  }
])
user_107703700
Related