Comparing an element's field in array with a field in MongoDB

Question

I have a collection Group like this:

{
    "_id" : ObjectId("5822dd5cb6a69ca404e0d93c"),
    "name" : "GROUP 1",
    "member": [
        {
            "_id": ObjectId("5822dd5cb6a69ca404e0d93d")
            "user": ObjectId("573ac820eb3ed3ea156905f6"),
            "task": ObjectId("5822ddecb6a69ca404e0d942"),
        },
        {
            "_id": ObjectId("5822dd5cb6a69ca404e0d93f")
            "user": ObjectId("57762fce5ece6a5d04457bf9"),
            "task": ObjectId("5822ddecb6a69ca404e0d943"),
        }
    ],
    curTask: {
        "_id": ObjectId("5822ddecb6a69ca404e0d942"),
        "time": ISODate("2016-01-01T01:01:01.000Z")
    }
}
{
    "_id" : ObjectId("573d5ff8d1b7b3b32e165599"),
    "name" : "GROUP 2",
    "member": [
        {
            "_id": ObjectId("574802e031e70b503eabe195")
            "user": ObjectId("573ac820eb3ed3ea156905f6"),
            "task": ObjectId("5775f1a74b41037e246a51d1"),
        },
        {
            "_id": ObjectId("574802e031e70b503eabe198")
            "user": ObjectId("573ac79beb3ed3ea156905f4"),
            "task": ObjectId("576cfa042c0a4054794dd242"),
        }
    ],
    curTask: {
        "_id": ObjectId("577249a2f9dba0c750ef705b"),
        "time": ISODate("2016-01-01T01:01:01.000Z")
    }
}
{
    "_id" : ObjectId("574802e031e70b503eabe194"),
    "name" : "GROUP 3",
    "member": [
        {
            "_id": ObjectId("574be0a2bf16234f5a752f83")
            "user": ObjectId("573ac79beb3ed3ea156905f4"),
            "task": ObjectId("5822ddecb6a69ca404e0d942"),
        },
        {
            "_id": ObjectId("574d397d6e9f07d64d1e4e40")
            "user": ObjectId("57762fce5ece6a5d04457bf9"),
            "task": ObjectId("5822ddecb6a69ca404e0d943"),
        }
    ],
    curTask: {
        "_id": ObjectId("5822ddecb6a69ca404e0d942"),
        "time": ISODate("2016-01-01T01:01:01.000Z")
    }
}

And I want to be able to find all group where user with objectId 573ac820eb3ed3ea156905f6 (1st user in group 1) do not do the same task as currentTask. So far I've wrote this query:

db.getCollection('groups').find({"member":{ "$elemMatch": {"user": ObjectId("573ac820eb3ed3ea156905f6")
, "task": { "$ne":"this.curTask._id"}}}})

But this didn't seem to work as it still return the group where user 573ac820eb3ed3ea156905f6 having his task === curTask._id. The first half of elemMatch seem to work fine (only find group with user with objectid 573ac820eb3ed3ea156905f6 in member, the query only return group 1 and 2 since group 3 don't have that user.) but I cant seem to make mongodb compare a field in the object of the array with another field of the document. Anyone have any idea how do I make this comparison?

Not getting a clue about how we can use this inside element matcher — Niranjan Kumar
– Niranjan Kumar, Commented Nov 14, 2016 at 5:26
Do you care about the tasks of other members in the array which has matched the 1st condition. — hyades
– hyades, Commented Nov 14, 2016 at 5:41

hyades · Accepted Answer · 2016-11-14 06:03:57Z

1

There are two solutions to the problem -

First - Using $where. By using $where you can use Javascript code inside mongodb queries. Makes the code flexible, but the shortcoming is that it runs slow since Javascript code has to run rather than more optimized mongoDB C++ code.

db.getCollection('groups').find({
    $where: function () {
            var flag = 0;

            for(var i=0; i<obj.member.length;i++) {
                    if(obj.member[i].user.str == ObjectId("573ac820eb3ed3ea156905f6").str && obj.member[i].task.str != obj.curTask._id.str ){flag = 1; break;}
                }
            return flag;
        }
    })

Second - Using an aggregation pipeline. Here I am unwinding the array, doing matches as described, and finally recreating the array as it was needed. If the not matching elements in the member array are not needed, one can omit the last grouping part.

[
{$match: {'member.user': ObjectId("573ac820eb3ed3ea156905f6")}},
{$unwind: '$member'},
{$project: {
    name: 1,
    member: 1,
    curTask: 1,
    ne: {$and: [{$ne: ['$member.task', '$curTask._id']}, {$eq: ['$member.user', ObjectId("573ac820eb3ed3ea156905f6")]}]}
    }},
{$group: {
    _id: '$_id',
    member: {$push: '$member'},
    curTask: {$first: '$curTask'},
    name: {$first: '$name'},
    check: {$sum: {$cond: ['$ne', 1, 0]}}
    }},
{$match: {check: {$gt: 0}}}
]

answered Nov 14, 2016 at 6:03

hyades

3,2101 gold badge22 silver badges36 bronze badges

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

1 Comment

Megazero Over a year ago

Thank you so much for the answer. I didnt even know that you can use a js code in the query. The 1st way work perfectly for me (I just need to change .str to + '' so it will also cover the case when the user dont have any task assign to him yet so his task is null).

Collectives™ on Stack Overflow

Comparing an element's field in array with a field in MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related