Hello Mongo Fam,
In my mongo collection, I have the “_id” which is object id key and an “priority” key which is an integer value. I’m applying the find query and sorting the data with {priority, -1}, {_id, 1}. But sometimes the “_id” sorting is happening first and then priority sorting where opposite should happen. The data is not coming as expected in these cases. The ordering of sorting is changing. Can you please give a solution to this?
It would be a major flaw if that would really happen. Most likely you are misusing find. Please share the exact code you are using.
opts := options.Find().SetSort(sortingOptionsBson).SetLimit(o_limit).SetSkip(o_skip)
cur, err = coll.Find(ctx, filter, opts)
// o_limit, o_skip have no issues. They are present above this line.
For sortingOptionsBson it would be like
sortingOptionsBson := bson.D{{"priority", -1},{"_id", 1}}
These are the inputs to the Find query. Can you suggest an alternate to this so that the priority sorting happens first and id sorting happens second all the time?
Please tag your thread with the programming language you are using.
I am not familiar with the syntax
In JSON, it should be { “priority” : -1 , “_id” : 1 }. I do not know if the above code produce the correct object.
How is options variable defined?
How is ctx variable defined?
Are you sure coll always refer to the collection that has the index?
I’m using Golang
opts := options.Find().SetSort(sortingOptionsBson).SetLimit(o_limit).SetSkip(o_skip)
Options is defined above here
ctx is passed from the function parameters where this code resides in
Yes I’m sure, coll represents the collection that has the index
Hope this can clear those queries
From the little I understand, the code
sets the variable opts from an expression that starts with the global variables options. We need to know how options is defined. May be someone with golang driver expertise knows that options is part of the driver. Hopefully this someone car carry on.
"go.mongodb.org/mongo-driver/bson"
"go.mongodb.org/mongo-driver/bson/primitive"
"go.mongodb.org/mongo-driver/mongo"
"go.mongodb.org/mongo-driver/mongo/options"
These are the mongo-drivers that I’m using
What would help at this point is that if you could share the explain plan of your query.
I’m using Golang. In my mongo collection, I have the “_id” which is object id key and an “priority” key which is an integer value. I’m applying the find query and sorting the data with {priority, -1}, {_id, 1} like shown below.
sortingOptionsBson := bson.D{{"priority", -1},{"_id", 1}}
My code would look something like this
// limit and offset values are passed from the arguments of the function where this code resides
// the problem lies with SetSort()
sortingOptionsBson := bson.D{{"priority", -1},{"_id", 1}}
opts := options.Find().SetSort(sortingOptionsBson).SetLimit(o_limit).SetSkip(o_skip)
cur, err = coll.Find(ctx, filter, opts)
Sometimes, the sort would be done based on “_id” first and then “priority” which should not be done.
I want to avoid this misordering and always want the priority sorting to happen first and _id sorting to happen second. What is the issue here and how can it be solved?
I want to reiterate:
We could also use sample documents that are wrongly sorted. It could also be useful to get sample documents that are correctly sorted.
Are the wrongly sorted documents reside on the same server as the one correctly sorted?
Could anyone with golang experience confirm that the code
generates a document equivalent to the JSON:
SO on how to run an explain with GO:
I saw a different syntax for bson in Help golang Aggregate Query
And like I wrote I do not know golang but I feel that
bson.M{ "priority" : -1 , "_id" : 1}
might be the correct way to specify the sort you want.
@Krishna_Chaitanya4 It’s not immediately clear what could be going wrong. The sort document looks correct
and the rest of the code you posted seems correct.
I have some questions to help troubleshoot:
I attempted to reproduce the issue you described using MongoDB 6.0 and Go Driver v1.12.1, but never got out-of-order results. Here’s the code I used to attempt to reproduce the issue:
func main() {
client, err := mongo.Connect(
context.Background(),
options.Client().ApplyURI("mongodb://localhost:27017/"))
if err != nil {
panic(err)
}
defer client.Disconnect(context.Background())
coll := client.Database("test").Collection("priority_order")
// Drop the collection so we start with an empty collection.
if err := coll.Drop(context.Background()); err != nil {
panic(err)
}
// Insert 20 docs with an integer "priority" field.
var docs []any
for i := 0; i < 20; i++ {
docs = append(docs, bson.D{{"priority", i}})
}
_, err = coll.InsertMany(context.Background(), docs)
if err != nil {
panic(err)
}
// Find the documents in reverse priority order, 2 at a time.
for i := 0; i < 10; i++ {
opts := options.Find().
SetSort(bson.D{{"priority", -1}, {"_id", 1}}).
SetLimit(2).
SetSkip(int64(i * 2))
cur, err := coll.Find(context.Background(), bson.D{}, opts)
if err != nil {
panic(err)
}
for cur.Next(context.Background()) {
fmt.Println(cur.Current)
}
if err := cur.Err(); err != nil {
panic(err)
}
}
}
// Output:
// {"_id": {"$oid":"64dfadfed96b783d87d5928a"},"priority": {"$numberInt":"19"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59289"},"priority": {"$numberInt":"18"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59288"},"priority": {"$numberInt":"17"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59287"},"priority": {"$numberInt":"16"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59286"},"priority": {"$numberInt":"15"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59285"},"priority": {"$numberInt":"14"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59284"},"priority": {"$numberInt":"13"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59283"},"priority": {"$numberInt":"12"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59282"},"priority": {"$numberInt":"11"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59281"},"priority": {"$numberInt":"10"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59280"},"priority": {"$numberInt":"9"}}
// {"_id": {"$oid":"64dfadfed96b783d87d5927f"},"priority": {"$numberInt":"8"}}
// {"_id": {"$oid":"64dfadfed96b783d87d5927e"},"priority": {"$numberInt":"7"}}
// {"_id": {"$oid":"64dfadfed96b783d87d5927d"},"priority": {"$numberInt":"6"}}
// {"_id": {"$oid":"64dfadfed96b783d87d5927c"},"priority": {"$numberInt":"5"}}
// {"_id": {"$oid":"64dfadfed96b783d87d5927b"},"priority": {"$numberInt":"4"}}
// {"_id": {"$oid":"64dfadfed96b783d87d5927a"},"priority": {"$numberInt":"3"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59279"},"priority": {"$numberInt":"2"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59278"},"priority": {"$numberInt":"1"}}
// {"_id": {"$oid":"64dfadfed96b783d87d59277"},"priority": {"$numberInt":"0"}}
I also have the same issue,
here are my two different pipelines,
pipeline1
[{
'$lookup': {
'from': 'model_results',
'let': {
'doc_id_str': {
'$toString': '$_id'
}
},
'pipeline': [{
'$match': {
'$expr': {
'$and': [{
'$eq': ['$document_id', '$$doc_id_str']
}
]
}
}
}, {
'$project': {
'_id': 0,
'PCR_prediction.processed_img_path': 1
}
}
],
'as': 'model_results'
}
}, {
'$match': {
'project_id': {
'$in': ['43329', '53643', '84434', '84587', '85503', '85956', '85993', '85996', '86153', '86193', '86225', '86226', '86250', '86315', '86415', '86521', '86848', '87367', '87647', '87648', '87650', '87651', '87652', '87654', '87656', '87661', '87664', '87665', '87668', '87669', '87670', '87671', '87672', '87673', '87674', '87675', '87679', '87680', '87683', '87686', '87687', '87688', '87690', '87691', '87692', '87693', '87694', '87695', '87696', '87698', '87700', '87701', '87704', '87706', '87707', '87708', '87710', '87711', '87712', '87714', '87717', '87718', '87719', '87722', '87723', '87724', '87725', '87726', '87727', '87730', '87732', '87734', '87736', '87737', '87739', '87740', '87741', '87742', '87743', '87744', '87746', '87750', '87751', '87758', '87761', '87762', '87763', '87764', '87765', '87766', '87767', '87768', '87769', '87770', '87771', '87772', '87773', '87774', '87776', '87777', '87778', '87779', '87780', '87781', '87782', '87784', '87786', '87787', '87788', '87789', '87791', '87792', '87794', '87795', '87796', '87797', '87798', '87799', '87800', '87802', '87803', '87804', '87805', '87806', '87807', '87808', '87809', '87811', '87815', '87816', '87819', '87822', '87823', '87825', '87827', '87828', '87829', '87831', '87832', '87833', '87834', '87835', '87836', '87837', '87838', '87839', '87840', '87841', '87842', '87843', '87852', '87859', '87878', '87880', '87887', '87891', '87895', '87900', '87901', '87903', '87904', '87905', '87906', '87907', '87908', '87913', '87914', '87918', '87919', '87920', '87922', '87923', '87924', '87925', '87928', '87929', '87931', '87935', '87938', '87941', '87944', '87945', '87946', '87947', '87950', '87951', '87961', '87962', '87965', '87967', '87970', '87971', '87972', '87973', '87974', '87975', '87976', '87977', '87978', '87979', '87980', '87981', '87982', '87983', '87984', '87989', '87993', '87994', '87995', '87996', '88000', '88003', '88004', '88005', '88006', '88007', '88008', '88009', '88010', '88011', '88012', '88013', '88014', '88015', '88016', '88017', '88019', '88020', '88021', '88022', '88023', '88024', '88025', '88026', '88027', '88028', '88029', '88030', '88031', '88036', '88038', '88042', '88043', '88044', '88045', '88046', '88047', '88048', '88051', '88052', '88053', '88055', '88056', '88057', '88058', '88059', '88062', '88063', '88064', '88065', '88066', '88067', '88068', '88069', '88070', '88075', '88076', '88080', '88081', '88083', '88086', '88091', '88092', '88093', '88094', '88096', '88101', '88102', '88104', '88105', '88106', '88107', '88109', '88110', '88112', '88114', '88115', '88116', '88118', '88121', '88122', '88123', '88124', '88125', '88126', '88127', '88128', '88136', '88145', '88146', '88150', '88151', '88154', '88155', '88157', '88160', '88163', '88167', '88170', '88171', '88172', '88173', '88174', '88175', '88176', '88177', '88178', '88179', '88182', '88184', '88185', '88186', '88187', '88188', '88189', '88192', '88193', '88194', '88197', '88198', '88199', '88204', '88205', '88207', '88214', '88216', '88217', '88218', '88220', '88221', '88222', '88223', '88224', '88225', '88229', '88230', '88231', '88232', '88233', '88234', '88235', '88237', '88239', '88241', '88242', '88243', '88244', '88245', '88246', '88247', '88248', '88249', '88251', '88254', '88255', '88256', '88261', '88262', '88263', '88264', '88265', '88266', '88268', '88269', '88270', '88271', '88273', '88274', '88275', '88276', '88279', '88281', '88282', '88283', '88285', '88286', '88287', '88288', '88289', '88290', '88291', '88292', '88293', '88294', '88295', '88296', '88302', '88304', '88305', '88306', '88307', '88308', '88310', '88311', '88313', '88315', '88316', '88317', '88318', '88319', '88322', '88323', '88326', '88330', '88331', '88332', '88333', '88335', '88336', '88337', '88338', '88340', '88341', '88342', '88346', '88347', '88348', '88349', '88350', '88351', '88352', '88354', '88355', '88356']
},
'$and': [{
'$or': [{
'status': ''
}, {
'status': {
'$exists': False
}
}, {
'status': 'Canceled'
}, {
'status': 'Suspended'
}, {
'status': 'Rejected'
}
]
}, {
'status': {
'$ne': 'Approved'
}
}
]
}
}, {
'$sort': {
'document_priority': 1
}
}, {
'$match': {
'delete': False,
'folder_id': {
'$exists': True
}
}
}, {
'$facet': {
'totalCount': [{
'$count': 'count'
}
],
'results': [{
'$skip': 0
}, {
'$limit': 20
}
]
}
}
]
pipeline2
[{
'$lookup': {
'from': 'model_results',
'let': {
'doc_id_str': {
'$toString': '$_id'
}
},
'pipeline': [{
'$match': {
'$expr': {
'$eq': ['$document_id', '$$doc_id_str']
}
}
}, {
'$project': {
'_id': 0,
'PCR_prediction.processed_img_path': 1
}
}
],
'as': 'model_results'
}
}, {
'$match': {
'$and': [{
'project_id': {
'$in': ['43329', '53643', '84434', '84587', '85503', '85956', '85993', '85996', '86153', '86193', '86225', '86226', '86250', '86315', '86415', '86521', '86848', '87367', '87647', '87648', '87650', '87651', '87652', '87654', '87656', '87661', '87664', '87665', '87668', '87669', '87670', '87671', '87672', '87673', '87674', '87675', '87679', '87680', '87683', '87686', '87687', '87688', '87690', '87691', '87692', '87693', '87694', '87695', '87696', '87698', '87700', '87701', '87704', '87706', '87707', '87708', '87710', '87711', '87712', '87714', '87717', '87718', '87719', '87722', '87723', '87724', '87725', '87726', '87727', '87730', '87732', '87734', '87736', '87737', '87739', '87740', '87741', '87742', '87743', '87744', '87746', '87750', '87751', '87758', '87761', '87762', '87763', '87764', '87765', '87766', '87767', '87768', '87769', '87770', '87771', '87772', '87773', '87774', '87776', '87777', '87778', '87779', '87780', '87781', '87782', '87784', '87786', '87787', '87788', '87789', '87791', '87792', '87794', '87795', '87796', '87797', '87798', '87799', '87800', '87802', '87803', '87804', '87805', '87806', '87807', '87808', '87809', '87811', '87815', '87816', '87819', '87822', '87823', '87825', '87827', '87828', '87829', '87831', '87832', '87833', '87834', '87835', '87836', '87837', '87838', '87839', '87840', '87841', '87842', '87843', '87852', '87859', '87878', '87880', '87887', '87891', '87895', '87900', '87901', '87903', '87904', '87905', '87906', '87907', '87908', '87913', '87914', '87918', '87919', '87920', '87922', '87923', '87924', '87925', '87928', '87929', '87931', '87935', '87938', '87941', '87944', '87945', '87946', '87947', '87950', '87951', '87961', '87962', '87965', '87967', '87970', '87971', '87972', '87973', '87974', '87975', '87976', '87977', '87978', '87979', '87980', '87981', '87982', '87983', '87984', '87989', '87993', '87994', '87995', '87996', '88000', '88003', '88004', '88005', '88006', '88007', '88008', '88009', '88010', '88011', '88012', '88013', '88014', '88015', '88016', '88017', '88019', '88020', '88021', '88022', '88023', '88024', '88025', '88026', '88027', '88028', '88029', '88030', '88031', '88036', '88038', '88042', '88043', '88044', '88045', '88046', '88047', '88048', '88051', '88052', '88053', '88055', '88056', '88057', '88058', '88059', '88062', '88063', '88064', '88065', '88066', '88067', '88068', '88069', '88070', '88075', '88076', '88080', '88081', '88083', '88086', '88091', '88092', '88093', '88094', '88096', '88101', '88102', '88104', '88105', '88106', '88107', '88109', '88110', '88112', '88114', '88115', '88116', '88118', '88121', '88122', '88123', '88124', '88125', '88126', '88127', '88128', '88136', '88145', '88146', '88150', '88151', '88154', '88155', '88157', '88160', '88163', '88167', '88170', '88171', '88172', '88173', '88174', '88175', '88176', '88177', '88178', '88179', '88182', '88184', '88185', '88186', '88187', '88188', '88189', '88192', '88193', '88194', '88197', '88198', '88199', '88204', '88205', '88207', '88214', '88216', '88217', '88218', '88220', '88221', '88222', '88223', '88224', '88225', '88229', '88230', '88231', '88232', '88233', '88234', '88235', '88237', '88239', '88241', '88242', '88243', '88244', '88245', '88246', '88247', '88248', '88249', '88251', '88254', '88255', '88256', '88261', '88262', '88263', '88264', '88265', '88266', '88268', '88269', '88270', '88271', '88273', '88274', '88275', '88276', '88279', '88281', '88282', '88283', '88285', '88286', '88287', '88288', '88289', '88290', '88291', '88292', '88293', '88294', '88295', '88296', '88302', '88304', '88305', '88306', '88307', '88308', '88310', '88311', '88313', '88315', '88316', '88317', '88318', '88319', '88322', '88323', '88326', '88330', '88331', '88332', '88333', '88335', '88336', '88337', '88338', '88340', '88341', '88342', '88346', '88347', '88348', '88349', '88350', '88351', '88352', '88354', '88355', '88356']
},
'$and': [{
'$or': [{
'status': ''
}, {
'status': {
'$exists': False
}
}, {
'status': 'Canceled'
}, {
'status': 'Suspended'
}, {
'status': 'Rejected'
}
]
}, {
'status': {
'$ne': 'Approved'
}
}
]
}, {
'$or': [{
'locked_by_userid': {
'$in': ['10022', '']
}
}, {
'locked_by_userid': {
'$exists': False
}
}
]
}
]
}
}, {
'$sort': {
'document_priority': 1
}
}, {
'$match': {
'delete': False,
'folder_id': {
'$exists': True
}
}
}, {
'$project': {
'document_id': {
'$toString': '$_id'
},
'folder_id': 1,
'file_name': 1,
'_id': 0
}
}, {
'$skip': 0
}, {
'$limit': 20
}
]
pipeline 1 order is
| Line 22: document_id: 667d2e2cc89e06d79039f461, | |
|---|---|
| Line 92: document_id: 667d2e55c89e06d79039f463, | |
| Line 160: document_id: 66841bbbbee5716f7821d8d0, | |
| Line 225: document_id: 66825e00055c08cd52fa331c, | |
| Line 288: document_id: 667d500ad0d408a6e307e8cf, | |
| Line 356: document_id: 667d2dd8aa995265202e4b81, | |
| Line 415: document_id: 6677e0f53bb5dd7e58440811, | |
| Line 467: document_id: 6677e0f2872f317c0dc9fd2e, | |
| Line 519: document_id: 6677d7433bb5dd7e584407f7, | |
| Line 571: document_id: 6677d740bbbebb61c49b3039, | |
| Line 623: document_id: 6677bcd83bb5dd7e584407b9, | |
| Line 683: document_id: 66754121d598ce1606ad0d24, | |
| Line 765: document_id: 6673faf229f34c753834b457, | |
| Line 904: document_id: 6673d45f89cce69a21158a60, | |
| Line 1014: document_id: 667436158014fa290da794f6, | |
| Line 1094: document_id: 667436158014fa290da794f9, | |
| Line 1174: document_id: 667436158014fa290da794fc, | |
| Line 1254: document_id: 667436158014fa290da794ff, | |
| Line 1334: document_id: 667436158014fa290da79502, | |
| Line 1414: document_id: 667436158014fa290da79505, |
pipeline2 order is
| Line 4: ‘document_id’: ‘667d2e2cc89e06d79039f461’ | |
|---|---|
| Line 8: ‘document_id’: ‘667d2e55c89e06d79039f463’ | |
| Line 12: ‘document_id’: ‘667436158014fa290da79502’ | |
| Line 16: ‘document_id’: ‘667436158014fa290da79505’ | |
| Line 20: ‘document_id’: ‘6677bcd83bb5dd7e584407b9’ | |
| Line 24: ‘document_id’: ‘667436158014fa290da794fc’ | |
| Line 28: ‘document_id’: ‘6677d7433bb5dd7e584407f7’ | |
| Line 32: ‘document_id’: ‘6677d740bbbebb61c49b3039’ | |
| Line 36: ‘document_id’: ‘667d2dd8aa995265202e4b81’ | |
| Line 40: ‘document_id’: ‘66754121d598ce1606ad0d24’ | |
| Line 44: ‘document_id’: ‘6673faf229f34c753834b457’ | |
| Line 48: ‘document_id’: ‘667436158014fa290da794f9’ | |
| Line 52: ‘document_id’: ‘6677e0f2872f317c0dc9fd2e’ | |
| Line 56: ‘document_id’: ‘667436158014fa290da794ff’ | |
| Line 60: ‘document_id’: ‘6673d45f89cce69a21158a60’ | |
| Line 64: ‘document_id’: ‘66825e00055c08cd52fa331c’ | |
| Line 68: ‘document_id’: ‘66841bbbbee5716f7821d8d0’ | |
| Line 72: ‘document_id’: ‘667d500ad0d408a6e307e8cf’ | |
| Line 76: ‘document_id’: ‘6677e0f53bb5dd7e58440811’ | |
| Line 80: ‘document_id’: ‘667436158014fa290da794f6’ |