mongodb, how to find in array other array

Question

I need some help. I am trying to find an array in another array.

Records:

{
    "_id" : ObjectId("56b7e6966213e8d142ef55b7"),
    "users" : [ 
        ObjectId("56b0e547a838b5a45b4d0100"), // 0
        ObjectId("56a7e16e37e5adf32b97cc3d")  // 1
    ]
},
{
    "_id" : ObjectId("56b7e6966213e8d142ef55b7"),
    "users" : [ 
        ObjectId("56b0e547a838b5a45b4d0100"), 
        ObjectId("56a7e16e37e5adf32b97cc3d"),
        ObjectId("56b7e6b96213e8d142ef55b8")
    ]
}

I'm trying to find only first record "_id" : ObjectId("56b7e6966213e8d142ef55b7"),

I'm using query:

db.collection.find({"users" : { $in: [ 
    ObjectId("56b0e547a838b5a45b4d0100"), 
    ObjectId("56a7e16e37e5adf32b97cc3d")
]}})

or

db.collection.find({ $and: [{
    "users": ObjectId("56b0e547a838b5a45b4d0100")
}, {
    "users": ObjectId("56a7e16e37e5adf32b97cc3d")
}]})

Response gives me two records.

This request gives me the correct response:

db.collection.find({"users" : [ 
    ObjectId("56b0e547a838b5a45b4d0100"), // 0
    ObjectId("56a7e16e37e5adf32b97cc3d")  // 1
]})

but if record has an array like this:

{
    "_id" : ObjectId("56b7e6966213e8d142ef55b7"),
    "users" : [ 
        ObjectId("56a7e16e37e5adf32b97cc3d"), // 1
        ObjectId("56b0e547a838b5a45b4d0100")  // 0
    ]
}

it doesn't work

$all, $eq, $in also doesn't work

db.rooms.find({  
    users: {
        $all: [ 
        ObjectId("56a7e16e37e5adf32b97cc3d"), 
        ObjectId("56b0e547a838b5a45b4d0100")
        ]
    } 
})

I need to find rows where [1,2] == [1,2] or [1,2] == [2,1]

not where [1,2,3] == [1,2]

I will appreciate if somebody can help me.

Blakes Seven · Accepted Answer · 2016-02-08 02:26:02Z

If all you expect is the resulting document with "only" those two array entries, then the combination of conditions you want is $all and $size:

db.rooms.find({
    "users": {
        "$all": [
                ObjectId("56b0e547a838b5a45b4d0100"),
                ObjectId("56a7e16e37e5adf32b97cc3d") 
        ],
        "$size": 2
    }
})

Where $all is basically an $and with shorter syntax, and the $size retricts that since the list as n entries, then you only want to match something of that particular length/size.

This is better than specifying the array directly, since it will match in either order, as opposed to this statement that would not match considering the order is different and therefore not an "exact" match:

db.rooms.find({
    "users": [
        ObjectId("56a7e16e37e5adf32b97cc3d"),
        ObjectId("56b0e547a838b5a45b4d0100")
    ]
})

So logically the array contains "all" the specified entries, and is the same "size" as the input and no different.

for some reason this doesnt work for me db.rooms.find({ "users": { "$all": [ ObjectId("56b0e547a838b5a45b4d0100"), ObjectId("56a7e16e37e5adf32b97cc3d") ], "$size": 2 } }) , im using { $and: [{ "users": ObjectId("56b0e547a838b5a45b4d0100") }, { "users" : ObjectId("56a7e16e37e5adf32b97cc3d") }], users: { $size: 2 } }
@MaxSeleznov Same basic thing. If your MongoDB version is less than 2.6 then $all might behave differently.

Collectives™ on Stack Overflow

mongodb, how to find in array other array

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related