MongoDb Issue with sorting while using aggregate

Question

I am required to write a aggregate query that lists the subject code, title of book and publisher of books. The results should that be listed in ascending order for the subject code and descending order for the publisher of books.

So far with the query I have, I am able to have the output print the results with subject code in ascending order but the publisher of books is not sorted out in descending order.

Query:

db.Subject.aggregate([{$project:{"subject.subCode":1,"subject.book.bookTitle":1,"subject.book.publisher":1}},{$sort:{"subject.subCode":1,"subject.book.publisher":-1}}]).pretty()

Output:

{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e10771d"),
    "subject" : {
        "subCode" : "CSCI103",
        "book" : [
            {
                "bookTitle" : "Introduction to the Design and Analysis of Algorithms",
                "publisher" : "Pearson"
            },
            {
                "publisher" : "Pearson"
            }
        ]
    }
}
{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e10771e"),
    "subject" : {
        "subCode" : "CSCI114",
        "book" : [
            {
                "bookTitle" : "C++ Programming - Program design including data structure",
                "publisher" : "CENGAGE Learning"
            },
            {
                "bookTitle" : "Starting Out With C++: From Control Structures through Objects",
                "publisher" : "Addison-Wesley"
            }
        ]
    }
}
{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e10771f"),
    "subject" : {
        "subCode" : "CSCI124",
        "book" : [
            {
                "bookTitle" : "Data Structures Using C++",
                "publisher" : "CENGAGE Learning"
            },
            {
                "bookTitle" : "C++ Programming - Program design including data structure",
                "publisher" : "CENGAGE Learning"
            },
            {
                "bookTitle" : "Starting Out With C++: From Control Structures through Objects",
                "publisher" : "Addison-Wesley"
            }
        ]
    }
}
{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e107721"),
    "subject" : {
        "subCode" : "CSCI203",
        "book" : [
            {
                "bookTitle" : "Introduction to the Design and Analysis of Algorithms",
                "publisher" : "Pearson"
            },
            {
                "bookTitle" : "Introduction to Algorithms",
                "publisher" : "The MIT Press"
            }
        ]
    }
}
{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e107720"),
    "subject" : {
        "subCode" : "CSCI235",
        "book" : [
            {
                "bookTitle" : "Fundamentals of Database Systems",
                "publisher" : "Addison-Wesley"
            },
            {
                "bookTitle" : "Database Management Systems",
                "publisher" : "McGraw-Hill"
            }
        ]
    }
}
{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e107723"),
    "subject" : {
        "subCode" : "CSCI321"
    }
}
{
    "_id" : ObjectId("5fb1ea7658bf7f7d4e107722"),
    "subject" : {
        "subCode" : "IACT201"
    }
}

Chunk of sample data:

db.Subject.insert(
{ 
    "_id":ObjectId(),
    "subject":{ 
        "subCode":"CSCI235",
        "subTitle":"Database Systems",
        "credit":3,
        "type":"Core",
        "prerequisite": ["csci124","csci103"],
        "assessments": [
                { "assessNum": 1,
                  "weight":10,
                  "assessType":"Assignment",
                  "description":"Assignment 1 - Normalization and Indexing" },
                { "assignNum": 2,
                  "weight":10,
                  "assessType":"Assignment",
                  "description":"Assignment 2 - PL/SQL programming and Concurrency Control" },
                { "assessNum": 3,
                  "weight":10,
                  "assessType":"Assignment",
                  "description":"Assignment 3 - JSON/BSON and MongoDB" },
                { "assessNum": 4,
                  "weight":10,
                  "assessType":"Laboratory",
                  "description":"Laboraory/Implementation Tasks" },
                { "assessNum": 5,
                  "weight": 60,
                  "assessType":"Examination",
                  "description":"Closed-book Final Examination" }
            ],
        "book": [
                { "ISBN":"13:978-0-136-08620-8",
                  "bookType":"textbook",
                  "bookTitle":"Fundamentals of Database Systems",
                  "edition":6,
                  "yearPub":2010,
                  "publisher":"Addison-Wesley",
                  "author": [ "Ramez Elmasri", "Shamkant B Navathe" ] },
                { "ISBN":"0-07-246563-8",
                  "bookType":"reference",
                    "bookTitle":"Database Management Systems",
                  "edition":3,
                  "yearPub":2003,
                  "publisher":"McGraw-Hill",
                  "author": [ "Raghur Ramakrishnan", "Johannes Gehrke" ] } ]
  }
}
)

How can I change my query so that the publishers will also be sorted out in descending order?

the sorting does not occur inside the array, it sorts ascending order documents with the same subCode. Basically, sorting occurs at the document level. — user12582392
– user12582392, Commented Nov 19, 2020 at 3:15
Is there a way to circumvent this so that I can meet the required condition? — Frio_Penitencia
– Frio_Penitencia, Commented Nov 19, 2020 at 3:17

user12582392 · Accepted Answer · 2020-11-19 03:20:49Z

1

The sorting does not occur inside the array, it sorts ascending order documents with the same subCode. Basically, sorting occurs at the document level.

Try this

db.Subject.aggregate([ 
{$project:{"subject.subCode":1,
  "subject.book.bookTitle":1,
  "subject.book.publisher":1} 
},
{$unwind:"$subject.book"},
{$sort:{
  "subject.subCode":1,
  "subject.book.publisher":-1
}
}]).pretty()

$unwind creates a new entry for each item in the array.

answered Nov 19, 2020 at 3:20

user12582392

Sign up to request clarification or add additional context in comments.

4 Comments

Frio_Penitencia Over a year ago

Noted. Your answer provided the result perfectly. I realized that my entry earlier gave 2 entries with no books in it as shown here: { "_id" : ObjectId("5fb1ea7658bf7f7d4e107723"), "subject" : { "subCode" : "CSCI321" } } { "_id" : ObjectId("5fb1ea7658bf7f7d4e107722"), "subject" : { "subCode" : "IACT201"

Frio_Penitencia Over a year ago

May I know why this happened?

user12582392 Over a year ago

@Frio_Penitencia test using db.Subject.findOne({_id:ObjectId("5fb1ea7658bf7f7d4e107722")}) to see if there is any problem in the structure. maybe the fields are empty

Frio_Penitencia Over a year ago

Yes those entries are empty as not all subjects utilize books in their curriculum.

Collectives™ on Stack Overflow

MongoDb Issue with sorting while using aggregate

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related