"range query" unable to get the desired data when When the same field contains a different type value in cluster mode.

About the Community Welcome
,
1

my test collection have 4 chunks, as following:

                test.test
                        shard key: { "_id" : 1 }
                        unique: false
                        balancing: true
                        chunks:
                                xxx-81mzljyl_0 2
                                xxx-81mzljyl_1 1
                                xxx-81mzljyl_2 1
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : "abc" } on : cmgo-81mzljyl_0 Timestamp(2, 0) 
                        { "_id" : "abc" } -->> { "_id" : ObjectId("67a4779ffa7ca35692362ccf") } on : cmgo-81mzljyl_1 Timestamp(3, 1) 
                        { "_id" : ObjectId("67a4779ffa7ca35692362ccf") } -->> { "_id" : ObjectId("67a47931fa7ca356923668a7") } on : cmgo-81mzljyl_2 Timestamp(4, 1) 
                        { "_id" : ObjectId("67a47931fa7ca356923668a7") } -->> { "_id" : { "$maxKey" : 1 } } on : cmgo-81mzljyl_0 Timestamp(4, 0)

there only have 8 pieces of data， as following:

mongos> db.test.find({ "_id": { $gte: MinKey}}).sort({_id:1})
{ "_id" : 1 }
{ "_id" : 121 }
{ "_id" : "aaaaaa" }
{ "_id" : "abc" }
{ "_id" : ObjectId("67a4b25d72257c83e02d4aae"), "aaaa" : 1 }
{ "_id" : ObjectId("67a4b25f72257c83e02d4aaf"), "aasaa" : 1 }
{ "_id" : ObjectId("67a4b26a72257c83e02d4ab0"), "6aasaa" : 1 }
{ "_id" : ObjectId("67a4b26d72257c83e02d4ab1"), "7aasaa" : 1 }

According to the preceding query, the following four pieces of data belong to the chunk(“_id” : { “$minKey” : 1 } } -->> { “_id” : “abc” }):

{ "_id" : 1 }
{ "_id" : 121 }
{ "_id" : "aaaaaa" }
{ "_id" : "abc" }

I use this chunk’s min and max to get data， but I noly get 2 data, as following:

mongos> db.test.find({ "_id": { $gte: MinKey , $lte: "abc"}});
{ "_id" : "abc" }
{ "_id" : "aaaaaa" }
mongos>

Question： why can’t access query get the following two data：

{ "_id" : 1 }
{ "_id" : 121 }

my range query sql is :

 db.test.find({ "_id": { $gte: MinKey , $lte: "abc"}});
2

Hi

$gte/$lte comparison operators only perform comparisons on fields where the BSON type matches the query value’s type (documentation), so in your specific case over strings.

In order to look for every document inside the range independently of the BSON type you can use the $expr with $gt/$lt which don’t do type bracketing.

db.test.explain().find({ $expr: {$and: [{$gte:["$_id", MinKey]},{$lte:["$_id", "abc"]}]}});
[{ _id: 1 }, 
{ _id: 121 }, 
{ _id: 'aaaaaa' }, 
{ _id: 'abc' } ]

Note that the right bound of a chunk is not inclusive, this means that if you are trying to query the data specifically within a given chunk’s boundaries, you should use the $lt operator for the upper bound rather than $lte.

db.test.explain().find({ $expr: {$and: [{$gte:["$_id", MinKey]},{$lt:["$_id", "abc"]}]}});
[{ _id: 1 },
{ _id: 121 },
{ _id: 'aaaaaa' } ]
1 Like