Check duplicates of certain field for documents array with inner array

Question

I have 2 objects,

  {   
     _id: ObjectId("5cd9010310b80b3e38cd3f88") 
     subGroup: [
       bookList: [ 
         {
            title: "A good book",
            id: "abc123"
         }
       ]    
      ] 
  }

{    
     _id: ObjectId("5cd9010710b80b3e38cd3f89") 
     subGroup: [
       bookList: [ 
         {
            title: "A good book",
            id: "abc123"
         }
       ]

These are 2 different objects. I would like to detect the occurence of these 2 objects where the title is duplicated (eg the same).

I tried this query

 db.scope.aggregate({"$unwind": "$subGroup.bookList"}, {"$group" : { "_id": "$title", "count": { "$sum": 1 } } }, {"$match": {"id" :{ "$ne" : null } , "count" : {"$gt": 1} } })

which i looked at other threads on stackoverflow. However, it does not return me anything. How can i solve this?

mickl · Accepted Answer · 2019-05-13 06:20:35Z

1

There are few issues here:

$unwind should be run on subGroup and on subGroup.bookList separately
when specifying _id for $group stage you should use full path (subGroup.bookList.title)
in your $match stage you want to check if _id (not id) is $ne null

Try:

db.col.aggregate([
    {"$unwind": "$subGroup"}, 
    {"$unwind": "$subGroup.bookList"},
    {"$group" : { "_id": "$subGroup.bookList.title", "count": { "$sum": 1 } } },
    {"$match": { "_id" :{ "$ne" : null } , "count" : { "$gt": 1} } } 
])

Mongo playground

answered May 13, 2019 at 6:20

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check duplicates of certain field for documents array with inner array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related