Mongodb find match query on array field doesn't work

Question

I have following schema with colletcion & there values associated with it my mongodb. And following is example document of this one.

{
    "_id" : ObjectId("590d46d685a155f33a4a1f1d"),
    "domainName" : "gmail.com",
    "userlevels" : [ 
        {
            "labId" : 104,
            "userlevel" : true
        }, 
        {
            "labId" : 401,
            "userlevel" : false
        }, 
        {
            "labId" : 202,
            "userlevel" : true
        }, 
        {
            "labId" : 102,
            "userlevel" : true
        }, 
        {
            "labId" : 108,
            "userlevel" : true
        }, 
        {
            "labId" : 110,
            "userlevel" : true
        }, 
        {
            "labId" : 120,
            "userlevel" : true
        }, 
        {
            "labId" : 820,
            "userlevel" : false
        }
    ],
    "labconfigs" : [ 
        ObjectId("590d46d685a155f33a4a1f0c"), 
        ObjectId("590d46d685a155f33a4a1f11"), 
        ObjectId("590d46d685a155f33a4a1f10"), 
        ObjectId("590d46d685a155f33a4a1f0d"), 
        ObjectId("590d46d685a155f33a4a1f12"), 
        ObjectId("590d46d685a155f33a4a1f0f"), 
        ObjectId("590d46d685a155f33a4a1f0e"), 
        ObjectId("591087b0b18c5f472905343a")
    ]
}

So what we want is all documents matching within particular range of labID, with only fields which are matching in array ("userlevels") to be in result output.

Say if we want all matching documents in collection with range between $gt 100 to lt 200 , then following desired output should come.

{
    "_id" : ObjectId("590d46d685a155f33a4a1f1d"),
    "domainName" : "gmail.com",
    "userlevels" : [ 
        {
            "labId" : 104,
            "userlevel" : true
        },
        {
            "labId" : 102,
            "userlevel" : true
        }, 
        {
            "labId" : 108,
            "userlevel" : true
        }, 
        {
            "labId" : 110,
            "userlevel" : true
        }, 
        {
            "labId" : 120,
            "userlevel" : true
        }
    ]
}

What i have tried & this is my mongodb query.

            db.collection('domains').find({
                                           "userlevels":{ $all :[
                                               { "$elemMatch" : { "labId" :{$gte : 100, $lt:200 }}}
                                               ]}
                                          }).toArray(function(err, items) {
              console.log("items",items);
                                 });

It replies with all documents matching documents within range of lab_id's (100 to 200) , but it replies in userlevels with lab_id's.

s7vr · Accepted Answer · 2017-05-08 21:29:00Z

1

You will have to use aggregation pipeline.

Initial $match to limit the documents that have at least one userlevels array element with matching labId criteria followed by $filter with cond to limit the elements in the array to the matching labId criteria in each of the matching document.

$project to include(1) domainName and overwrite the userlevels with filtered value.

aggregate({
    $match: {
        "userlevels.labId": {
            $gt: 100,
            $lt: 200
        }
    }
}, {
    $project: {
        domainName: 1,
        userlevels: {
            $filter: {
                input: "$userlevels",
                as: "result",
                cond: {
                    $and: [{
                        $gt: ["$$result.labId", 100]
                    }, {
                        $lt: ["$$result.labId", 200]
                    }]
                }
            }
        }
    }
})

edited May 8, 2017 at 21:29

answered May 8, 2017 at 21:00

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

7 Comments

Inder R Singh Over a year ago

1. What does domainName: 1 means ? . 2. Also in projection $filter, can we add new cond to match elements with 'userauth':true along with $gt & $lt

s7vr Over a year ago

Np. Updated answer to add more explanation. Yes, you can $and userlevel:true in the cond

s7vr Over a year ago

Something like

{                     $and: [{                         $gt: ["$$result.labId", 100]                     }, {                         $lt: ["$$result.labId", 200]                     }, {$eq:["$$result.userlevel", true]}]

Inder R Singh Over a year ago

Would this query work on Mongo 3.0 ? i know it is not working on 2.6.12 & it is working on 3.4.

s7vr Over a year ago

No, it will work from 3.2 & above. For 3.0 & below versions, you have to replace filter part with set operator. More here stackoverflow.com/questions/37605431/…

|

Mihir Bhende · Accepted Answer · 2017-05-08 20:55:46Z

0

Here you go :

db.domains.aggregate([
    { $unwind : '$userlevels'},
    { $match : { 'userlevels.labId' : { $gt: 100, $lt: 200 } }},
    { $group : {
        '_id' : '$_id',
        'domainName': { '$first' : '$domainName'},
        "userlevels": { "$push": "$userlevels" }
    }}
]);

edited May 8, 2017 at 20:55

answered May 8, 2017 at 20:48

Mihir Bhende

9,0952 gold badges33 silver badges39 bronze badges

1 Comment

Inder R Singh Over a year ago

How can we do it in Mongo 3.0 ?

Inder R Singh · Accepted Answer · 2017-06-09 22:39:40Z

For Mongodb version 3.0 & below use this query which worked for me.

    db.collection('domains').aggregate({
            $match: {
                "userlevels.labId": {
                    $gt: 100,
                    $lt: 200
                }
            }
        }, {
            $project: {
                domainName: 1,
                userlevels: {
                    "$setDifference": [{
                        $map: {
                        input: "$userlevels",
                        as: "result",
                        in: {
                            $cond: [{
                                $and: [{
                                    $gt: ["$$result.labId", 100]
                                }, {
                                    $lt: ["$$result.labId", 200]
                                }, {
                                    $eq: ["$$result.userlevel", true]
                                }]
                            },
                                "$$result",
                                false
                            ]
                        }
                    }},
                   [false]
                  ]
                }
            }
        }

     })

Collectives™ on Stack Overflow

Mongodb find match query on array field doesn't work

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related