0

I am new to mongodb, I have a requirement and would like to know how to generate custom resultset using Mongo aggregate operator. Any help would be appreciated.

Need to group the collection by "company" and "status" and would need to produce resultset given below.

Collection

[
  {
    "company": "google",
    "status": "active",
    "offer": {
      "job": "developer",
      "salary": 10000.00
    },
    
  },
  {
    "company": "google",
    "status": "active",
    "offer": {
      "job": "designer",
      "salary": 500000.00
    },
    
  },
  {
    "company": "amazon",
    "status": "inactive",
    "offer": {
      "job": "designer",
      "salary": 500000.00
    },
    
  }
]

Expected Result-Set

[
  {
      "company" : "google",
      "report" : [{
          "status" : "active",
          "totalSalary" : 60000
      },
      {
          "status" : "inactive",
          "totalSalary" : 0
      }]
  },
  {
      "company" : "amazon",
      "report" : [{
          "status" : "active",
          "totalSalary" : 0
      },
      {
          "status" : "inactive",
          "totalSalary" : 500000.00
      }]
  }
]
Hoppo
  • 1,130
  • 1
  • 13
  • 32
Maneesh Madhav
  • 1
  • 1

1 Answers1

1

You should 100% check the official documentation on aggregates, it's a bit complicated at first but once you get the hang of it they're great. I also recommend you https://mongoplayground.net/, it's a great site for doing this kind of tests.

What you're looking for is something like this

db.collection.aggregate([
  {
    $group: {
      _id: {
        company: "$company"
      },
      report: {
        $addToSet: "$offer"
      }
    }
  }
])

You can test it here. You also probably want to rename the resulting _id field that's mandatory in a group aggregate. You can find how to do that here

Webox
  • 178
  • 6
  • Thanks Webox, for the quick response. I need multiple fields (company & status) and result should group it based on the field "company" – Maneesh Madhav Jul 02 '20 at 16:33
  • My bad, you're looking to add up all active and inactive in one single element of the report array. So every report will have one "active" adding up all active jobs and 1 inactive (per company) adding up all inactive reports? And if there are 0 of one category it should be 0? – Webox Jul 02 '20 at 17:34
  • Absolutely, that is what I am looking for. – Maneesh Madhav Jul 02 '20 at 17:55
  • I can generate something similar; however, I need to dedup the company by taking it out .[ { "company": "google", "status": "active", "total": 10000 }, { "company": "google", "status": "inactive", "total": 500000 }, { "company": "amazon", "status": "inactive", "total": 500000 } ] – Maneesh Madhav Jul 03 '20 at 09:14