Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mongoDB: how to reverse $unwind

Consider this collection of test results:

[{
    _id: ObjectId(...),
    name: "Test1",
    acts: [
    {
        name: "act1", 
        tests: [
            {name: "test1", result: true}, 
            {name: "test2", result: true}]
    }]
},
{
    _id: ObjectId(...),
    name: "Test2",
    acts: [
    {
        name: "act1", 
        tests: [
            {name: "test1", result: true}, 
            {name: "test2", result: false}]
    }, 
    {
        name: "act2", 
        tests: [
            {name: "test3", result: true}]
    }]
}]

I'm trying to use aggregations to create a calculated field with the sum of all test results, I want something like this:

[{
    _id: ObjectId(...),
    name: "Test1",
    result: true, //new aggregated value
    acts: [
    {
        name: "act1", 
        result: true, //new aggregated value
        tests: [
            {name: "test1", result: true}, 
            {name: "test2", result: true}]
    }]
},
{
    _id: ObjectId(...),
    name: "Test2",
    result: false, //new aggregated value
    acts: [
    {
        name: "act1", 
        result: false, //new aggregated value
        tests: [
            {name: "test1", result: true}, 
            {name: "test2", result: false}]
    }, 
    {
        name: "act2", 
        result: true, //new aggregated value
        tests: [
            {name: "test3", result: true}]
    }]
}]

I have tried using aggregate and $unwind, $project and $group:

aggregate([
  {$unwind: "$acts"},
  {$unwind: "$acts.tests"},
  {$project: {name: 1, acts: 1, failed: {$cond: {if: {$eq: ["$acts.tests.test", "true" ]}, then: 0, else: 1}}}},
  {$group: {_id: "$_id", failedCount: {$sum: "$failed"}, acts: {$push: "$acts.tests"}}}
])

But I can't get it to reverse the $unwind operation, I only get the resulting data structure to differ from the original. Is it possible to get the result to look exactly like the original collection but with the new aggregated values?

/gemigspam

like image 371
gemigspam Avatar asked Aug 05 '14 10:08

gemigspam


1 Answers

There is a particular trick to how this is handled, but firstly if you have MongoDB 2.6 or greater available then you can actually do what you want without using $unwind. This can be very handy for performance if you are processing a lot of documents.

The key operators here are $map which processes arrays in place and the $allElementsTrue operator which will evaluate your "result" fields. The usage of "map" here allows both the testing of the inner "tests" array to see where the "result" fields in there all meet the true condition. In the outer array case, this "result" can be placed into those documents as you require, and of course the full evaluation for the document follows the same rules:

db.test.aggregate([
    { "$project": {
        "name": 1,
        "result": {
            "$allElementsTrue": {
                "$map": {
                    "input": "$acts",
                    "as": "act",
                    "in": {
                        "$allElementsTrue": {
                            "$map": {
                                 "input": "$$act.tests",
                                 "as": "test",
                                 "in": "$$test.result"
                            }
                        }
                    }
                }
            }
        },
        "acts": {
            "$map": {
                 "input": "$acts",
                 "as": "act",
                 "in": {
                    "name": "$$act.name",
                    "result": {
                        "$allElementsTrue": {
                            "$map": {
                                "input": "$$act.tests",
                                "as": "test",
                                "in": "$$test.result"
                            }
                        }
                    },
                    "tests": "$$act.tests"
                 }
            }
        }
    }}
])

The way to do this in earlier versions requires you to $group back in two steps in order to "rebuild" the arrays while doing the tests on those "result" fields again. The other difference here is also using the $min operator as false will be considered a lesser value than true and evaluates to the same "allElements" concept:

db.test.aggregate([
    { "$unwind": "$acts" },
    { "$unwind": "$acts.tests" },
    { "$group": {
        "_id": {
            "_id": "$_id",
            "name": "$name",
            "actName": "$acts.name"
        },
        "result": { "$min": "$acts.tests.result" },
        "tests": {
           "$push": {
               "name": "$acts.tests.name",
               "result": "$acts.tests.result"
           }
        }
    }},
    { "$group": {
        "_id": "$_id._id",
        "name": { "$first": "$_id.name" },
        "result": { "$min": "$result" },
        "acts": {
            "$push": {
                "name": "$_id.actName",
                "result": "$result",
                "tests": "$tests"
            }
        }
    }}
])
like image 72
Neil Lunn Avatar answered Sep 21 '22 10:09

Neil Lunn