Mongoose query to group by date

Question

I am pretty new to mongoose aggregation and I have the collection in the below format

Collection Documents:

{
  "movie":"Fast and Furious",
  "rating":"Good",
  "ratingTime":"2015-3-06 4:05:10"
},
{
  "movie":"Avengers Infinity Stones",
  "rating":"Very Good",
  "ratingTime":"2020-20-4 22:10:40"
},
{
  "movie":"Mad Max Fury Road",
  "rating":"Average",
  "ratingTime":"2015-3-06 15:23:25"
},
{
  "movie":"Toy story",
  "rating":"Good",
  "ratingTime":"2020-20-4 10:11:02"
}

I want it in the below format :

[
   {
       "2015-3-06":[{
            "movie":"Fast and Furious",
            "rating":"Good",
            "ratingTime":"2015-3-06 4:05:10"
        },{
            "movie":"Mad Max Fury Road",
            "rating":"Average",
            "ratingTime":"2015-3-06 15:23:25"
        }]
   },
   {
        "2020-20-4":[{
            "movie":"Avengers Infinity Stones",
            "rating":"Very Good",
            "ratingTime":"2020-20-4 22:10:40"
        },{
            "movie":"Toy story",
            "rating":"Good",
            "ratingTime":"2020-20-4 10:11:02"
        }]
   }
]

Can somebody please help me as I am not getting any ideas to get the output in desired format. Really appreciate for the help

score 1 · Answer 1 · answered Jul 11 '20 at 20:06

In your situation, as far as I understood you want to use dynamic schema keys. But I think it is a bad idea, better you can use the first option and sort them by date, or If you really want to use it you can do it using {string: false}. Using strict makes your entire schema free-form, if you don't want to make the entire schema free-form, but a certain portion of it, you can also modify your schema to use mixed

   var movieSchema=newSchema({
       movie:{
          type:String
       },
       rating:{
          type:String
       },
       ratingTime:{
          type:Date,
          default:Date.now()
       }
  })

// var movieSchemaByDate = new Schema({..}, { strict: false }); /*if you are using older versions */
    var movieSchemaByDate = new Schema({..}) 
    var Movie = mongoose.model('Movie', movieSchemaByDate);
    var movie = new Movie({ [date]: movieSchema });
    movie.save();

Reference :

mongoose strict

Classify mongoose schema arrays by date

score 0 · Answer 2 · answered Jul 11 '20 at 19:19

My solution to your problem would be of this type:

var mongoose = require('mongoose');
var Schema = mongoose.Schema;

var movieSchema=newSchema(
{
    movie:{
        type:String
    },
    rating:{
        type:String
    },
    ratingTime:{
        type:Date,
        default:Date.now()
    }
}
)

var yearSchema = new Schema({
    movieYear:[movieSchema]
});
var books=mongoose.model('books',bookSchema)
module.exports=books;

You can read more about Mongoose Subdocuments and Mongoose Schemas.

score 0 · Answer 3 · answered Jul 11 '20 at 20:07

    const aggregate = await Test.aggregate([
      {
        $group: {
          _id: {
            $dateToString: {
              format: '%Y-%m-%d',
              date: '$ratingTime'
            }
          },
          root: {
            $addToSet: '$$ROOT'
          }
        }
      },
      {
        $unwind: {
          path: '$root',
          preserveNullAndEmptyArrays: false
        }
      },
      {
        $group: {
          _id: '$_id',
          movies: {
            $addToSet: '$root'
          }
        }
      }
    ]);
    aggregate.forEach((dateObj) => {
      dateObj[dateObj._id] = dateObj.movies;
      delete dateObj._id;
      delete dateObj.movies;
    });
    console.log(JSON.stringify(aggregate, undefined, 3));

and since dynamic field naming is not possible in aggreagation (at least i think so) you need to do the rest after getting the results.

and output will be:

    [
      {
        '2015-07-03': [
          {
            _id: '5f0a11833456a62a2a91e36a',
            movie: 'Mad Max Fury Road',
            rating: 'Average',
            ratingTime: '2015-07-03T12:23:25.000Z',
            __v: 0
          },
          {
            _id: '5f0a11833456a62a2a91e368',
            movie: 'Fast and Furious',
            rating: 'Good',
            ratingTime: '2015-07-03T12:23:25.000Z',
            __v: 0
          }
        ]
      },
      {
        '2020-05-20': [
          {
            _id: '5f0a11833456a62a2a91e36b',
            movie: 'Toy story',
            rating: 'Good',
            ratingTime: '2020-05-20T07:11:02.000Z',
            __v: 0
          },
          {
            _id: '5f0a11833456a62a2a91e369',
            movie: 'Avengers Infinity Stones',
            rating: 'Very Good',
            ratingTime: '2020-05-20T19:10:40.000Z',
            __v: 0
          }
        ]
      }
    ];

and links to take a look group by dates in mongodb

https://www.mongodb.com/products/compass

Darvesh · Answer 4 · 2020-07-12T20:37:55.570

As Eric Asca mentioned in one of the answer, dynamic field naming isn't possible. You can group the documents by date like this. There is one thing to notice that the date format in the given data seem invalid which is why I'm using $substr.

const result = await Model.aggregate([
    {
        $group: {
            _id: { $substr: ["$ratingTime", 0, 9] },
            movies: {
                $push: "$$ROOT",
            },
        },
    },
]);

The result would look like this.

[
  {
    "_id": "2020-20-4",
    "movies": [
      {
        "_id": "5f0a27a5df68ee25952ae10a",
        "movie": "Avengers Infinity Stones",
        "rating": "Very Good",
        "ratingTime": "2020-20-4 22:10:40",
        "__v": 0
      },
      {
        "_id": "5f0a27a5df68ee25952ae10c",
        "movie": "Toy story",
        "rating": "Good",
        "ratingTime": "2020-20-4 10:11:02",
        "__v": 0
      }
    ]
  },
  {
    "_id": "2015-3-06",
    "movies": [
      {
        "_id": "5f0a27a5df68ee25952ae109",
        "movie": "Fast and Furious",
        "rating": "Good",
        "ratingTime": "2015-3-06 4:05:10",
        "__v": 0
      },
      {
        "_id": "5f0a27a5df68ee25952ae10b",
        "movie": "Mad Max Fury Road",
        "rating": "Average",
        "ratingTime": "2015-3-06 15:23:25",
        "__v": 0
      }
    ]
  }
]

This can easily be transformed to the format you want.

const output = result.map(({_id, movies}) => ({[_id]: movies}))

Mongoose query to group by date

4 Answers4