CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Matt Carlotta
Matt Carlotta

Reputation: 19762

Aggregate subdocuments, filter them by a list of ids, and group values by id

I have a list of mongo ObjectIds that I need to map over, assign some responses (which may or may not exist) to that id, then generate an availability average based upon the responses. For example, I have a list of ObjectIds which refer to Users:

["5d978d372f263f41cc624727", "5d978d372f263f41cc624728", "5d978d372f263f41cc624729"]

And an aggregated list of Events which contain an employeeResponses subdocument that, if they've responded, records their _id , response, and any event notes:

[
  {
    ...
    employeeResponses: [
      {
        _id: "5d978d372f263f41cc624727",
        response: "I want to work.",
        notes: ""
      },
      {
        _id: "5d978d372f263f41cc624728",
        response: "Available to work.",
        notes: ""
      }
    ]
  },
  {
    ...
    employeeResponses: [
      {
        _id: "5d978d372f263f41cc624727",
        response: "I want to work.",
        notes: ""
      },
      {
        _id: "5d978d372f263f41cc624728",
        response: "Prefer not to work.",
        notes: ""
      }
    ]
  },
  {
    ...
    employeeResponses: [
      {
        _id: "5d978d372f263f41cc624727",
        response: "Not available to work.",
        notes: ""
      },
      {
        _id: "5d978d372f263f41cc624729",
        response: "Not available to work.",
        notes: ""
      },
    ]
  }
]

What I need to do is assign their response (or lack of response) to their id in a responses array:

[
  {
    "_id": "5d978d372f263f41cc624727",
    "responses": ["I want to work.", "I want to work.", "Not available to work."]
  },
  {
    "_id": "5d978d372f263f41cc624728",
    "responses": ["Available to work.", "Prefer not to work.", "No response."] 
  }
  {
    "_id": "5d978d372f263f41cc624729",
    "responses": ["No response.", "No response.", "Not available to work."] 
  }
]

Then, based upon their response, generate a member availability average (available: ["I want to work.", "Available to work"]; unavailable: ["Prefer not to work.", "Not available to work.", "No response."]):

[
  {
    "_id": "5d978d372f263f41cc624727",
    "availability": 66
  },
  {
    "_id": "5d978d372f263f41cc624728",
     "availability": 33,
  }
  {
    "_id": "5d978d372f263f41cc624729",
     "availability": 0,  
  }
]

With the help of @mickl, I can aggregate the responses for 1 ObjectId, but I'm struggling to expand it to a list of ObjectIds without mapping over the ids and aggregating per user -- which seems very inefficient and costly.

Upvotes: 0

Views: 629

Answers (1)

Haruo
Haruo

Reputation: 516

You can Try This:

Original Example: https://mongoplayground.net/p/_pDaErcuvkP

Adjusted Query in https://mongoplayground.net/p/oURNxgNiprl:

    db.collection.aggregate([
  {
    $unwind: {
      path: "$employeeResponses",          
    }
  },
  {
    "$group": {
      _id: "$employeeResponses._id",
      "availability": {
        "$sum": {
          "$cond": [
            {
              "$in": [
                "$employeeResponses.response",
                [
                  "Available to work.",
                  "I want to work."
                ]
              ]
            },
            1,
            0
          ]
        }
      }
    }
  }
])

Upvotes: 1

Related Questions