Find documents matching multiple fields in an object array in MongoDB

Question

Let's say I have an array of objects (let us call that array A) and I need a query to find a collection in MongoDB for all documents matching one of it's fields to one of the properties of object 1 in array A and another field to some other property in the same object in array A.

The documents do not have all the properties that the objects in array A have.

To make things clear...

Array A would look something like this...

[{
    id_bus:1,
    id_bus_variation:13,
    ....
},{
    id_bus:2,
    id_bus_variation:184,
    ....
},{
    id_bus:3,
    id_bus_variation:13,
    ....
}]

The documents in my database include those two properties and I need to match those two at the same time. For example, I need to find in my database the docs that have id_bus == 1 and id_bus_variation == 13, and also the ones that have id_bus == 2 and id_bus_variation == 184 but not the ones that id_bus == 4 and id_bus_variation == 13.

I really don't have any idea of how to do this using a single query, the only way around it I found is to go through array A and execute a query for each of it's elements, matching all the fields I need, but that doesn't seem efficient.

Answer 1

It sounds like you want to match the structure of a subdocument in an array to one of many possible structures specified by an array. I'll give an example of how to do this in the mongo shell:

> db.test.insert({ 
    "_id" : 0, 
    bus : [
        { "id_bus" : 1, "id_bus_variation" : 1 },
        { "id_bus" : 2, "id_bus_variation" : 2 },
        { "id_bus" : 3, "id_bus_variation" : 3 }
    ]
})
> db.test.insert({ 
    "_id" : 1, 
    bus : [
        { "id_bus" : 1, "id_bus_variation" : 3 },
        { "id_bus" : 2, "id_bus_variation" : 2 },
        { "id_bus" : 3, "id_bus_variation" : 1 }
    ]
})
> db.test.insert({ 
    "_id" : 2, 
    bus : [
        { "id_bus" : 1, "id_bus_variation" : 1 },
        { "id_bus" : 2, "id_bus_variation" : 3 },
        { "id_bus" : 3, "id_bus_variation" : 2 }
    ]
})

If we want to return all documents where (id_bus = 2 and id_bus_variation = 3) or (id_bus = 3 and id_bus_variation = 3), as specified in an array

> var match = [{ "id_bus" : 2, "id_bus_variation" : 3 }, { "id_bus" : 3, "id_bus_variation" : 3 }];

We can construct the query programmatically:

> var query = { "$or" : [] }
> for (var i = 0; i < match.length; i++) {
    query["$or"].push({ "bus" : { "$elemMatch" : match[i] } });
}
> db.test.find(query, { "_id" : 1 }) // just get _id's for easy reading
{ "_id" : 0 }
{ "_id" : 2 }

We get the expected results.

Answer 2

I don't know if I understand your Question.

Your collection could be like

{
"_id" : ObjectId("53de54c1560b7815e123792f"),
"bus" : [ 
    {
        "id_bus" : 1,
        "id_bus_variation" : 13
    }, 
    {
        "id_bus" : 2,
        "id_bus_variation" : 184
    }, 
    {
        "id_bus" : 3,
        "id_bus_variation" : 13
    }
]

}

And you want retrieve the document only if id_bus and id_bus_variation are "true"

You can try it

db.stack.find({$and:[{ "bus.id_bus" : 1,"bus.id_bus_variation" : 13},{"bus.id_bus" : 2,"bus.id_bus_variation" : 184}]})

and retrieve the Document only if bus.id_bus and bus.id_bus_variation are in Document. For Example

db.stack.find({$and:[{ "bus.id_bus" : 1,"bus.id_bus_variation" : 13},{"bus.id_bus" : 2,"bus.id_bus_variation" : 184},{"bus.id_bus":4}]})

you haven't any result.

If you want exactly the element inside Object

db.stack.find ( { bus: { "$elemMatch" : { id_bus:1, id_bus_variation : 13} } }  )

The document return only if both value are "true"