Find duplicate values inside an array in Mongo DB but it can be present outside object

Question

{
    "_id" : ObjectId("15672"),
    "userName" : "4567",
    "library" : [ 
        {
            "serialNumber" : "Book_1"
        }, 
        {
            "serialNumber" : "Book_2"
        }, 
        {
            "serialNumber" : "Book_4"
        }
    ]
},
{
    "_id" : ObjectId("123456"),
    "userName" : "123",
    "library" : [ 
        {
            "serialNumber" : "Book_2"
        }
    ]
},
{
    "_id" : ObjectId("1835242"),
    "userName" : "13526",
    "library" : [ 
        {
            "serialNumber" : "Book_7"
        }, 
        {
            "serialNumber" : "Book_6"
        }, 
        {
            "serialNumber" : "Book_5"
        }, 
        {
            "serialNumber" : "Book_4"
        }, 
        {
            "serialNumber" : "Book_3"
        }, 
        {
            "serialNumber" : "Book_5"
        }
    ]
}

I want a query which will give me the username in which serialNumber values are duplicate. The serial number values in one library can be present in other username library but it should not be there in one particular username library

You can use the aggregation operator [$setUnion](https://docs.mongodb.com/manual/reference/operator/aggregation/setUnion/) on the `library` array field and this returns an array (call it `library_unique`) of unique elements. Then, compare the sizes (`$size` aggregation operator gives the arra's length) of the `library` and `library_unique`. If the sizes are _not_ equal that means there are duplicate elements in the `library` array for that document. — prasad_, Mar 13 '20 at 01:23

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-13T04:55:34.460

Try this query :

db.collection.aggregate([
    /** First match stage is optional if all of your docs are of type array & not empty */
    { $match: { $expr: { $and: [{ $eq: [{ $type: "$library" }, "array"] }, { $ne: ["$library", []] }] } } },
    /** Add a new field allUnique to each doc, will be false where if elements in library have duplicates */
    {
        $addFields: {
            allUnique: {
                $eq: [
                    {
                        $size:
                        {
                            $reduce: {
                                input: "$library.serialNumber",
                                initialValue: [], // start with empty array
                                /** iterate over serialNumber's array from library & push current value if it's not there in array, at the end reduce would produce an array with uniques */
                                in: { $cond: [{ $in: ["$$this", "$$value"] }, [], { $concatArrays: [["$$this"], "$$value"] }] }
                            }
                        }
                    },
                    {
                        $size: "$library"
                    }
                ]
            }
        }
    },
    /** get docs where allUnique: false */
    {
        $match: {
            allUnique: false
        }
    },
    /** Project only needed fields & remove _id which is bydefault projected */
    {
        $project: {
            userName: 1,
            _id: 0
        }
    }
])

Other option can be doing this through $unwind but which is not preferable on huge datasets as it explodes your collection.

Test : MongoDB-Playground

Or from answer of @Dennis in this link duplicate-entries-from-an-array , You can try as below :

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $eq: [
              {
                $type: "$library"
              },
              "array"
            ]
          },
          {
            $ne: [
              "$library",
              []
            ]
          }
        ]
      }
    }
  },
  {
    $addFields: {
      allUnique: {
        $eq: [
          {
            $size: {
              "$setUnion": [
                "$library.serialNumber",
                []
              ]
            }
          },
          {
            $size: "$library"
          }
        ]
      }
    }
  },
  {
    $match: {
      allUnique: false
    }
  },
  {
    $project: {
      userName: 1,
      _id: 0
    }
  }
])

Test : MongoDB-Playground

Thank you so much whoami but i forgot one point if library array is there no serialNumber key is there then still it will give me that username i don't want that userName can you update that condition also in your query, it will be very helpful to me — Abhishek K V, Mar 13 '20 at 06:32
@AbhishekKV : Try this :: https://mongoplayground.net/p/LUZowJ1h2Yr , instead of `{ $ne: [ "$library", [] ] }` add `{ $eq: [ { $type: "$library.serialNumber" }, "array" ] }` — whoami - fakeFaceTrueSoul, Mar 13 '20 at 06:58
@AbhishekKV : maybe instead of that type check, just check ‘library.serialNumber’ is not an empty array — whoami - fakeFaceTrueSoul, Mar 13 '20 at 08:56

Find duplicate values inside an array in Mongo DB but it can be present outside object

1 Answers1