I am trying to do the following questions.

1. Find the ISBN and title of books that was published in 2022.

2. Find the ISBN, title of book and publisher of books that have at least two authors and at most three authors.

MongoDB subject.js

For Question 1, I've tried: I wanted to try to use $eq but I'm not so sure how to so I tried using $elemMatch but it seems like I have no idea how it works too.

db.Subject.aggregate( [
     {"$unwind":"$subject.book"},
     {"$match":{"subject.book":{"$elemMatch":{"subject.yearPub":2022}} }},
     {"$project":{"subject.book.ISBN":1,"subject.book.bookTitle":1 }}
] )

For Question 2, I've tried: This works so far it gives me books with 3 authors. But for some reason it misses the books with 2 authors.

db.Subject.aggregate([{"$match": {"$or": [{'subject.book.author.3': {"$exists": true}},
{'book.author.2': {"$exists": true}}]}},
{"$project": {"subject.book.ISBN": 1,"subject.book.bookTitle": 1,"subject.book.publisher": 1,"_id":0}}
]).pretty()

CodePudding user response：

Instead of $unwind you can also use $filter:

db.collection.aggregate([
  {
    $match: {
      "subject.book": { $exists: true }
    }
  },
  {
    "$project": {
      book: {
        $filter: {
          input: "$subject.book",
          cond: { $eq: [ "$$this.yearPub", 2022 ] }
        }
      }
    }
  }
])

Mongo Playground

CodePudding user response：

Q1: Filter with dot notation: "subject.book.yearPub"

db.Subject.aggregate([
  {
    "$unwind": "$subject.book"
  },
  {
    "$match": {
      "subject.book.yearPub": 2022
    }
  },
  {
    "$project": {
      "subject.book.ISBN": 1,
      "subject.book.bookTitle": 1
    }
  }
])

Demo Q1 @ Mongo Playground

Q2: I suspect that you observe the result incorrectly. subject.book.author.3 will return you the fourth element from the subject.book.author array as the index starts from 0.

For your current query, it will return the document with the third and fourth elements in the subject.book.author array which means that as long the array contains more than 2 elements, the document will be returned.

Instead, you need the $size operator to get the size of the array. While there are documents that don't have the book property, so use $ifNull to return the empty array to prevent an error when using $size.

db.Subject.aggregate([
  {
    "$match": {
      $expr: {
        $in: [
          {
            $size: {
              $ifNull: [
                "$subject.book.author",
                []
              ]
            }
          },
          [
            2,
            3
          ]
        ]
      }
    }
  },
  {
    "$project": {
      "subject.book.ISBN": 1,
      "subject.book.bookTitle": 1,
      "subject.book.publisher": 1,
      "_id": 0
    }
  }
])

Demo Q2 @ Mongo Playground

Note: You attach too many questions into one. Please remember to split it into different questions (posts) when you ask a new question in the future.