mongodb - Find duplicates in Mongo DB array across multiple documents

Question

Is there a way to use the mongodb aggregation framework to find duplicates in document arrays?

Here is a simplified version of the box collection:

{_id : 1, name : 'Box 1', products : [
 {pId : 123, name : 'sneaker'},
 {pId : 456, name : 'pants'},
 {pId : 789, name : 'shirt'}
]},
{_id : 2, name : 'Box 2', products : [
 {pId : 123, name : 'sneaker'},
 {pId : 456, name : 'pants'},
 {pId : 111, name : 'socks'}
]},
{_id : 3, name : 'Box 3', products : [
 {pId : 123, name : 'sneaker'},
 {pId : 222, name : 'belt'},
 {pId : 333, name : 'shorts'}
]}

Lets say I want to check if the box 1 products are part of any other boxes. Can I use the aggregation framework like this:

box.aggregate([
 {$match: {'products.pId' : {$in :  [123, 456, 789]}},
 //  ??   //
 ]);

Ideally I am hoping for an output like this:

[{_id : 123, count : 3,
 {_id : 456, count : 2,
 {_id : 789, count : 1}]

I know this can be achieved by returning all docs and them looping over each products array but wanted to see if mongodb can export this directly.

Thanks!!

question from:https://stackoverflow.com/questions/65852118/find-duplicates-in-mongo-db-array-across-multiple-documents

Answer 1

You can do it in this way using aggregation:

• First $unwind to get every element in product array separated to can group them
• Second $group by products.pId and create a new field called count with the $sum for every one that exists.
• And the last step is $match the values you want.

db.collection.aggregate([
  {
    "$unwind": "$products"
  },
  {
    "$group": {
      "_id": "$products.pId",
      "count": {"$sum": 1}
    }
  },
  {
    "$match": {"_id": {$in: [123,456,789]}}
  },
  
])

Example here