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.

I have this problem, stackoverflow.com/questions/70368089/…, with this data $and: [ {"opening_hours.time": {$elemMatch: { "from": {$lte: ISODate("2001-01-01 03:00:00.000")}}}}, {"opening_hours.time": {$elemMatch: { "to": {$gte: ISODate("2001-01-01 03:00:00.000")}}}} ] it is still gives me the same wrong results. — Aljohn Yamaro
– Aljohn Yamaro, Commented Dec 19, 2021 at 2:04

wdberkeley · Accepted Answer · 2014-08-04 19:57:57Z

12

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.

answered Aug 4, 2014 at 19:57

wdberkeley

11.7k1 gold badge30 silver badges24 bronze badges

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

2 Comments

martskins Over a year ago

Yep, that's it. Strangely (and stupidly) enough I'm used to doing this when using PHP + mysql but it didn't come to mind in this project. Thanks for your answer.

Rafael Santos Over a year ago

Is there a simple one-liner that would return all documents for { "id_bus" : 2, "id_bus_variation" : 2 }, in this case, documents with _id 0 and 1?

Barno · Accepted Answer · 2014-08-03 15:49:20Z

10

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"

answered Aug 3, 2014 at 15:49

Barno

3,3415 gold badges31 silver badges60 bronze badges

2 Comments

martskins Over a year ago

The thing is I don't have id_bus and id_bus_variation values beforehand, they are in an array which will vary in length.

Tejas R. Over a year ago

@Barno if we want exact match in array along with id based comparison then how this query will be gets modified? Id is outside array db.stack.find ( { bus: { "$elemMatch" : { id_bus:1, id_bus_variation : 13} } } )

Collectives™ on Stack Overflow

Find documents matching multiple fields in an object array in MongoDB

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related