CODEWITHSUNDEEP
arraysmongodbmongodb-query
Max Seleznov
Max Seleznov

Reputation: 3

mongodb, how to find in array other array

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.

Upvotes: 0

Views: 43

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50436

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.

Upvotes: 1

Related Questions