CODEWITHSUNDEEP
mongodb
user2101699
user2101699

Reputation: 257

MongoDB count values of multible nested documents

My data looks something like that:

[
  {
    "_id": 1,
    "members": [
      {
        "id": 1,
        "name": "name_1",
        "assigned_tasks": [
          1,
          2,
          3
        ]
      },
      {
        "id": 1,
        "name": "name_2",
        "assigned_tasks": [
          1
        ]
      }
    ],
    "tasks": [
      {
        "id": 1,
        "name": "task_1",
        
      },
      {
        "id": 2,
        "name": "task_2",
        
      },
      {
        "id": 3,
        "name": "task_3",
      }
    ]
  }
]

I have a collection that represents a "class" which contains a list of members and a list of projects. Each member can be assigned to multiple projects.

I wanna be able to count the number of members assigned to each of the tasks in the results and add it as a new field like:

[
  {
    "_id": 1,
    "members": [
      {
        "id": 1,
        "name": "name_1",
        "assigned_tasks": [
          1,
          2,
          3
        ]
      },
      {
        "id": 1,
        "name": "name_2",
        "assigned_tasks": [
          1
        ]
      }
    ],
    "tasks": [
      {
        "id": 1,
        "name": "task_1",
        "number_of_assigned_members":2
      },
      {
        "id": 2,
        "name": "task_2",
        "number_of_assigned_members":1
      },
      {
        "id": 3,
        "name": "task_3",
        "number_of_assigned_members":2
      }
    ]
  }
]

How can I create that query?

Upvotes: 1

Views: 41

Answers (1)

turivishal
turivishal

Reputation: 36094

You can use $map and than $reduce,

  • $map tasks through object by object check in $reduce on members, if assigned_tasks is available or not, if available then add 1 otherwise 0,
db.collection.aggregate([
  {
    $addFields: {
      tasks: {
        $map: {
          input: "$tasks",
          as: "t",
          in: {
            $mergeObjects: [
              "$$t",
              {
                number_of_assigned_members: {
                  $reduce: {
                    input: "$members",
                    initialValue: 0,
                    in: {
                      $cond: [
                        { $in: ["$$t.id", "$$this.assigned_tasks"] },
                        { $add: ["$$value", 1] },
                        "$$value"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

Upvotes: 2

Related Questions