const OrderProductSchema = new Schema<TOrderDetailsSchema>({
  orderLimit: Number,
  productId: { type: Schema.Types.ObjectId, ref: 'Product', required: true },
  amount: Number
});

/* OrderSchema correspond to a Orders collection in MongoDB. */
const OrderSchema = new Schema<TOrderSchema>({
  vendor: String,
  productsInOrder: [OrderProductSchema]
});

/* order products */
const orderProduct1 = {
  orderLimit: 5,
  productId: 'some-object-id',
  amount:  4
};

const orderProduct2 = {
  orderLimit: 3,
  productId: 'some-other-object-id',
  amount:  3
};

const orderProduct3 = {
  orderLimit: 8,
  productId: 'some-another-other-object-id',
  amount: 5
};

/* orders */
const order1 = {
  vendor: 'first-vendor',
  productsInOrder: [orderProduct1, orderProduct2, orderProduct3]
};

const order2 = {
  vendor: 'second-vendor',
  productsInOrder: [/* some not listed orderProducts */]
};

I have a use case where I need to:

1. find an order where subdocument OrderProduct with amount greater than or equal to orderLimit exists
2. if it exists do nothing
3. if it doesn't find an order subdocument OrderProduct with amount less than orderLimit and increase amount by 1
4. if there's no such order but there's an order with the same vendor, create a new OrderProduct and push it to productsInOrder
5. if there's no order, create new order, create OrderProduct and add it to array.

So when adding another orderProduct1 to order1, orderProduct1s amount should increase by 1.

When adding orderProduct2 to order1 nothing should happen because its amount is equal to its orderLimit

When adding a new product to order2 OrderProduct should be created and added to productsInOrder array.

I thought I can do all this if-order-limit-do-nothing-else-if-exist-update in a single aggregation but I can't figure out how to use $merge conditionally or if it's even possible.

So what would be the most optimal way to achieve that?

PS. $merge does not return updated document in Nodejs driver, all it returns is an empty array whether it's successfull or discarded!

Playground

CodePudding user response：

You can do this all with a single update using the aggregation pipeline update framework, like so:

const newProduct = {
  orderLimit: 3,
  productId: 'some-other-object-id',
  amount:  3
};


db.collection.findOneAndUpdate({
  "vendor": "Vendor 1"
},
[
  {
    $set: {
      productsInOrder: {
        $cond: [
          {
            $in: [
              newProduct.productId,
              {
                $ifNull: [
                  "$productsInOrder.productId",
                  []
                ]
              }
            ]
          },
          // if product exists
          {
            $map: {
              input: "$productsInOrder",
              in: {
                $cond: [
                  {
                    $and: [
                      {
                        $eq: [
                          "$$this.productId",
                          newProduct.productId
                        ]
                      },
                      {
                        $lt: [
                          {
                            $toInt: "$$this.amount"
                          },
                          {
                            $toInt: "$$this.orderLimit"
                          }
                        ]
                      }
                    ]
                  },
                  {
                    $mergeObjects: [
                      "$$this",
                      {
                        amount: {
                          $sum: [
                            1,
                            {
                              $toInt: "$$this.amount"
                            }
                          ]
                        }
                      }
                    ]
                  },
                  // do nothing
                  "$$this",
                ]
              },
              
            }
          },
          // product does not exist
          {
            $concatArrays: [
              {
                $ifNull: [
                  "$productsInOrder",
                  []
                ]
              },
              [
                newProduct
              ]
            ]
          }
        ]
      }
    }
  }
],
{
  upsert: true
})

Mongo Playground