CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
mthh
mthh

Reputation: 123

How to group by multiple in MongoDB

first i want to get city with group by, and second want to group by and counting of box_id. how to get it?

Link example : MongoPlayground

Input

[
  {
    "_id": ObjectId("5dcbb949e7599115cf1b9441"),
    "date": "2019-11-13T15:05:28.379Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bekasi",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2098),
    "update_at": "2019-11-13T15:05:28.391Z"
  },
  {
    "_id": ObjectId("5dcbba2ee7599115cf1ba50b"),
    "date": "2019-11-13T15:09:17.001Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bekasi",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2099),
    "update_at": "2019-11-13T15:09:17.009Z"
  },
  {
    "_id": ObjectId("5dcbbb14e7599115cf1bb9ff"),
    "date": "2019-11-13T15:13:06.010Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bekasi",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2098),
    "update_at": "2019-11-13T15:13:06.019Z"
  },
  {
    "_id": ObjectId("5dcf596e8c3dfe4d6e865acf"),
    "date": "2019-11-16T09:05:32.160Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bekasi",
      "country": "Indonesia",
      "district": "Gandasari",
      "latitude": -6.27103,
      "longitude": 107.09003333333334,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-16T09:05:32.175Z",

  },
  {
    "_id": ObjectId("5dcf5a538c3dfe4d6e866a78"),
    "date": "2019-11-16T09:09:21.265Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bekasi",
      "country": "Indonesia",
      "district": "Gandasari",
      "latitude": -6.27103,
      "longitude": 107.09003333333334,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-16T09:09:21.273Z"
  },
  {
    "_id": ObjectId("5dde3eab4144014acda0563b"),
    "date": "2019-11-27T16:15:21.740Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bogor",
      "country": "Indonesia",
      "district": "Cibinong",
      "latitude": -6.498371666666666,
      "longitude": 106.85079166666668,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2098),
    "update_at": "2019-11-27T16:15:21.753Z"
  },
  {
    "_id": ObjectId("5dde3fa14144014acda083ac"),
    "date": "2019-11-27T16:19:27.102Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bogor",
      "country": "Indonesia",
      "district": "Cibinong",
      "latitude": -6.498371666666666,
      "longitude": 106.85079166666668,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2095),
    "update_at": "2019-11-27T16:19:27.114Z"
  },
  {
    "_id": ObjectId("5dde40984144014acda09bb3"),
    "date": "2019-11-27T16:23:32.742Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bogor",
      "country": "Indonesia",
      "district": "Cibinong",
      "latitude": -6.498371666666666,
      "longitude": 106.85079166666668,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2095),
    "update_at": "2019-11-27T16:23:32.753Z"
  },
  {
    "_id": ObjectId("5dde41a64144014acda0bd03"),
    "date": "2019-11-27T16:28:04.557Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bogor",
      "country": "Indonesia",
      "district": "Cibinong",
      "latitude": -6.498371666666666,
      "longitude": 106.85079166666668,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2095),
    "update_at": "2019-11-27T16:28:04.569Z"
  },
  {
    "_id": ObjectId("5dde429b4144014acda0d6a9"),
    "date": "2019-11-27T16:32:09.888Z",
    "id": NumberInt(166),
    "location": {
      "city": "Bekasi",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2094),
    "update_at": "2019-11-27T16:32:09.909Z"
  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced4b"),
    "date": "2019-11-13T15:05:28.379Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2095),
    "update_at": "2019-11-13T15:05:28.391Z"
  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced4c"),
    "date": "2019-11-13T15:09:17.001Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-13T15:09:17.009Z"
  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced4d"),
    "date": "2019-11-13T15:13:06.010Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Jatimulya",
      "latitude": -6.266871666666666,
      "longitude": 107.01972499999998,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-13T15:13:06.019Z"
  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced4e"),
    "date": "2019-11-16T09:05:32.160Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Gandasari",
      "latitude": -6.27103,
      "longitude": 107.09003333333334,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-16T09:05:32.175Z",

  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced4f"),
    "date": "2019-11-16T09:09:21.265Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Gandasari",
      "latitude": -6.27103,
      "longitude": 107.09003333333334,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2096),
    "update_at": "2019-11-16T09:09:21.273Z"
  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced50"),
    "date": "2019-11-27T16:15:21.740Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Cibinong",
      "latitude": -6.498371666666666,
      "longitude": 106.85079166666668,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-27T16:15:21.753Z"
  },
  {
    "_id": ObjectId("5ccbb96706d1d47a4b2ced51"),
    "date": "2019-11-27T16:19:27.102Z",
    "id": NumberInt(166),
    "location": {
      "city": "Jakarta",
      "country": "Indonesia",
      "district": "Cibinong",
      "latitude": -6.498371666666666,
      "longitude": 106.85079166666668,
      "province": "Jawa Barat"
    },
    "box_id": NumberInt(2097),
    "update_at": "2019-11-27T16:19:27.114Z"
  }
]

Implementation 

db.collection.aggregate([
  {
    "$match": {
      "update_at": {
        "$gte": "2019-11-02T00:00:00.0Z",
        "$lt": "2019-12-06T00:00:00.0Z"
      },
      "id": {
        "$in": [
          166
        ]
      }
    }
  },
  {
    $group: {
      _id: "$location.city",
      count: {
        $sum: 1
      }
    }
  },
  {
    "$project": {
      _id: 0,
      city: "$_id",
      count: "$count"
    }
  },
  {
    $sort: {
      value: -1
    }
  }
])

Actual Output:

 [
  {
    "city": "Jakarta",
    "value": 7
  },
  {
    "city": "Bekasi",
    "value": 6
  },
  {
    "city": "Bogor",
    "value": 4
  }
]

My expectation code show first i want to grouping city and next want to grouping to count of box_id, you can see my expected output. look count_box_id is total box id that has been grouped.

Expected Output

[
  {
    "city": "Jakarta",
    "count": 7,
    "count_box_id" : 3
  },
  {
    "city": "Bekasi",
    "count": 6,
    "count_box_id" : 4 // example box_id = 2098 2099 2097 2094
  },
  {
    "city": "Bogor",
    "count": 4,
    "count_box_id" : 2
  }
]

Upvotes: 1

Views: 44

Answers (1)

Ashh
Ashh

Reputation: 46451

You can use $addToSet to push into single array with unique values and then use $size to count the number of elements in the array.

db.collection.aggregate([
  { "$match": {
    "update_at": {
      "$gte": "2019-11-02T00:00:00.0Z",
      "$lt": "2019-12-06T00:00:00.0Z"
    },
    "id": { "$in": [166] }
  }},
  { "$group": {
    "_id": "$location.city",
    "count": { "$sum": 1 },
    "box_id": { "$addToSet": "$box_id" }
  }},
  { "$project": {
    "_id": 0,
    "city": "$_id",
    "count": "$count",
    "box_id": { "$size": "$box_id" }
  }},
  { "$sort": { "value": -1 } }
])

MongoPlayground

Upvotes: 1

Related Questions