MongoDB Projection of Nested Arrays

Question

I've got a collection "accounts" which contains documents similar to this structure:

{
    "email" : "[email protected]",
    "groups" : [
        {
            "name" : "group1",
            "contacts" : [
                { "localId" : "c1", "address" : "some address 1" },
                { "localId" : "c2", "address" : "some address 2" },
                { "localId" : "c3", "address" : "some address 3" }
            ]
        },
        {
            "name" : "group2",
            "contacts" : [
                { "localId" : "c1", "address" : "some address 1" },
                { "localId" : "c3", "address" : "some address 3" }
            ]
        }
    ]
}

Via

q = { "email" : "[email protected]", "groups" : { $elemMatch: { "name" : "group1" } } }
p = { "groups.name" : 0, "groups" : { $elemMatch: { "name" : "group1" } } }
db.accounts.find( q, p ).pretty()

I'll successfully get just the group of a specified account I'm interested in.

Question: How can I get a limited list of "contacts" within a certain "group" of a specified "account"? Let's suppose I've got the following arguments:

• account: email - "[email protected]"
• group: name - "group1"
• contact: array of localIds - [ "c1", "c3", "Not existing id" ]

Given these arguments I'd like to have the following result:

{
    "groups" : [
        {
            "name" : "group1", (might be omitted)
            "contacts" : [
                { "localId" : "c1", "address" : "some address 1" },
                { "localId" : "c3", "address" : "some address 3" }
            ]
        }
    ]
}

I don't need anything else apart from the resulting contacts.

Approaches

All queries try to fetch just one matching contact instead of a list of matching contacts, for the sake of simplicity. I've tried the following queries without any success:

p = { "groups.name" : 0, "groups" : { $elemMatch: { "name" : "group1", "contacts" : { $elemMatch: { "localId" : "c1" } } } } }
p = { "groups.name" : 0, "groups" : { $elemMatch: { "name" : "group1", "contacts.localId" : "c1" } } }
not working: returns whole array or nothing depending on localId


p = { "groups.$" : { $elemMatch: { "localId" : "c1" } } }
error: {
    "$err" : "Can't canonicalize query: BadValue Cannot use $elemMatch projection on a nested field.",
    "code" : 17287
}


p = { "groups.contacts" : { $elemMatch: { "localId" : "c1" } } }
error: {
    "$err" : "Can't canonicalize query: BadValue Cannot use $elemMatch projection on a nested field.",
    "code" : 17287
}

Any help is appreciated!

Answer 1

2017 Update

Such a well put question deserves a modern response. The sort of array filtering requested can actually be done in modern MongoDB releases post 3.2 via simply $match and $project pipeline stages, much like the original plain query operation intends.

db.accounts.aggregate([
  { "$match": {
    "email" : "[email protected]",
    "groups": {
      "$elemMatch": { 
        "name": "group1",
        "contacts.localId": { "$in": [ "c1","c3", null ] }
      }
    }
  }},
  { "$addFields": {
    "groups": {
      "$filter": {
        "input": {
          "$map": {
            "input": "$groups",
            "as": "g",
            "in": {
              "name": "$$g.name",
              "contacts": {
                "$filter": {
                  "input": "$$g.contacts",
                  "as": "c",
                  "cond": {
                    "$or": [
                      { "$eq": [ "$$c.localId", "c1" ] },
                      { "$eq": [ "$$c.localId", "c3" ] }
                    ]
                  } 
                }
              }
            }
          }
        },
        "as": "g",
        "cond": {
          "$and": [
            { "$eq": [ "$$g.name", "group1" ] },
            { "$gt": [ { "$size": "$$g.contacts" }, 0 ] }
          ]
        }
      }
    }
  }}
])

This makes use of of the $filter and $map operators to only return the elements from the arrays as would meet the conditions, and is far better for performance than using $unwind. Since the pipeline stages effectively mirror the structure of "query" and "project" from a .find() operation, the performance here is basically on par with such and operation.

Note that where the intention is to actually work "across documents" to bring details together out of "multiple" documents rather than "one", then this would usually require some type of $unwind operation in order to do so, as such enabling the array items to be accessible for "grouping".

---

This is basically the approach:

db.accounts.aggregate([
    // Match the documents by query
    { "$match": {
        "email" : "[email protected]",
        "groups.name": "group1",
        "groups.contacts.localId": { "$in": [ "c1","c3", null ] },
    }},

    // De-normalize nested array
    { "$unwind": "$groups" },
    { "$unwind": "$groups.contacts" },

    // Filter the actual array elements as desired
    { "$match": {
        "groups.name": "group1",
        "groups.contacts.localId": { "$in": [ "c1","c3", null ] },
    }},

    // Group the intermediate result.
    { "$group": {
        "_id": { "email": "$email", "name": "$groups.name" },
        "contacts": { "$push": "$groups.contacts" }
    }},

    // Group the final result
    { "$group": {
        "_id": "$_id.email",
        "groups": { "$push": {
            "name": "$_id.name",
            "contacts": "$contacts" 
        }}
    }}
])

This is "array filtering" on more than a single match which the basic projection capabilities of .find() cannot do.

You have "nested" arrays therefore you need to process $unwind twice. Along with the other operations.