MongoDB Advanced Query - Getting data based on Array of objects

Question

On Mongo 2.4.6

Collection of Users
{
    "_id" : User1,
     "orgRoles" : [ 
        {"_id" : 1, "app" : "ANGRYBIRDS", "orgId" : "CODOE"}, 
        {"_id" : 2, "app" : "ANGRYBIRDS", "orgId" : "MSDN"}
    ],
},
{
    "_id" : User2,
     "orgRoles" : [ 
        {"_id" : 1, "app" : "ANGRYBIRDS", "orgId" : "CODOE"}, 
        {"_id" : 2, "app" : "HUNGRYPIGS", "orgId" : "MSDN"}
    ],
},
{
    "_id" : User2,
     "orgRoles" : [ 
        {"_id" : 1, "app" : "ANGRYBIRDS", "orgId" : "YAHOO"}, 
        {"_id" : 2, "app" : "HUNGRYPIGS", "orgId" : "MSDN"}
    ],
}

With data that looks like above, I'm trying to write a query to get:

All the id's of the users that have only one ANGRYBIRDS app and that ANGRYBIRDS app is in the CODOE organization.

So it would return User2 because they have 1 ANGRYBIRDS and is in the ORG "CODOE" but not User1 because they have two ANGRYBIRDS or User3 because they don't have an ANGRYBIRDS app in the "CODOE" organization. I'm fairly new to mongo queries, so any help is appreciated.

Answer 1

One way of doing it using the aggregation pipeline is:

db.users.aggregate([

// Match the documents with app being "ANGRYBIRDS" and orgID being "CODE"
// Note that this step filters out most of the documents and is good to have
// at the start of the pipeline, moreover it can make use of indexes, if
// used at the beginning of the aggregation pipeline.
{
    $match : {
        "orgRoles.app" : "ANGRYBIRDS",
        "orgRoles.orgId" : "CODOE"
    }
},

// unwind the elements in the orgRoles array
{
    $unwind : "$orgRoles"
},

// group by userid and app
{
    $group : {
        "_id" : {
            "id" : "$_id",
            "app" : "$orgRoles.app"
        },
        // take the id and app of the first document in each group, since all
        // the
        // other documents in the group will have the same values.
        "id" : {
            $first : "$_id"
        },
        "app" : {
            $first : "$orgRoles.app"
        },
        // orgId can be different, so form an array for each group.
        "orgId" : {
            $push : {
                "id" : "$orgRoles.orgId"
            }
        },
        // count the number of documents in each group.
        "count" : {
            $sum : 1
        }
    }
},
// find the matching group
{
    $match : {
        "count" : 1,
        "app" : "ANGRYBIRDS",
        "orgId" : {
            $elemMatch : {
                "id" : "CODOE"
            }
        }
    }
},

// project only the userid
{
    $project : {
        "id" : 1,
        "_id" : 0
    }
} ]);

Edit: Removed mapping the aggregation result, since the problem requires solution in v2.4.6, and according to the documentation.

Changed in version 2.6: The db.collection.aggregate() method returns a cursor and can return result sets of any size. Previous versions returned all results in a single document, and the result set was subject to a size limit of 16 megabytes.

Answer 2

To do something with a few more detailed conditions not immediately offered by standard operators, then your best approach is to use the aggregation framework. This allows you do some processing to work our your conditions, such as the number of matches:

db.collection.aggregate([
     // Filter the documents that are possible matches
     { "$match": { 
         "orgRoles": { 
             "$elemMatch": { 
                 "app": "ANGRYBIRDS", "orgId": "CODOE"
             }
         }
     }},

     // De-normalize the array content
     { "$unwind": "$orgRoles" },

     // Group and count the matches
     { "$group": {
          "_id": "$_id",
          "orgRoles": { "$push": "$orgRoles" },
          "matched": { 
              "$sum": {
                  "$cond": [
                      { "$eq": ["$orgRoles.app", "ANGRYBIRDS"] },
                      1,
                      0
                  ]
              }
          }
     }},

     // Filter where matched is more that 1
     { "$match": { 
         "orgRoles": { 
             "$elemMatch": { 
                 "app": "ANGRYBIRDS", "orgId": "CODOE"
             }
         },
         "matched": 1
     }},

     // Optionally project to just keep the original fields
     { "$project": { "orgRoles": 1 } }
])

The main thing here happens after the initial $match is processed to only return those documents that have at least one array element matching the main condition, and then after the array elements are processed with $unwind so they can be inspected individually.

The trick is the conditional $sum operation with the $cond operator which is a "ternary". This evaluates "howMany" matches were found in the array to the "ANGRYBIRDS" string. Following this you $match again in order to "filter" any documents that had a match count of more than one. Still leaving the other condition in there, but that is really not necessary.

Just for the record, this is also possible with using the JavaScript evaluation of the $where clause, but due to that it is likely not to be as efficient at processing:

db.collection.find({
    "orgRoles": {
        "$elemMatch": {
            "app": "ANGRYBIRDS", "orgId": "CODOE"
        }
    },
    "$where": function() {
        var orgs = this.orgRoles.filter(function(el) {
            return el.app == "ANGRYBIRDS";
        });
        return ( orgs.length == 1 );
    }
})