How to remove documents nested within2 arrays

emmanuel_bernard · 2024-08-11T17:19:40.204Z

Hi there,

I’m trying to delete elements in an array if they appear multiple times.
In the example, I have underlined the 2 elements I want to delete.

I tried with UpdateMany and $pull, but I can’t identify the duplicates.

Thanks

Jack_Woehr · 2024-08-11T21:54:59.789Z

So your problem is doing searches for documents with duplicates in markets.salePeriods.panel.storeSegmentations … focus on that and then afterwards think about the $pull

emmanuel_bernard · 2024-08-12T09:43:26.730Z

Morning Jack,

With $unwind and $group stages, I can identify the duplicates.
Is this the best way to do it?

[
  {
    $unwind: "$markets"
  },
  {
    $unwind: "$markets.salePeriods"
  },
  {
    $unwind:
      "$markets.salePeriods.panel.storeSegmentations"
  },
  {
    $group: {
      _id: {
        docId: "$_id",
        marketId: "$markets.marketId",
        salePeriodId:
          "$markets.salePeriods.salePeriodId",
        storeSegmentationId:
          "$markets.salePeriods.panel.storeSegmentations.segmentationType"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $match: {
      count: {
        $gt: 1
      }
    }
  },
  {
    $group: {
      _id: {
        docId: "$_id.docId",
        marketId: "$_id.marketId",
        salePeriodId: "$_id.salePeriodId"
      },
      duplicateStoreSegmentations: {
        $push: "$_id.storeSegmentationId"
      }
    }
  },
  {
    $group: {
      _id: {
        docId: "$_id.docId",
        marketId: "$_id.marketId"
      },
      duplicateSalePeriods: {
        $push: {
          salePeriodId: "$_id.salePeriodId",
          duplicateStoreSegmentations:
            "$duplicateStoreSegmentations"
        }
      }
    }
  },
  {
    $group: {
      _id: "$_id.docId",
      duplicateMarkets: {
        $push: {
          marketId: "$_id.marketId",
          duplicateSalePeriods:
            "$duplicateSalePeriods"
        }
      }
    }
  }
]

Jack_Woehr · 2024-08-12T13:17:59.553Z

I would think so. Looks neat and tidy!

emmanuel_bernard · 2024-08-12T15:35:55.744Z

I want to avoid the $unwind stages which are resource-intensive. I have tried using the updateMany statement with $arrayFilters.

But don’t succeed

Here is a document example

{
  _id: ObjectId('66ba22ed128efe0e16f91fab'),
  markets: [
    {
      marketId: '1',
      salePeriods: [
        {
          salePeriodId: 'a',
          panel: {
            storeSegmentations: [
              {
                segmentationType: 'A',
                delete: false
              },
              {
                segmentationType: 'B',
                delete: false
              }
            ]
          }
        },
        {
          salePeriodId: 'b',
          panel: {
            storeSegmentations: [
              {
                segmentationType: 'A',
                delete: false
              },
              {
                segmentationType: 'A',
                delete: false
              },
              {
                segmentationType: 'B',
                delete: false
              }
            ]
          }
        }
      ]
    },
    {
      marketId: '2',
      salePeriods: [
        {
          salePeriodId: 'a',
          panel: {
            storeSegmentations: [
              {
                segmentationType: 'A',
                delete: false
              },
              {
                segmentationType: 'B',
                delete: false
              },
              {
                segmentationType: 'B',
                delete: false
              }
            ]
          }
        }
      ]
    }
  ]
}

db.test.updateMany(
  {},
  {
    $set: {
      "markets.$[].salePeriods.$[p].panel.storeSegmentations.$[s].delete": true
    }
  },
  {
    arrayFilters: [
      {
        "p.panel.storeSegmentations": {
          $ne: [
            {
              $size: {
                $filter: {
                  input: "$$p.panel.storeSegmentations",
                  as: "storeSegmentation",
                  cond: {
                    $eq: ["$$storeSegmentation.segmentationType", "$$s.segmentationType"]
                  }
                }
              }
            },
            1
          ]
        }
      },
      {
        "s.segmentationType": { $exists: true }
      }
    ]
  }
);

John_Page · 2024-08-12T15:48:12.577Z

Hi Emmanuel,
You can’t do this with something as simple as a push nor can you efficient query for duplicates so it’s not worth trying that. You need to use an Expressive update to define what markets looks like relative to it’s current value.

Also - as an aisde you shoudl never $unwind then $group by _id, this is a pathological worst case for performance and a common huge anti-pattern.

I’ve made two assumptions here
(1) You want this to work for not just the first market or first sale period but all of them
(2) This may be wrong but I assum we can just convert storeSegmentations array to a set.

If (2) isn’t true then you would need to figure out logic for which object to keep if you have a duplicate segmentationType in an array and apply that using $reduce.

Anyway - here is the example code, I’ve broken it down to define each clause seperately - if you really want ot look at the whole thing then print out the value of expressiveUpdate.


db.a.drop()

var record = {
    _id : "205734",
    productId: "205734",
    markets : [
        {
            salePeriods : [
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"},
                            { segmentationType: "SUPPLY_SIZES"},
                        ]
                    }
                },
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"},
                            { segmentationType: "SUPPLY_SIZES"},
                        ]
                    }
                }
            ]
        },
        {
            salePeriods : [
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"},
                            { segmentationType: "SUPPLY_SIZES"},
                        ]
                    }
                }
            ]
        }
    ]
}

