Mongo Db $in query with multiple keys

Question

I have a Mongo db collection of documents which contains _id , version and many other fields. I have an array of id's like :

["1A", "2b", "3C", "4D"]

If I want all the documents that contain any of the above id's, i could query like :

{
  _id : {
         $in: ["1A", "2b", "3C", "4D"]
        }
}

I'd like to know if we can query with another condition in addition to the _id i.e the above array with condition, something like below :

{
      $in: [{"id":"1A","version":"1.1"},{"id":"2B","version":"2.1"},{"id":"3C","version":"3.1"},{"id":"4D","version":"4.1"}]
        }
}

Example for {"id":"1A","version":"1.1"} : I want all the documents that has id as 1A and version as 1.1. Similarly for all the objects in the array.

What is your question? Please rewrite question with better example. — Saleem
– Saleem, Commented Nov 23, 2016 at 13:52
@Saleem Sorry for the missing clarity. I've edited the question. Hope it is understandable now. — Supradeep
– Supradeep, Commented Nov 23, 2016 at 14:15

Ankur Verma · Accepted Answer · 2016-11-24 07:40:51Z

4

You can try something like this:

 db.collection.find(
   {
     //this OR will make sure that you get at any matching combination
     $or:[
       {"<FIELD1>": "<VALUE1.1>", "<FIELD2>": "<VALUE1.2>"},
       {"<FIELD1>": "<VALUE2.1>", "<FIELD2>": "<VALUE2.2>"}
     ]
    }
 )

The documentation of above $and is available here.

Also there are other operators as well, like $or, $not, here

edited Nov 24, 2016 at 7:40

answered Nov 24, 2016 at 7:09

Ankur Verma

5,93316 gold badges62 silver badges95 bronze badges

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

3 Comments

Supradeep Over a year ago

Thanks for the answer. This will look for ids and versions seperately and "and" them. My problem is , each id i.e your "FIELD1" has its specific corresponding version i.e your "FIELD2".

Supradeep Over a year ago

Thanks for that. That is one solution to make a single query for all the combinations. I can loop through my array and build this query, but my question is if there is a possibility to do that without "AND"ing each combination i.e have all the combinations in a single query.

Ankur Verma Over a year ago

Updated the answer and I am 99% sure you will accept this. Just kidding. ;)

s7vr · Accepted Answer · 2016-11-23 18:00:40Z

2

Showing example with two values. You can add as many you need. Make use of $or and $and operators for multi keys.

find({
    "$or": [{
        "$and": [{
            "_id": "1A"
        }, {
            "version": "1.1"
        }]
    },{
        "$and": [{
            "_id": "2B"
        }, {
            "version": "2.1"
        }]
    }]
})

Update to include the search in the object arrays.

Sample Collection:

db.multikeys.insertMany([{
    _id: 1,
    tags: [{
        _id: "1A",
        version: "1.1"
    }, {
        _id: "2B",
        version: "1.4"
    }]
}, {
    _id: 2,
    tags: [{
        _id: "2B",
        version: "2.1"
    }, {
        _id: "3C",
        version: "3.1"
    }]
}])

Query:

db.multikeys.find({
    tags: {
        $elemMatch: {
            $or: [{
                _id: "2B",
                version: "2.1"
            }, {
                _id: "1A",
                version: "1.1"
            }]
        }
    }
}).pretty()

Sample Output:

{
    "_id": 1,
    "tags": [{
        "_id": "1A",
        "version": "1.1"
    }, {
        "_id": "2B",
        "version": "1.4"
    }]
} {
    "_id": 2,
    "tags": [{
        "_id": "2B",
        "version": "2.1"
    }, {
        "_id": "3C",
        "version": "3.1"
    }]
}

edited Nov 23, 2016 at 18:00

answered Nov 23, 2016 at 14:16

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

4 Comments

Rahul Over a year ago

This won't work properly when filter is happening against a array of item. Give a try and see for yourself

s7vr Over a year ago

I know. but OP didnt mention anything about searching in the array. I'll wait for OP to confirm and change it accordingly.

Supradeep Over a year ago

This doesn't work. I have an array of objects like mentioned in the question in which each object has an id and version. I want to check if there are any documents that satisfies both of those values in each object. I want the document only if that id matches with the corresponding version. This is same like $in except i have two values to check instead of one.

s7vr Over a year ago

Please take a look now. Updated based on my understanding. Hope it helps. you can use $in operator too instead of $or I think.

Rahul · Accepted Answer · 2016-11-23 14:25:09Z

1

You need to use $elemMatch to match both the criteria like

db.collection1.find(
   { arrayObjectKeyName: { $elemMatch: { id: "1A", version: "1.1" } } }
)

Sure, here is an example of such

db.collection1.find({ "arrayObjectKeyName": {"$elemMatch":{"id": "1A","version":{"$in":["1.1","1.3","1.9876"]}  }} })

edited Nov 23, 2016 at 14:25

answered Nov 23, 2016 at 13:59

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

4 Comments

Supradeep Over a year ago

Thanks for the response. But to do this, i have to query with each "id" seperately. Instead I want to be able to do a single query like i did "$in" for array of id's.

Rahul Over a year ago

@suzo, No, you can as well use the $in operator in $elemMatch

Supradeep Over a year ago

Exactly, i was just wondering how we can do that. Can you please help me on that?

Supradeep Over a year ago

Thanks for the edit but it doesn't work. Please see the above comment in Veeram's answer

Collectives™ on Stack Overflow

Mongo Db $in query with multiple keys

3 Answers 3

3 Comments

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related