why the result is null? i have this as my collection, the result shoul be contain the product and the average of quantity, match by year.

this is my collection

{
    "order":"o1",
    "day": ISODate("2020-01-04T00:00:00.000Z"),
    "paid":"Y",
    "cost": {"price":30, "currency":"USD"},
    "items":[{"product":"p1", "colours":["blue","black"], "quantity":15}],
   "delivery_days":5
},
{
    "order":"o2",
    "day": ISODate("2020-01-22T00:00:00.000Z"),
    "paid":"Y",
    "cost": {"price":13, "currency":"EUR"},
    "items":[{"product":"p2","colours":["white"],"quantity":4},
               {"product":"p3","colours":["white","black"],"quantity":1}],
   "delivery_days":4
},
{
   "order":"o3","day": ISODate("2018-10-17T00:00:00.000Z"),
   "paid":"N",
   "cost":{"price":33,"currency":"EUR"},
   "items":[{"product":"p3","colours":["blue","black"],"quantity":4}], 
   "delivery_days":4
}

this is my code

db.UAS0456.aggregate([
 {
   $project:
     {
      order:"$order",
      year:{$year:"$day"},
      items:"$items",
      product:"$item.product",
      quantity:"$item.quantity",
     }
 },
 {
  $match:
    {
    "year":{$gte:2018},
    "year":{$lte:2020}
    }
 },
 {
  $group:
    {
     _id:"$product",
     averageOfQuantity:
       {
         $avg: "$items.quantity"
       }
     }
  }
 ]);

and this is the result. how to get the result? it should be many product.

{ "_id" : null, "averageOfQuantity" : null }

CodePudding user response：

query

db.collection.aggregate([
    {
        $match: {
            "$expr": {
                $and: [
                    {
                        "$gte": [{ "$year": "$day" }, 2018]
                    }, {
                        "$lte": [{ "$year": "$day" }, 2020]
                    }
                ]
            }
        }
    },{
        $project: {
            year: { $year: '$day' },
            items: '$items'
        }
    }, {
        $unwind: {
            path: '$items'
        }
    }, {
        $group: {
            _id: {
                y: '$year',
                p: '$items.product'
            },
            result: {
                $avg: '$items.quantity'
            }
        }
    }, {
        $project: {
            _id: 0,
            year: '$_id.y',
            product: '$_id.p',
            avg: '$result'
        }
    }
])

result

{
    "year" : 2020,"product" : "p1","avg" : Double("15")
},   
{
    "year" : 2020,"product" : "p2","avg" : Double("4")
}, 
{
    "year" : 2020,"product" : "p3","avg" : Double("1")
},
{
    "year" : 2018,"product" : "p3","avg" : Double("4")
}