MongoDB $in query multiple predicates-CodePudding

I have a table as follows:

------------------------------------------------------
|   row_num | person_id   | org_id   | other columns |
|-----------|-------------|----------|---------------|
|         0 | person_0    | org_0    |       .       |       
|         1 | person_1    | org_0    |       .       |
|         2 | person_2    | org_0    |       .       |
|         3 | person_3    | org_0    |       .       |
------------------------------------------------------      
|         3 | person_0    | org_1    |       .       |
|         4 | person_1    | org_1    |       .       |
|         5 | person_2    | org_1    |       .       |
|         6 | person_3    | org_1    |       .       |
------------------------------------------------------
|         6 | person_0    | org_2    |       .       |
|         7 | person_1    | org_2    |       .       |
|         8 | person_2    | org_2    |       .       |
|         9 | person_3    | org_2    |       .       |
------------------------------------------------------

The primary key is (person_id, org_id). This combination is guaranteed to be unique.

Let us say, I have lists person_ids and corresponding org_ids for certain persons and I want to fetch their records from the collection.

persons = [("person_0", "org_0"), ("person_1", "org_1"), ("person_3", "org_1")]
person_ids, org_ids = zip(*persons)

In this case the expected output is columns from rows 0, 4, 6.

I can always find the answer by finding the intersection of the following two queries, but I was wondering if there is any smarter way to do this:

db.collection.find({person_id: {$in: person_ids})

db.collection.find({org_id: {$in: org_ids})

CodePudding user response：

If you need it by OR operator you can do it by this command :

db.collection.find({
    $or:[
        {person_id: { $in: person_ids } },
        {org_id: { $in: org_ids  } }
    ]
})

If you need it by AND operator you can do it by this command :

db.collection.find({
    person_id: { $in: person_ids },
    org_id: { $in: org_ids  } 
})

CodePudding user response：

You can find the answer in one query this way

db.collection.find({
  person_id: { $in: person_ids },
  org_id: { $in: org_ids }
})

Here's simple demo: https://mongoplayground.net/p/TwYxZRDFVBI