mongodb complex aggregation nested conditional groupby

user_15636316 0
Viewed 26

I have an collection of orders, which look like this:

[
  {
    _id: ObjectID,
    date: Date,
    products: [
      {
        id: string,
        quantity: number
      },
      {
        id: string,
        quantity: number
      },
    ]
  }
]

I need to aggregate them so that I can determine, using a product.id, the quantity of each id for each date (not time)

My resultant data set would look something similar to this:

[
  { 
    date: "2021-01-1", 
    products: {
      // product_id: quantity
      PRODUCT_ID_1: 4
      PRODUCT_ID_2: 9
    } 
  }
]

Any ideas on how I can do this?

mongodb aggregation
1 Answers
  • $dateToString convert date to specific your required format
  • $unwind deconstruct products array
  • $group by id, date and sum quantity
  • $group by only date and construct products array in key-value format
  • $arrayToObject convert products array to object
db.collection.aggregate([
  {
    $addFields: {
      date: {
        $dateToString: {
          date: "$date",
          format: "%Y-%m-%d"
        }
      }
    }
  },
  { $unwind: "$products" },
  {
    $group: {
      _id: {
        date: "$date",
        id: "$products.id"
      },
      quantity: { $sum: "$products.quantity" }
    }
  },
  {
    $group: {
      _id: "$_id.date",
      products: {
        $push: {
          k: "$_id.id",
          v: "$quantity"
        }
      }
    }
  },
  {
    $project: {
      products: { $arrayToObject: "$products" }
    }
  }
])

Playground

user_89871280
Related