Query for returning documents having specific date range number then order them then sort

user_12584882 0
Viewed 27

I have a Song schema that looks like:

const songSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
  },
  URL: {
    type: String,
    required: true,
  },
  playedOn: [mongoose.Schema.Types.Date],
});

And in the database I have values such as:

{
        "_id" : ObjectId("6013e57d909d5252e869eac0"),
        "playedOn" : [
                ISODate("2021-01-29T10:41:55.444Z"),
                ISODate("2021-01-29T10:41:55.447Z"),
                ISODate("2021-01-29T10:41:55.448Z"),
                ISODate("2021-01-29T10:41:55.450Z"),
                ISODate("2021-01-29T10:41:55.451Z"),
                ISODate("2021-01-29T10:41:55.452Z")
        ],
        "name" : "Ariana Grande - Positions",
        "URL" : "https://firebasestorage.googleapis.com/v0/b/reactnativeauth-sj.appspot.com/o/003.%20Ariana%20Grande%20-%20positions.mp3?alt=media&token=d206e095-6360-46c4-ad1e-40a9204ca9d0",
        "__v" : 1
}
{
        "_id" : ObjectId("6013e57d909d5252e869eac1"),
        "playedOn" : [
                ISODate("2021-01-29T10:41:53.708Z")
        ],
        "name" : "Dua Lipa - Break My Heart",
        "URL" : "https://firebasestorage.googleapis.com/v0/b/reactnativeauth-sj.appspot.com/o/005.%20Dua%20Lipa%20-%20Break%20My%20Heart.mp3?alt=media&token=7feea778-761c-4e0e-8840-6f60e7795651",
        "__v" : 1
}

I want to first get the songs played most in the last 7 days (say top 10) sorted in descending order by the number of plays, The result be like:

{name: 'Positions', _id: '39s39s', numberOfPlays: 9}
{name: 'Irresistible', _id: '39s39s', numberOfPlays: 5}
{name: 'Closer', _id: '39s39s', numberOfPlays: 3}

I have tried things like:

db.songs.find({playedOn: {elemMatch: {$lt: ISODate()}}}, {_id: 1, playedOn: 1, name: 1}).pretty();

db.songs.find({playedOn: {$lt: ISODate() , $gte: ISODate("2021-01-28T09:20:37.732Z")}}, {_id: 1, playedOn: 1, name: 1}).pretty();

And many different things, but I just can't figure out the query that I should write for the same. I habe also tried using aggregate, but in vain. Please give a direction as to how it can be done.

mongoose mongodb schema arrays database
1 Answers

Using aggregate(),

  • $match start date and end date in $elemMatch
  • $filter to get filtered date from array
  • $size to get size of returned elements from $filter
  • $sort by numberOfPlays in descending order
  • $limit to return 10 documents
// start date and end date set your logic as per your requirement
let endDate = new Date();
let startDate = new Date(endDate);
startDate.setDate(startDate.getDate() - 7);

db.songs.aggregate([
  {
    $match: {
      playedOn: {
        $elemMatch: {
          $gte: startDate,
          $lt: endDate
        }
      }
    }
  },
  {
    $project: {
      name: 1,
      numberOfPlays: {
        $size: {
          $filter: {
            input: "$playedOn",
            cond: {
              $and: [
                { $gte: ["$$this", startDate] },
                { $lt: ["$$this", endDate] }
              ]
            }
          }
        }
      }
    }
  },
  { $sort: { numberOfPlays: -1 } },
  { $limit: 10 }
])

Playground

Using find() Starting in MongoDB v4.4, as part of making projection consistent with aggregation’s $project stage,

  • for limit and sort try .sort("-numberOfPlays").limit(10) end of the query

Playground

user_89871280
Related