Mongo db Query to filter nested array of objects in document

Question

I have the following document

{
    "userid": "5a88389c9108bf1c48a1a6a7",
    "email": "[email protected]",
    "lastName": "abc",
    "firstName": "xyz",
    "__v": 0,
    "friends": [{
        "userid": "5a88398b9108bf1c48a1a6a9",
        "ftype": "SR",
        "status": "ACCEPT",
        "_id": ObjectId("5a9585b401ef0033cc8850c7")
    },
    {
        "userid": "5a88398b9108bf1c48a1a6a91111",
        "ftype": "SR",
        "status": "ACCEPT",
        "_id": ObjectId("5a9585b401ef0033cc8850c71111")
    },
    {
        "userid": "5a8ae0a20df6c13dd81256e0",
        "ftype": "SR",
        "status": "pending",
        "_id": ObjectId("5a9641fbbc9ef809b0f7cb4e")
    }]
},
{
    "userid": "5a88398b9108bf1c48a1a6a9",
    "friends": [{ }],
    "lastName": "123",
    "firstName": "xyz",
    .......
},
{
    "userid": "5a88398b9108bf1c48a1a6a91111",
    "friends": [{ }],
    "lastName": "456",
    "firstName": "xyz",
    ...
}

First Query

Here I want to get userId from friends array ,which having status equals to "ACCEPT". ie

 [5a88398b9108bf1c48a1a6a9,5a88398b9108bf1c48a1a6a91111]

Second Query

After that, I have to make another query on the same collection to get details of each userid returned in the first query. final Query will return details of [5a88398b9108bf1c48a1a6a9,5a88398b9108bf1c48a1a6a91111] both userid ie

[
        {
         userid" : "5a88398b9108bf1c48a1a6a9",
         "lastName" : "123",
         "firstName" : "xyz"
         },
       {
         "userid" : "5a88398b9108bf1c48a1a6a91111",
          "lastName" : "456",
           "firstName" : "xyz"
       }
   ]

I have tried so far with

 Users.find ({'_id':5a88389c9108bf1c48a1a6a7,"friends.status":'ACCEPT'}, (error, users) => {})  
   or 


 Users.find ({'_id':5a88389c9108bf1c48a1a6a7, friends: { $elemMatch: { status: 'ACCEPT' } } }, (error, users) => {})

AlejandroMontilla : I have tried with follwoing mongooes Query to get data from collection Users.find ({'userid':5a88389c9108bf1c48a1a6a7, friends: { $elemMatch: { status: 'ACCEPT' } } }, (error, users) => { }); — Anurag G
– Anurag G, Commented Feb 28, 2018 at 14:45
please add that find to your question. And that find recover the spected document? — Alejandro Montilla
– Alejandro Montilla, Commented Feb 28, 2018 at 14:58
You are missing the ObjectId("5a88389c9108bf1c48a1a6a7") => Users.find({ _id:ObjectId("5a88389c9108bf1c48a1a6a7" )}). Also take a look at $redact — Alejandro Montilla
– Alejandro Montilla, Commented Feb 28, 2018 at 15:40
Can you please edit your question to show us your expected final output? — chridam
– chridam, Commented Mar 2, 2018 at 13:31
@chridam : Thank you so much for effort on the first query. It is working fine for me. I want details of all users(ie firstQuery resultant) from the same document. Hope you can understand my query, Thanks :) — Anurag G
– Anurag G, Commented Mar 2, 2018 at 13:48

chridam · Accepted Answer · 2018-03-02 13:56:01Z

5

Use the aggregation framework's $map and $filter operators to handle the task. $filter will filter the friends array based on the specified condition that the status should equal "ACCESS" and $map will transform the results from the filtered array to the desired format.

For the second query, append a $lookup pipeline step which does a self-join on the users collection to retrieve the documents which match the ids from the previous pipeline.

Running the following aggregate operation will produce the desired array:

User.aggregate([
    { "$match": { "friends.status": "ACCEPT" } },
    { "$project": {
            "users": {
                "$map": {
                    "input": {
                        "$filter": {
                            "input": "$friends",
                            "as": "el",
                            "cond": { "$eq": ["$$el.status", "ACCEPT"] }
                        }
                    },
                    "as": "item",
                    "in": "$$item.userid"
                }
            }
    } },
    { "$lookup": {  
        "from": "users",
        "as": "users",
        "localField": "users",
        "foreignField": "userid"
    } },
]).exec((err, results) => {
    if (err) throw err;
    console.log(results[0].users); 
});

edited Mar 2, 2018 at 13:56

answered Feb 28, 2018 at 22:10

chridam

104k26 gold badges246 silver badges243 bronze badges

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

2 Comments

Anurag G Over a year ago

Thank you @chridam, it is working fine for the First query

chridam Over a year ago

@AnuragGautam The second query requires a $lookup pipeline, please check the above edit.

Krishna · Accepted Answer · 2018-02-28 16:45:40Z

2

I did not test it. just for an idea, give it a try and let me know.

 db.Users.aggregate(
       [
        {
           $unwind: "$friends"
        },
        {
          $match:{ "$friends.status": "ACCEPT"}
        },
        {
        $project:{ "FriendUserID":"$friends.userid"}
        },
        { 
          $lookup:{  
             from:"Users",
             as: "FriendsUsers",
             localField: "FriendUserID",
             foreignField: "userid"
          }
        },
        {
          $project: { FriendsUsers.lastName:1,FriendsUsers.firstName:1 }
        }
       ]
    )

answered Feb 28, 2018 at 16:45

Krishna

5493 silver badges14 bronze badges

1 Comment

Anurag G Over a year ago

Thanks @krishan for sharing your views but unfortunately it is not working

Rafiq · Accepted Answer · 2022-01-11 07:01:54Z

filtering nested elements

const products = await Product.aggregate<ProductDoc>([
    {
      $match: {
        userId: data.id,
      },
    },
    {
      $project: {
        promotions: {
          $filter: {
            input: '$promotions',
            as: 'p',
            cond: {
              $eq: ['$$p.status', PromotionStatus.Started],
            },
          },
        },
        userId: 1,
        name: 1,
        thumbnail: 1,
      },
    },
  ]);

for multiple condition

   cond: {
            $and: [
              {
                $eq: [
                  "$$c.product",
                  "37sd87hjsdj3"
                ]
              },
              {
                $eq: [
                  "$$c.date",
                  "date-jan-4-2022"
                ],
                
              }
            ]
          },

Collectives™ on Stack Overflow

Mongo db Query to filter nested array of objects in document

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related