find_one() return one document give a combination of fields

Question

I am trying to return a document from my mongodb (using pymongo). I want the query to return a document given an id and a tag.

ids = ['123', '456', '234', '534']

rows = []
for i in ids:
    for b in ["Tag1", "Tag2", "Tag3"]:
        temp = pb_db.db.collection.find_one({"ID": i, "Tag": b}, {'ID': 1, 'Tag': 1, 'Name': 1, '_created_at': 1})
        if temp is not None:
            rows.append(temp)

A document with an ID of '123' may have one record with 'Tag1' and a separate document with 'Tag3'. Any combination of 'ID' and 'Tag' is possible.

The goal is to return one instance of each id, tag combination (hence using find_one())

At the moment my code above is very inefficient as it queries the db for every id three times (my list of ids is much larger than this example). Is it possible to use the find_one() query to return a document for a given id with each tag only once? Thanks,

example mongo structure:

{
    "_id" : "random_mongo_id",
    "Tag" : "Tag1",
    "_created_at" : ISODate("2016-06-25T00:00:00.000Z"),
    "ID" : [ 
        "123"
    ],
},
{
    "_id" : "random_mongo_id",
    "Tag" : "Tag2",
    "_created_at" : ISODate("2016-07-25T00:00:00.000Z"),
    "ID" : [ 
        "123"
    ],
},
{
    "_id" : "random_mongo_id",
    "Tag" : "Tag1",
    "_created_at" : ISODate("2016-07-25T00:00:00.000Z"),
    "ID" : [ 
        "534"
    ],
}

so in this example i would expect to see:

ID: 123, Tag: Tag1
ID: 123, Tag: Tag2
ID: 534, Tag: Tag1

Answer 1

You need to use $in and the $elemMatch query operator.

ids = ['123', '456', '234', '534']
tags = ["Tag1", "Tag2", "Tag3"]

db.collection.find_one({
    "Tag": { "$in": tags}, 
    "ID": { "$elemMatch": { "$in": ids}}
})

Answer 2

You could do it in a single pass by using the $in operator to compare the items in the "ID" array in the database with the items in the "ids" array variable, and similarly for the tags.

Use the $in Operator to Match Values in an Array

The collection inventory contains documents that include the field tags, as in the following:

{ _id: 1, item: "abc", qty: 10, tags: [ "school", "clothing" ], sale: false }

Then, the following update() operation will set the sale field value to true where the tags field holds an array with at least one element matching either "appliances" or "school".

db.inventory.update(
  { tags: { $in: ["appliances", "school"] } },
  { $set: { sale:true } }
)

In user3939059's case, the query would be something like this:

ids = ['123', '456', '234', '534']
tags = ['Tag1', 'Tag2', 'Tag3']

pb_db.db.collection.find({"ID": {$in: ids}, "Tag": {$in: tags}}, {'ID': 1, 'Tag': 1, 'Name': 1, '_created_at': 1})