CODEWITHSUNDEEP
mongodbmongodb-query
salgado
salgado

Reputation: 48

Is there a way to filter embedded arrays in mongodb?

I have a collection like this

[
{
    "_id" : {
        "id" : 1
    },
    "group" : [ 
        {
            "id" : 1,
            "users" : [ 
                {
                    "id" : 3000,
                    "active" : true,
                }, 
                {
                    "id" : 3001,
                    "active" : false,
                }
            ]
        },
    ]
},
{
    "_id" : {
        "id" : 2
    },
    "group" : [ 
        {
            "id" : 2,
            "users" : [ 
                {
                    "id" : 4000,
                    "active" : true,
                }, 
                {
                    "id" : 4001,
                    "active" : false,
                }
            ]
        },
    ]
}
]

I would like to project all collection, but just the users with active equals to true. Like this:

[
{
    "_id" : {
        "id" : 1
    },
    "group" : [ 
        {
            "id" : 1,
            "users" : [ 
                {
                    "id" : 3000,
                    "active" : true,
                }, 
            ]
        },
    ]
},
{
    "_id" : {
        "id" : 2
    },
    "group" : [ 
        {
            "id" : 2,
            "users" : [ 
                {
                    "id" : 4000,
                    "active" : true,
                }, 
            ]
        },
    ]
}
]

I tried to use this to bring the result, but doesn't work.

{
        $project: {

            '_id': 1,
            'groups': {
                 $filter: {
                    input: '$group',
                    as: 'g',
                    cond: {
                        $and: [
                            { '$eq': ['$$g.users.active', true] }
                        ],
                    },
                },
            }
        }
}

If i just use { '$eq': ['$$g.id', 1] }, it works but when i try to use the third level, this doesn't work.

Is there some way to access the third level and make a filter with the values? I searched in mongodb documentation but i didn't find anything.

Does anyone have any solution?

Upvotes: 2

Views: 113

Answers (3)

ambianBeing
ambianBeing

Reputation: 3529

Adding another way to achieve the target O/P.

The idea here is first $unwind the outer array field group and so to feed group.users at $filter to chunk out active users. Then $group back again using the _id and modifying the structure of group using $push

db.collection.aggregate([
  {
    $unwind: "$group"
  },
  {
    $addFields: {
      filteredUsers: {
        $filter: {
          input: "$group.users",
          as: "u",
          cond: {
            $eq: ["$$u.active", true]
          }
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id",
      group: {
        $push: {
          id: "$group.id",
          users: "$filteredUsers"
        }
      }
    }
  }
]);

Upvotes: 1

Buzz Moschetti
Buzz Moschetti

Reputation: 7588

I believe this pipeline will produce the desired output:

db.foo.aggregate([
{$addFields: {group: {$map: {   // "overwrite" group with addFields
                input: "$group",
                as: "z",
                in: {
                    id: "$$z.id",   // just copy the inbound _id 
                    users: {$filter: {
                            input: "$$z.users",
                            as: "zz",
                            cond: {$eq: [ "$$zz.active", true] }
                        }}
                }

            }}
    }}
                      ]);

Upvotes: 3

Tomasz Kasperczyk
Tomasz Kasperczyk

Reputation: 2093

You have to use $map for every nested level. In addition, you need $mergeObjects to extract all the properties belonging to group that are not filtered.

[{ 
    $project: { 
        '_id': 1,
        'group': { 
            $map: { 
                'input': '$group',
                'as': 'g',
                'in': {
                    $mergeObjects: [
                        '$$g', 
                        {
                            'users': {
                                $filter: { 
                                    "input": "$$g.users",
                                    "as": "u",
                                    "cond": { 
                                        $and: [{'$eq': ['$$u.active', true]}]
                                    }
                                }
                            }
                        }
                    ]
                }
            }
        }
    }
}]

The proposed aggregation returns everything you need and applies the requested filter. Thanks to $mergeObjects I was able to extract not only the group.users array, but also everything else that's inside the group array (group._id).

Upvotes: 3

Related Questions