1

i have an array of element while selecting data from mongo using $elemmatch with $or and $all then return all the element where condition doesn't match..

db.user.find( {$and: [{
    roles: { $all: [
        { "$elemMatch" : {$or:[{ effectiveTo: { $gt: new Date()} } , { effectiveTo: null }]}}
    ]}
},
{
    groups: { $all: [
        { "$elemMatch" : {$or:[{ effectiveTo: { $gt: new Date()} } , { effectiveTo: null }]}}
    ]}
}]})
Rahul Gour
  • 487
  • 2
  • 7
  • 21
  • instead of find use remove. check this link https://docs.mongodb.com/manual/reference/method/db.collection.remove/ – Dexter Feb 21 '17 at 05:59
  • this above query returning all element of groups array where effectiveTo date is not greater than current date , those i want to remove.. – Rahul Gour Feb 21 '17 at 06:00
  • @MohitJain , thanks but i dont want to remove elements of my array , need only fetch to show on portal...need to maintain all records.. – Rahul Gour Feb 21 '17 at 06:09
  • Why do you use $all here? You have only one condition in $elemMatch. Couldn't you do just e.g. roles: {$elemMatch: { ... }} and same for groups? – Antonio Narkevich Feb 21 '17 at 06:33
  • Also please edit the question. It's really hard to understand what you want to achieve. Please clean the code and remove unneeded comments. – Antonio Narkevich Feb 21 '17 at 06:38
  • @AntonioNarkevich removed $all from query but currently showing all element of array , i have condition in element i.e. effectiveTo > current date "OR" effectiveTo is null but it showing those element also where effectiveTo < currentdate , please help – Rahul Gour Feb 21 '17 at 06:43
  • Ok. So for example there is a user record with 2 roles. One of them has effectiveTo > current date and the second one has effectiveTo < current date. You want to get this user from db but have only one role in roles array (where effectiveTo is > currentDate), right? Or you want to skip such user at all? – Antonio Narkevich Feb 21 '17 at 06:47
  • @AntonioNarkevich, great as per transaction i stored same , may be user can have multiple role one can activated and other cannot (where effectivTO < currentdate>) i want fetch user also because one role is activated and only activated role should be come in report (roles.effectiveTo > currentdate) , dont want to skip user.. – Rahul Gour Feb 21 '17 at 06:52

1 Answers1

1

What you want to do is basically to filter sub-array in mongodb. There are a lot of similar questions around.

You cannot do it with .find() but you can achieve this with aggregation.

The easiest way would be to use $filter (available starting from MongoDB v. 3.2)

In your case please try this:

var currentDate = new Date();
var effectiveToCondition = {
    $elemMatch: {
        $or: [
            {effectiveTo: {$gt: currentDate}},
            {effectiveTo: null}
        ]
    }
};
db.user.aggregate([
{
    $match: {
        $and: [
            {
                $or: [
                    {roles: {$size: 0}},
                    {roles: effectiveToCondition}
                ]
            },
            {
                $or: [
                    {groups: {$size: 0}},
                    {groups: effectiveToCondition}
                ]
            }
        ]
    }
},
//Will filter out the records where groups AND rolse are both empty
//Please uncomment if needed
// {
//  $match: {
//      $or: [
//          {'groups.0': {$exists: true}},
//          {'roles.0': {$exists: true}}
//      ]
//  }
// },
{
    $project: {
        user: '$$ROOT',
        filteredRoles: {
            $filter: {
                input: '$roles',
                as: 'role',
                cond: {
                    $or: [
                        {$gt: ['$$role.effectiveTo', currentDate]},
                        {$not: {$ifNull: ['$$role.effectiveTo', false]}}
                    ]
                }
            }
        },
        filteredGroups: {
            $filter: {
                input: '$groups',
                as: 'group',
                cond: {
                    $or: [
                        {$gt: ['$$group.effectiveTo', currentDate]},
                        {$not: {$ifNull: ['$$group.effectiveTo', false]}}
                    ]
                }
            }
        }
    }
}]);

You'll have 'user' property with the original document and 2 additional properties: filteredGroups and filteredRoles.

Antonio Narkevich
  • 4,206
  • 18
  • 28
  • it's not working , returning 0 element in filteredGroups and filteredRolesobject while elements is there.. – Rahul Gour Feb 21 '17 at 09:00
  • @RahulGour Could you please provide a document that should work but it doesn't, so I'll be able to test. You could use http://jsbin.com/ – Antonio Narkevich Feb 21 '17 at 09:11
  • too long , unable to add in comments , can u tell me other way or how to add snippet in it – Rahul Gour Feb 21 '17 at 09:28
  • @RahulGour please use anything that is comfortable for you. Either a google doc, dropbox, jsbin.com (or any other service), get a shareable link and send it to me. – Antonio Narkevich Feb 21 '17 at 09:41
  • @RahulGour Now I see the issue. You store dates as Strings. That is why you are not able to compare them using $gt. You will have to update the database and somehow convert all the dates to actual 'Date' objects. Unfortunately there is no other way. Also I updated the answer. Now it handles the cases when effectiveTo is not set. ("effectiveTo === null" and "there is no effectiveTo at all" are different cases) Please try it. – Antonio Narkevich Feb 21 '17 at 10:39
  • condition for no effectiveTo in document will also include in this condition?? var effectiveToCondition = { $elemMatch: { $or: [ {effectiveTo: {$gt: currentDate}}, {effectiveTo: null} ] } }; – Rahul Gour Feb 21 '17 at 10:46
  • but what about date with null value? those are also not coming , ok let me check.. and thanks for your time.. – Rahul Gour Feb 21 '17 at 11:54
  • it is returning only first document of collection , not showing all the document , can you please help me... – Rahul Gour Feb 22 '17 at 04:03
  • http://jsbin.com/zuxuzupoco/edit?html,js above link have 4 document of user collection , can you help me.. – Rahul Gour Feb 22 '17 at 04:06
  • @RahulGour I have updated the answer, please check it out. – Antonio Narkevich Feb 22 '17 at 06:08