Sort mongo results if a value exists in the field or not

Question

I have a Student struct which looks like this.

type Student struct {
    Name            string                         `json:"name" bson:"name"`
    Marks           int                            `json:"marks" bson:"marks"`
    Subjects        []string                       `json:"subjects" bson:"subjects"`
}

I am using opts.Sort to Sort the result. More on that

opts.Sort = bson.D{
    {Key: "marks", Value: -1},
}

I also want to sort the results by Subjects, in a way that, if for any Student, if the subject Math exist, it should be sorted on top (descending order), before sorting it by marks I tried doing this

opts.Sort = bson.D{
    {Key: "subjects", Value: bson.M{"$in": "math"}},
    {Key: "marks", Value: -1},
}

I know this doesn't seem right because I am not passing 1 or -1 but I don't know how can I modify it to make it work. What am I missing here?.

icza · Answer 1 · 2023-01-19T13:38:32.213

1

You can't do this with a single "simple" query.

Query the records sorted by marks, and do a second sorting in Go, by moving documents having "math" subject to the front.

If you need to do this only in MongoDB, you may redesign: e.g. you may add a boolean field to documents storing the information whether the student has "math", so you can easily include that in sorting.

Do note however that you may do this with the Aggregation framework. This is the query that would do what you need:

db.students.aggregate(
    {$addFields:{"hasMath": {$in:["math", "$subjects"]}}},
    {$sort:{hasMath: -1, marks: -1}}
)

What this does is essentially what I suggested: it adds a hasMath field, telling if the student has "math" in the subjects array, and then sorts documents first by hasMath descending, then by marks descending.

This is how you can do that in Go using the official mongo-go driver:

c := ... // Obtain students collection

pipe := []bson.M{
    {"$addFields": bson.M{
        "hasMath": bson.M{"$in": []any{"math", "$subjects"}},
    }},
    {"$sort": bson.D{
        {Key: "hasMath", Value: -1},
        {Key: "marks", Value: -1},
    }},
}

curs, err := c.Aggregate(ctx, pipe)
if err != nil {
    // Handle error
    panic(err)
}

var students []Student
if err := curs.All(ctx, &students); err != nil {
    // Handle error
    panic(err)
}

edited Jan 19 '23 at 13:38

answered Jan 18 '23 at 17:45

icza

389,944
63
907
827

Thanks you!. I never thought about adding a new field as it can be a big change and can affect a lot of APIs using this method unfortunately. How can I do a "second Sorting".I am a bit lost here, thanks – Rajat Singh Jan 19 '23 at 11:48
1

@RajatSingh The second part of the answer solves your task using the Aggregation framework. This doesn't require you to change anything in the database, the field is only created during the aggregation execution. – icza Jan 19 '23 at 11:53
just one doubt, are you sure we have to use `{"$sort": bson.D{{"hasBoostTags", -1}}}` and not `{"$sort": bson.D{{Key: "hasBoostTags", Value: -1}}},` ?. I get a warning `primitive.E struct literal uses unkeyed fields` – Rajat Singh Jan 19 '23 at 13:28
1

@RajatSingh That is just a warning, it's recommended to use named key literals. It's just a warning, the code means the same. Do use it if you want to get rid of the warning. I've updated the answer. – icza Jan 19 '23 at 13:37
I just found out that we also gonna use this sorting on an another collection, using `Find()`, is there's any way to extend the code or any other ways what you talked about `do a second sorting in Go`, how can I do it, I am pretty new to Mongo and trying to read the docs and understand but didn't made any progress. – Rajat Singh Jan 19 '23 at 22:09
1

@RajatSingh You can use this "technique" on any collection. Without specifics I can't give a more specific answer. – icza Jan 19 '23 at 22:20

Sort mongo results if a value exists in the field or not

1 Answers1