1

I would like to delete ($pull) nested array elements where one of the element's properties is null and where the array has more than one element.

Here is an example. In the following collection, I would like to delete those elements of the Orders array that have Amount = null and where the Orders array has more than one element. That is, I would like to delete only the element with OrderId = 12, but no other elements.

db.TestProducts.insertMany([
    {
        ProductDetails: { "ProductId": 1, Language: "fr" },
        Orders: [
            { "OrderId": 11, "Amount": 200 },
            { "OrderId": 12, "Amount": null }
        ]
    },
    {
        ProductDetails: { "ProductId": 2, Language: "es" },
        Orders: [
            { "OrderId": 13, "Amount": 300 },
            { "OrderId": 14, "Amount": 400 }
        ]
    },
    {
        ProductDetails: { "ProductId": 3, Language: "en" },
        Orders: [
            { "OrderId": 15, "Amount": null }
        ]
    }
]);

The following attempt is based on googling and a combination of a few other StackOverflow answers, e.g. Aggregate and update MongoDB

db.TestProducts.aggregate(
    [
        { $match: { "Orders.Amount": { "$eq": null } } },
        { $unwind: "$Orders" },
        {
            "$group": {
                "_id": {
                    ProductId: "$ProductDetails.ProductId",
                    Language: "$ProductDetails.Language"
                },"count": { "$sum": 1 }
            }
        },
        { "$match": { "count": { "$gt": 1 } } },
        { "$out": "temp_results" } 
    ],
    { allowDiskUse: true}
);


db.temp_results.find().forEach((result) => {
    db.TestProducts.updateMany({"ProductDetails.ProductId": result._id.ProductId, "ProductDetails.Language": result._id.Language }, 
        { $pull: { "Orders": {"Amount": null } }})
});

This works, but I am wondering if it can be done in a simpler way, especially if it is possible to delete the array elements within the aggregation pipeline and avoid the additional iteration (forEach).

turivishal
  • 34,368
  • 7
  • 36
  • 59
Developer
  • 435
  • 4
  • 16

2 Answers2

1

You can check these conditions in the update query, check 2 conditions

  • Amount is null
  • check the expression $expr condition for the size of the Orders array is greater than 1
db.TestProducts.updateMany({
  "Orders.Amount": null,
  "$expr": {
    "$gt": [{ "$size": "$Orders" }, 1]
  }
},
{
  "$pull": {
    "Orders": { "Amount": null }
  }
})

Playground

turivishal
  • 34,368
  • 7
  • 36
  • 59
-1

an example an example might help:

 let feed = await Feed.findOneAndUpdate(
      {
        _id: req.params.id,
        feeds: {
          $elemMatch: {
            type: FeedType.Location,
            locations: {
              $size: 0,
            },
          },
        },
      },
      {
        $pull: {
          feeds: { locations: { $size: 0 }, type: FeedType.Location },
        },
      },
      { new: true, multi: true }
    );
Rafiq
  • 8,987
  • 4
  • 35
  • 35