How to Aggregate MongoDB Nested Query?

Question

So I have been trying to aggregate two particular sets of documents for quite some time but to no success until now. My documents are: Document1:

 {
        "_id": {
            "$oid": "606d68e88346778f70f14b50"
        },
        "user": 123,
        "task1": {
            "Humor": 0,
            "Spirituality": 1,
            "Fairness": 0
        },
        "task2": {
            "Humor": 0,
            "Spirituality": 1,
            "Fairness": 0
        },
        "task3": {
            "Humor": 0,
            "Spirituality": 1,
            "Fairness": 0
        }
    }

Document2:

{
    "_id": {
        "$oid": "606d68fe8346778f70f14b51"
    },
    "user": 123,
    "task1": {
        "Humor": 0,
        "Spirituality": 1,
        "Fairness": 0
    },
    "task2": {
        "Humor": 0,
        "Spirituality": 1,
        "Fairness": 0
    },
    "task3": {
        "Humor": 0,
        "Spirituality": 1,
        "Fairness": 0
    }
}

What I want to be done is to aggregate the score for all traits in all the documents. For example: I have Spirituality appearing three times in document 1 and if I sum it up, I get a score of 3 in document1 and a score of 3 in document2 again for a combined score of 6 in total.

The expected output should be:

{"user":123,"Humor":0,"Spirituality":6,"Fairness":0}

I have been trying different approaches but to no avail so far. Would appreciate the help.

The expected output should be: {"user":123,"Humor":0,"Spirituality":6,"Fairness":0} — Moeed Lodhi

Tushar Gupta - curioustushar Tushar Gupta - curioustushar · Accepted Answer · 2021-04-07T08:56:49

Demo - https://mongoplayground.net/p/RV8W9t6zERq

$group by userid and push tasks to individuals task1, task2, task3 arrays

in 2nd pipeline $project combine into a one tasks array

In 3rd pipeline get sum

db.collection.aggregate([
  { $group: { _id: "$user", task1: { $push: "$task1" }, task2: { $push: "$task2" }, task3: { $push: "$task3" } } },
  { $project: { _id: 0, userId: "$_id", tasks: { "$concatArrays": ["$task1", "$task2", "$task3"] }} },
  { $project: { userId: 1, "Spirituality": {   $sum: "$tasks.Spirituality" }, "Humor": { $sum: "$tasks.Humor" }, "Fairness": {   $sum: "$tasks.Fairness" } }
  }
])

How to Aggregate MongoDB Nested Query?

1 Answers