MongoDB projection query, filter field in matching element of inner array

Question

I have a document that contains an array. Like so:

"_id" : ObjectId("55101f81e4b07caf8554b9b1"),
"myId" : "1222222",
"isDelayed" : false,
"status" : "BALLS",
"yellow" : false,
"white" : true,
"people" : [ 
    {
        "peopleId" : 222222,
        "bc" : 0,
        "status" : "live",
        "fc" : 1,
        "tc": 4,
        "rc": "yellow" 

    }, 
    {
        "peopleId" : 33312,
        "bc" : 0,
        "status" : "live",
        "fc" : 1,
        "tc": 4,
        "rc": "yellow" 

    }, 
  ...

I have a mongo query like below, in the collection mycoll, if myId=1.222 and in the people array if people.peopleId=1123 it returns the first match:

db.getCollection('mycoll').find(
    {myId:'1.222',
        people: { $elemMatch: { peopleId: 1123 }
                 }
    },{"people.$": 1 }).pretty();

The result includes all fields in the people entry from the array:

"people" : [ 
        {
            "peopleId" : 1122,
            "bc" : 0,
            "status" : "live",
            "fc" : 1,
            "tc": 4,
            "rc": "yellow" 

        },

How do I filter the reply that it only returns the desired field from the matching entry in the inner array, say "status"? I can generate a filter for the outer document but not a field in an array element.

Juan Carlos Farah · Accepted Answer · 2015-04-09 01:17:43Z

5

You can do this using aggregation. What you need to do is:

Match the documents with the desired myId.
Unwind the people arrays so that there is one document per subdocument inside people.
Match the specified peopleId.
Project fields as desired.

db.mycoll.aggregate([
    { "$match": { "myId": "1.222"} },
    { "$unwind": "$people" },
    { "$match": { "people.peopleId": 1123 }},
    { "$project": { "peopleId": "$people.peopleId",
                    "status": "$people.status" }
    }
]).pretty();

edited Apr 9, 2015 at 1:17

answered Apr 9, 2015 at 0:38

Juan Carlos Farah

3,90934 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neo-coder · Accepted Answer · 2015-04-09 12:44:18Z

1

Mongo $elemMatch in projection used in projection to find out specific field in your case status below query return only status field

db.collectionName.find({"myId":"1222222"},{"people":{"$elemMatch":{"peopleId":33312}},"people.status":1}).pretty()

edited Apr 9, 2015 at 12:44

answered Apr 9, 2015 at 9:09

Neo-coder

7,8585 gold badges36 silver badges54 bronze badges

3 Comments

user1843591 Over a year ago

Hi @yogesh, yes but this will return 'status' for all elements of the array instead of just the matching one

Neo-coder Over a year ago

@user1843591 I think you should misplaced some thing, this shows me exact matching result status result as below {"_id" : ObjectId("55101f81e4b07caf8554b9b1"),"people" : [{"status" : "live"}]}

user1843591 Over a year ago

you're right, my apologies, it 'looked' like my attempt at it but i didn't actually try it out. thank you for your reply, I'm going to mark the answer by @juancarlos as correct as his solution will return the exact matches (all criteria) whereas this solution will return an 'empty' result for all entries that match "myId":"1222222" even if $elemMatch does not return anything.

Collectives™ on Stack Overflow

MongoDB projection query, filter field in matching element of inner array

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related