mongodb / mongoose subdocument aggregation query

Question

i need to get this output from my schema using mongoose aggregation framework

my schema

const innerSchema = mongoose.Schema({
  responseTime: Number,
  day: String,
  hour: Number
})

let logsSchema = mongoose.Schema({
  name: { type: String, enum: ['visit', 'new-request', 'new-customer'] },
  series: { type: [innerSchema], default: [] }
})

the output that i need is the following

        [{
            "name":"visit",
            "series": [
                {
                    "day": "Saturday",
                    "count": 50
                },
                {
                    "day": "Friday",
                    "count": 20
                }
            ]
         },
         {
            "name":"new-request",
            "series": [
                {
                    "day": "Saturday",
                    "count": 100
                },
                {
                    "day": "Friday",
                    "count": 4
                }
            ]
         }]

Currently stuck with this aggregation query

  Logs.aggregate([
    {
      $group: {
        '_id': '$name',
        series: { $first: '$series' }
      }
    },
    { $unwind: '$series' },
    {
      "$group": {
        "_id": '$series.day',
        count: { $sum: 1 }
      }
    },
    { $limit: 7 }

  ])

that outputs the following :

[
    {
        "_id": "Saterday",
        "count": 1
    },
    {
        "_id": "Friday",
        "count": 1
    },
    {
        "_id": "Sunday",
        "count": 5
    }
]

it misses the name&series fields

the series array i need it to be limited to top 7 docs to get a week of data

any help is appreciated

NEW ---> UPDATE

this is the original data set

updated it to a simple structure

      [
    {
        "_id": "5ea1770c165ece5a40af06ea",
        "name": "new-request",
        "day": "Saturday",
        "hour": 14,
        "createdAt": "2020-04-23T11:07:56.175Z",
        "updatedAt": "2020-04-23T11:07:56.175Z",
        "__v": 0
    },
    {
        "_id": "5ea17770165ece5a40af06eb",
        "name": "new-request",
        "day": "Thursday",
        "hour": 14,
        "createdAt": "2020-04-23T11:09:36.364Z",
        "updatedAt": "2020-04-23T11:09:36.364Z",
        "__v": 0
    },
    {
        "_id": "5ea17770165ece5a40af06ec",
        "name": "new-customer",
        "day": "Thursday",
        "hour": 14,
        "createdAt": "2020-04-23T11:09:36.984Z",
        "updatedAt": "2020-04-23T11:09:36.984Z",
        "__v": 0
    },
    {
        "_id": "5ea17771165ece5a40af06ed",
        "name": "visit",
        "day": "Thursday",
        "hour": 14,
        "createdAt": "2020-04-23T11:09:37.603Z",
        "updatedAt": "2020-04-23T11:09:37.603Z",
        "__v": 0
    },
    {
        "_id": "5ea17772165ece5a40af06ee",
        "name": "visit",
        "day": "Thursday",
        "hour": 14,
        "createdAt": "2020-04-23T11:09:38.207Z",
        "updatedAt": "2020-04-23T11:09:38.207Z",
        "__v": 0
    },
    {
        "_id": "5ea17772165ece5a40af06ef",
        "name": "visit",
        "day": "Saturday",
        "hour": 14,
        "createdAt": "2020-04-23T11:09:38.698Z",
        "updatedAt": "2020-04-23T11:09:38.698Z",
        "__v": 0
    },
    {
        "_id": "5ea17773165ece5a40af06f0",
        "name": "visit",
        "day": "Thursday",
        "hour": 14,
        "createdAt": "2020-04-23T11:09:39.247Z",
        "updatedAt": "2020-04-23T11:09:39.247Z",
        "__v": 0
    },
    {
        "_id": "5ea2dd44030d853950379007",
        "name": "visit",
        "day": "Friday",
        "hour": 15,
        "createdAt": "2020-04-24T12:36:20.867Z",
        "updatedAt": "2020-04-24T12:36:20.867Z",
        "__v": 0
    },
    {
        "_id": "5ea2dd56030d853950379008",
        "name": "visit",
        "day": "Friday",
        "hour": 15,
        "createdAt": "2020-04-24T12:36:38.297Z",
        "updatedAt": "2020-04-24T12:36:38.297Z",
        "__v": 0
    },
    {
        "_id": "5ea2dd58030d853950379009",
        "name": "visit",
        "day": "Friday",
        "hour": 15,
        "createdAt": "2020-04-17T00:36:40.583Z",
        "updatedAt": "2020-04-24T12:36:40.583Z",
        "__v": 0
    },
    {
        "_id": "5ea2dd58030d85395037900a",
        "name": "visit",
        "day": "Friday",
        "hour": 15,
        "createdAt": "2020-04-24T12:36:40.878Z",
        "updatedAt": "2020-04-24T12:36:40.878Z",
        "__v": 0
    }
]