db.a.insertOne(record);
var setOfSegmentationTypes = { $setUnion : [  "$$salePeriod.panel.storeSegmentations"  ]}
var newSalesPeriod = {panel : { storeSegmentations : setOfSegmentationTypes }}
var allSalePeriods = { $map : { input : "$$market.salePeriods", as:  "salePeriod", in : newSalesPeriod }}
var allMarkets = { $map : { input :"$markets", as: "market" , in: allSalePeriods }}
var expressiveUpdate = [ { $set : { markets : allMarkets }} ]
db.a.updateMany({},expressiveUpdate)
db.a.find();

emmanuel_bernard · 2024-08-12T16:14:39.954Z

Hi John,

My use case is more complex.
The duplicates are not always the same and I want to remove all items that appear more than once.

With this document :

{
    _id : "205734",
    productId: "205734",
    markets : [
        {
            salePeriods : [
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"},
                            { segmentationType: "SUPPLY_SIZES"},
                        ]
                    }
                },
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"},
                        ]
                    }
                }
            ]
        },
        {
            salePeriods : [
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"},
                        ]
                    }
                }
            ]
        }
    ]
}

Result should be :

{
    _id : "205734",
    productId: "205734",
    markets : [
        {
            salePeriods : [
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"}
                        ]
                    }
                },
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "C.WEB"},
                            { segmentationType: "SUPPLY_SIZES"}
                        ]
                    }
                }
            ]
        },
        {
            salePeriods : [
                {
                    panel: {
                        storeSegmentations :[
                            { segmentationType: "SUPPLY_SIZES"}
                        ]
                    }
                }
            ]
        }
    ]
}```

John_Page · 2024-08-12T16:32:07.818Z

And are there other fields in there as well as Segmentation type - so you want ot remove any Object in the array if the segmentationType field is not unique , is that correct?

John_Page · 2024-08-12T16:37:20.768Z

The code to iterate of the arrays of arrays stays the same, you jus t need to write an expression that instead of a simple setUnion returns an array with any duplicate elements removed. That shouldn’t be too difficult - Not sure if I’ll have an opportunity to try it though as traveling for a while.

emmanuel_bernard · 2024-08-13T07:41:58.015Z

Hi Jack,
I want to remove any Object in the array if the segmentationType field is not unique.

emmanuel_bernard · 2024-08-13T12:41:41.477Z

Yes, that is corret.
I want to remove any Object in the array if the segmentationType field is not unique.

emmanuel_bernard · 2024-08-14T13:03:32.834Z

I use $map aggregation operator to iterate arrays of arrays of arrays (3 levels).
It’s a bit complicated query but it’s fine