CODEWITHSUNDEEP
mongodb
Frio_Penitencia
Frio_Penitencia

Reputation: 103

MongoDb Issue with sorting while using aggregate

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?

Upvotes: 0

Views: 60

Answers (1)

user12582392
user12582392

Reputation:

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.

Upvotes: 1

Related Questions