How do i query properly to get the statistical data I need with current data format? (mongoDB / NoSQL)

user_16935176 0
Viewed 37

I have been struggling to find a way to get the statistical data that I need to share with other department.

this is a current document example in MongoDB.

{
date : ‘2021-09-10T12:00:00+09:00’,
Category : ‘usedCar’,
Data : [
    {name : ‘bmw’, theNumberOfSold : 15},
    {name : ‘Honda’, theNumberOfSold : 35},
    {name : ‘Toyota’, theNumberOfSold : 100}…….
] 
}

{
date : ‘2021-09-11T12:00:00+09:00’,
Category : ‘newCar’,
Data : [
    {name : ‘bmw’, theNumberOfSold : 5},
    {name : ‘Honda’, theNumberOfSold : 8},
    {name : ‘Toyota’, theNumberOfSold : 150}…….
] 
}

I'd like to get the result as follows. Would it be possible by mongodb aggregate query? I've tried so many ways the whole day but wouldn't get any..

-2021 Sep, usedCar list by name sorted by theNumberOfSold -2021 Jan~July, newCar list by name sorted by theNumberOfSold

the way I am going to utilize extracted data

mongodb nosql
1 Answers
  • $unwind unwind data
  • $group group by month、year、name
db.collection.aggregate([
  {
    $unwind: "$Data"
  },
  {
    $group: {
      _id: {
        year: {
          "$year": "$date"
        },
        month: {
          "$month": "$date"
        },
        name: "$Data.name"
      },
      totalAmount: {
        $sum: "$Data.theNumberOfSold"
      }
    }
  }
])

mongoplayground

user_166952450
Related