Search code examples
jsonmongodbnullaggregation

why the aggregation query's result is null in mongodb?


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 }

Solution

  • query

    db.collection.aggregate([
        {
            $match: {}
        },{
            $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")
    }