with the help of @AlexZeDim and some adjustments i managed to get it to work but i got one last issue with data sorting i need the data to be sorted by the createdAt

Logs.aggregate([
    {
      $group: {
        _id: '$name',
        series: { "$push": "$$ROOT" }
      }
    },
    {
      $unwind: "$series"
    },
    {
      $addFields: {
        createdAt: '$series.createdAt'
      }
    },

    { $match: { createdAt: { $gt: lastWeekDate, $lt: date } } },
    {
      $group: {
        _id: {
          name: "$_id",
          day: "$series.day"
        },
        count: {
          $sum: 1
        }
      }
    },

    {
      $group: {
        _id: "$_id.name",
        series: {
          $addToSet: {
            name: "$_id.day",
            value: {
              $sum: "$count"
            }
          }
        },

      }
    },
    {
      $addFields: {
        createdAt: '$createdAt'
      }
    },

    {
      $project: {
        _id: 0,
        name: "$_id",
        series: "$series"
      }
    }
  ])

Issue solved

Please, provide the original exmple/set of data from the collection — AlexZeDim
– AlexZeDim, Commented Apr 20, 2020 at 9:49
Also, I'm not sure about .limit part. If your series.day field have value of current DayOfTheWeek the simple group stage will give you exaclty what you need, because there are 7 different days in the week. And when you limit(7) you just showing the last 7 values, not 7 unique days of the week. — AlexZeDim
– AlexZeDim, Commented Apr 20, 2020 at 9:52
You can try using the $push operator instead of the $first with the first $group stage. Check the results after the first group stage. — prasad_
– prasad_, Commented Apr 20, 2020 at 9:52
by limit i ment that i need the data for last 7 days not all days , aslo i added the original data set , thanks for the reply @AlexZeDim — Tarik Husin
– Tarik Husin, Commented Apr 20, 2020 at 10:25

AlexZeDim · Accepted Answer · 2020-04-20 10:59:28Z

1

In the original dataset: it's not "Saterday", it's «Saturday». If you really want/need { $limit: 7 } stage, you could always add it by yourself in the end of this query, it gives exactly what you need:

db.collection.aggregate([
  {
    $unwind: "$series"
  },
  {
    $group: {
      _id: {
        name: "$_id",
        day: "$series.day"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: "$_id.name",
      series: {
        $addToSet: {
          day: "$_id.day",
          count: {
            $sum: "$count"
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      name: "$_id",
      series: "$series"
    }
  }
])

MongoPlayground

answered Apr 20, 2020 at 10:59

AlexZeDim

4,4424 gold badges34 silver badges79 bronze badges

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

5 Comments

Tarik Husin Over a year ago

cool i appreciate you'r help ,but the limit dosent work i guess i should add a createdAt field to the series records ? , also im using mongoose and in the name field it projects the doc _id , thanks again

AlexZeDim Over a year ago

Well, you could remove $project stage if you want, and you'll have _id: for visits/new_requests values instead of name As for using timestamps: true in your Schema, it a nice idea, but remember how mongo stores date. If so, you could convert date field by special operators like $dayOfTheWeek (or smth like that) each time, to receive current day.

Tarik Husin Over a year ago

Also if i can get a week of data where if there is 2 days that has no visit return in the count 0 instead of not showing it at all

AlexZeDim Over a year ago

If you want to remove count: 0 field, then you'll need to use $cond operator, for count: {$eq: 0}

Tarik Husin Over a year ago

thank you for pointing me to the right direction , i've manged to solve the issue :D

Collectives™ on Stack Overflow

mongodb / mongoose subdocument aggregation query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related