CODEWITHSUNDEEP
mongodbmongodb-query
Sergio Cano
Sergio Cano

Reputation: 760

increment or subtract a field in every document mongodb using aggregation

I am trying to query my DB but i don't know how to perform this query... i am trying to know the stock of a product that i have in every momment per document

I have these documents...

{
    _id: "6sdgsd5614sd12",
    type: 'purchase',
    date: '2020-04-11T20:33:14.021+00:00',
    products: [{
            _id: "62360asadc2q622",
            price: 542,
            units: 9
        }]
},
{
    _id: "34gf435345345f",
    type: 'purchase',
    date: '2020-04-12T50:51:14.691+00:00'.
    products: [{
            _id: "62360asadc2q622",
            price: 542,
            units: 5
        }]
},
{
    _id: "543g456g3344433",
    type: 'sales'
    date: '2020-04-13T20:12:14.011+00:00'
    products: [{
            _id: "62360asadc2q622",
            price: 542,
            units: 4
       }]
 },

Now i would like to add a field in every document with the stock in that particular momment, for example, My first document is a purchase that i bought 9 units, so my stock is 9, in my second i bought 5 units more, so my stock is 14, and in the last i sold 4 so my final stock is 10. These documents are sorted by date.

My expected result is an output like this...

{
    _id: "6sdgsd5614sd12",
    type: 'purchase',
    date: '2020-04-11T20:33:14.021+00:00',
    products: [{
            _id: "62360asadc2q622",
            price: 542,
            units: 9,
            stock: 9
        }]
},
{
    _id: "34gf435345345f",
    type: 'purchase',
    date: '2020-04-12T50:51:14.691+00:00'.
    products: [{
            _id: "62360asadc2q622",
            price: 542,
            units: 5,
            stock: 14
        }]
},
{
    _id: "543g456g3344433",
    type: 'sales'
    date: '2020-04-13T20:12:14.011+00:00'
    products: [{
            _id: "62360asadc2q622",
            price: 542,
            units: 4,
            stock: 10
       }]
 },

I've been trying aggregation with $inc and $subtract but i dont have idea how to do it, after matching the product i am using $addFields but the operator $inc i think is not working in Aggregation... anyone know how can i do it? Help... Thankss

Upvotes: 1

Views: 338

Answers (1)

Joe
Joe

Reputation: 28316

The problem is that aggregation does not keep any state between documents, so when it comes time to examine that last sales document, there is not state variable around to tell you that there are 14 on hand.

You might be able to get what you are looking for by:

  • $sort by date so everything is in the right order
  • $unwind the products array, so each document has only 1 product
  • $group by product_id, adding the original documents to an array
  • $addFields using $reduce to traverse the array and give each element the inventory total
  • $unwind that array
  • $group by the original _id and $push the product records back into an array.

Upvotes: 2

Related Questions