CODEWITHSUNDEEP
node.jsmongodbaggregate
Shambhavi_RB
Shambhavi_RB

Reputation: 121

Mongo db group within a document

I have a User schema and a Order schema like below: User -

{
    "_id" : user1,
    "fullName" : "test",
    "phone" : "8494948432",
    "phoneCode" : "+91",
}

Orders -

/* 1 */
{
    "orderId" : "ODR0000000486",
    "status" : "delivered",  //status can be delivered, undelivered, etc
    "user": user1 //refering user
}
/* 2 */
{
    "orderId" : "ODR0000000487",
    "status" : "undelivered",  
    "user": user1 
}

Now i want to get user's delivered order count, undelivered count, a object like this

{
    "_id": user1,
    "fullName" : "test",
    "phone" : "8494948432",
    "phoneCode" : "+91",
    "delivered": 1,
    "undelivered": 1
}

I have tried with aggregate on users using two different look up to orders, but it doesn't look good and is not efficient when there are mutiple order data to be calculated. db.getCollection('users').aggregate([

{
    "$lookup": {
      "from": "orders",
      "let": {
        "userId": "$_id"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$eq": [
                    "$status",
                    "delivered"
                  ]
                },
                {
                  "$eq": [
                    "$user",
                    "$$userId"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "delivered"
    }
  },
  {
    "$lookup": {
      "from": "orders",
      "let": {
        "userId": "$_id"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$and": [
                {
                  "$eq": [
                    "$status",
                    "undelivered"
                  ]
                },
                {
                  "$eq": [
                    "$user",
                    "$$userId"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "undelivered"
    }
  },
  {
    "$project": {
      "fullName": 1,
      "phone": 1,
      "phoneCode": 1,
      "delivered": {
        "$size": "$delivered"
      },
      "undelivered": {
        "$size": "$undelivered"
      }
    }
  }
])

Is there any way I can get this data by a single look-up and other mongo db options/functions?

Upvotes: 0

Views: 38

Answers (1)

hbamithkumara
hbamithkumara

Reputation: 2534

Try this..

Sample live demo

db.users.aggregate([
  {
    $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "user",
      as: "orders"
    }
  },
  {
    $unwind: "$orders"
  },
  {
    $group: {
      _id: {
        "userId": "$_id"
      },
      "data": {
        "$first": "$$ROOT"
      },
      "delivered": {
        "$sum": { "$cond": [{ "$eq": [ "$orders.status", "delivered"]}, 1, 0 ] }
      },
      "undelivered": {
        "$sum": { "$cond": [{ "$eq": [ "$orders.status", "undelivered"]}, 1, 0 ] }
      }
    }
  },
  {
    $project: {
      "_id": "$_id.userId",
      "fullName": "$data.fullName",
      "phone": "$data.phone",
      "phoneCode": "$data.phoneCode",
      "delivered": "$delivered",
      "undelivered": "$undelivered"
    }
  }
])

Upvotes: 1

Related Questions