Getting only Sub Elements

Question

If I have the following document

{
    "_id" : ObjectId("54986d5531a011bb5fb8e0ee"),
    "owner" : "54948a5d85f7a9527a002917",
    "type" : "group",
    "deleted" : false,
    "participants" : [
        { "_id": "54948a5d85f7a9527a002917", "name": "user1" },
        { "_id": "5491234568f7a9527a002918", "name": "user2" },
        { "_id": "5491234568f7a9527a002918", "name": "user3" },
        { "_id": "1234567aaaa7a9527a002917", "name": "user2" }
    ]
}

How would I get all records where name = 'user2'?

I'm trying the followoing:

db.users.find({ _id: ObjectId('54a7103b1a57eee00bc0a9e4') }, 
{ 'participants.$.name': 'user2') }).pretty()

...and I get the following:

error: {
    "$err" : "Can't canonicalize query: BadValue Positional projection 'participants.$.name' does not match the query document.",
    "code" : 17287
}

BatScream · Accepted Answer · 2015-01-13T21:59:28.393

Though the positional operator($) would give you the first matching element from the participant array. If you need all the participants in with the name user2, you need to aggregate the results.

Match the document with the required _id.
Use the redact operator to only keep all the sub documents that have participants, who have their name as user2.

Code:

var search = "user2";
db.users.aggregate([
{$match:{"_id":ObjectId("54986d5531a011bb5fb8e0ee")}},
{$redact:{$cond:[{$eq:[{$ifNull:["$name",search]},search]},
                 "$$DESCEND",
                 "$$PRUNE"]}},
{$project:{"participants":1,"_id":0}} // set _id:1, if you need the _id.
])

o/p:

{
        "participants" : [
                {
                        "_id" : "5491234568f7a9527a002918",
                        "name" : "user2"
                },
                {
                        "_id" : "1234567aaaa7a9527a002917",
                        "name" : "user2"
                }
        ]
}

Coming to your query,

db.users.find({ _id: ObjectId('54a7103b1a57eee00bc0a9e4') }, 
              { 'participants.$.name': 'user2'}).pretty()

The positional operator can be applied only on the array, that is referred in the query document of the find function. The above query document doesn't have a reference to the array named participants and only refers to the _id field to match a document. Hence you get the error.

From the docs,

The field being limited must appear in the query document

So, changing the query to include the participants array in the query document would fix the error.

  db.users.find({ "_id":ObjectId('54a7103b1a57eee00bc0a9e4'),
                  "participants.name": "user2"
                }, 
                {"participants.$.name":"user2"}).pretty()

But it would return you only the first participant that has matched the criteria in the query document.

From the docs,

Use $ in the projection document of the find() method or the findOne() method when you only need one particular array element in selected documents.

o/p:

{
        "_id" : ObjectId("54986d5531a011bb5fb8e0ee"),
        "participants" : [
                {
                        "_id" : "5491234568f7a9527a002918",
                        "name" : "user2"
                }
        ]
}

Getting only Sub Elements

1 Answers1