How to fetch document with filtered nested document using Mongo query?

Question

Here is my organization collection.

[{
    "_id" : ObjectId("5fd5fc1b9f117029b5233b2e"),
    "name" : "ClassA",
    "orgMembers" : [ 
        {
            "_id" : ObjectId("5fd5fc1b9f117029b5233b2f"),
            "userId" : "Ben",
        },
        {
            "_id" : ObjectId("5fd5fc1b9f117029b5233b2f"),
            "userId" : "Anton",
        }
    ],
},
{
    "_id" : ObjectId("5fd5fc1b9f117029b5233b2e"),
    "name" : "ClassA",
    "orgMembers" : [ 
        {
            "_id" : ObjectId("5fd5fc1b9f117029b5233b2f"),
            "userId" : "Ben",
        }
    ],
}]

Each document has properties like _id, name, orgMembers which represent document information. And orgMembers is the Array of Members (_id, userId) who belongs to organization.

In this collection, I want to fetch the organizations which includes orgMember with Anton as userId and as well orgMembers of fetched organization document should only contain Anton as a orgMember.

Expected Result is likewise

[{
    "_id" : ObjectId("5fd5fc1b9f117029b5233b2e"),
    "name" : "ClassA",
    "orgMembers" : [
        {
            "_id" : ObjectId("5fd5fc1b9f117029b5233b2f"),
            "userId" : "Anton",
        }
    ],
}]

Here ClassA organization has two orgMembers but need to be filtered matching with userId.

I have tried with

documentModel.find({ 'orgMembers.userId': 'Anton' })

But within this query, I get the result like this.

[{
    "_id" : ObjectId("5fd5fc1b9f117029b5233b2e"),
    "name" : "ClassA",
    "orgMembers" : [ 
        // should be omitted 
        {
            "_id" : ObjectId("5fd5fc1b9f117029b5233b2f"),
            "userId" : "Ben",
        },

        // should be contained
        {
            "_id" : ObjectId("5fd5fc1b9f117029b5233b2f"),
            "userId" : "Anton",
        }
    ],
}]

For expected result, orgMember with userId: Ben should be omitted.

How can I get my expected result with mongo query?

Kevin Li · Accepted Answer · 2020-12-18 03:38:55Z

1

I believe this will be worked on your side

db.collection.find({
  "orgMembers.userId": "Anton"
},
{
  orgMembers: {
    "$elemMatch": {
      userId: "Anton"
    }
  }
})

edited Dec 18, 2020 at 3:38

answered Dec 18, 2020 at 3:21

Kevin Li

1506 bronze badges

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

3 Comments

Josh Thomas Over a year ago

Thank you for your answer. I've tried your solution. mongoplayground.net/p/0Kwiku7o_Av But I didn't get the expected result.

Kevin Li Over a year ago

@AdelinIonut There was a little bit confusing for your question. I've updated the query now.

Josh Thomas Over a year ago

hi @KevinLi, you query doesn't work fine for me. If I add another property to orgMember, for example { 'userId': 'Anton', 'gender': 'man' }, it should fetch all properties but in your query, it only fetch { 'userId': 'Anton' }. And as well, I need to get all other properties for that organization but result excludes other properties of the organization. Can you take a look again the mongoplayground?

abhilash reddy · Accepted Answer · 2020-12-18 11:40:15Z

0

not sure if i quite got your requirement:

but try this if it works

db.collection.find({
  "orgMembers.userId": {
    $regex: "Anton",
    $options: "i"
  }
},
{
  name: 1,
  "orgMembers.$": 1
})

this is to return only the userId you are looking for in orgMembers.if there are more orgmembers they will not be part of output

answered Dec 18, 2020 at 11:40

abhilash reddy

11 bronze badge

Collectives™ on Stack Overflow

How to fetch document with filtered nested document using Mongo query?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related