MongoDB: $in with an ObjectId array

Question

Just a quick question about something I've just experienced and I'm still thinking about why:

mongos> db.tickets.count({ "idReferenceList" : { "$in" : [ { "$oid" : "53f1f09f2cdcc8f339e5efa2"} , { "$oid" : "5409ae2e2cdc31c5aa0ce0a5"}]}});

0

mongos> db.tickets.count({ "idReferenceList" : { "$in" : [ ObjectId("53f1f09f2cdcc8f339e5efa2") , ObjectId("5409ae2e2cdc31c5aa0ce0a5")]}});

2

I thought that both $oid and ObjectId spelling formats where exactly the same for MongoDB. Does anyone know why with the first query return 0 results and with the second one is returning 2 (the right answer)?

Furthermore, I'm using Morphia framework which uses MongoDB Java driver to interact with MongoDB. I've realised that there exists a problem by searching with $in operator in ObjectIds arrays over fields that are not _id by executing this lines of code:

List< ObjectId > fParams = new ArrayList< ObjectId >();

fParams.add(...);

Query<Ticket> query = genericDAO.createQuery();

query.field("idReferenceList").in(fParams);

result = genericDAO.find(query).asList();

Thank you very much in advance.

Regards,

• Luis Cappa

Uralic

genericDAO.find(query).asList(); // can you do this and check. ? Wjat is the filter you are using in this.createQuery(filter), by the way. – Cookie

Sorry, it was just a typo error. This 'createQuery' method is an internal method that creates a Query by filtering by the fParams list. In short words, genericDAO.Find(this.createQuery(filter)) is the same as genericDAO.find(query). Sorry about that. – Uralic

Answer 1

Both these formats are valid representations of an object id in MongoDB, according to the documentation,

http://docs.mongodb.org/manual/reference/mongodb-extended-json/

and they represented differently in the two modes,

    Strict Mode         mongo Shell Mode
    -----------         ----------------

   { "$oid": "<id>" }  ObjectId( "<id>" )

So, to query fields which contain objectid, from the shell/console mode, you need to use ObjectId("<id>"). Which is the syntax to be followed in the mongo shell mode.

Hence the query:

db.tickets.count({ "idReferenceList" : { "$in" : [ ObjectId("53f1f09f2cdcc8f339e5efa2") , ObjectId("5409ae2e2cdc31c5aa0ce0a5")]}});

would return you row count.

Now to do it via the Java API,

You need to do it as below:

String[] ids = {"53f1f09f2cdcc8f339e5efa2","5409ae2e2cdc31c5aa0ce0a5"};
ObjectId[] objarray = new ObjectId[ids.length];

for(int i=0;i<ids.length;i++)
{
    objarray[i] = new ObjectId(ids[i]);
}

BasicDBObject inQuery = new BasicDBObject("$in", objarray);
BasicDBObject query = new BasicDBObject("idReferenceList", inQuery);
DBCursor cursor = db.collection.find(query);
while(cursor.hasNext())
{
    DBObject doc = cursor.next();
    // process the doc.
}

Cookie

The thing is that I'm not querying to "_id" field, but to "idReferenceList", which is another different field defined with a field type of Array of objectIds. As you can see, It doesn't work... – Uralic

objectid representation remains the same irrespective of the field it is present in. Will update my answer. I just gave an reference to the _id field. – Cookie

But with the "$oid" representación It doesn't work. Did you test it? Just save two test documents via shell and try it yourself. The problem anyway is that I'm using MongoDB Java driver and I cannot spell by myself directly the "ObjectId" query format. – Uralic

Yes, read my answer carefully. $oid representation will not work in the shell because it is the syntax to be followed in a strict mode. – Cookie

So... any idea to solve this problem? Because as I told you I'm using MongoDB Java driver and query spelling is not as flexible as desired. – Uralic

groups.google.com/forum/m/#!topic/mongodb-user/YNrVuv1wGGw this link might help you. – Cookie

@Uralic - have updated my answer with how you need to do this in java API – Cookie

I'm using Morphia, which is using MongoDB java driver inside to interact with MongoDB. I'm doing the same as you indicated in your updated answer, but using Morphia framework, and it's not working. Take a look to my updated answer. – Uralic

Answer 2

I faced the same issue. I resolved like this way.

db.collection('post').find({ 'postIds': { $elemMatch: { $in:
deletedPosts.map(_post => {ObjectId(_post._id)}) } } })

Downhearted