CODEWITHSUNDEEP
mongodbaggregation-framework
Dmitry Samoylov
Dmitry Samoylov

Reputation: 1318

MongoDB: how do I check that all array entries are unique in the entire collection?

A little brainteaser for mongo users.

I have a collection of documents like

{
    "_id" : ObjectId("19628f4f0545a733185b672f"),
    "name" : "hello",
    "items" : [ 
        {
            "itemNumber" : 12512,
            "value" : "let"
        }, 
        {
            "itemNumber" : 2546,
            "value" : "put"
        }
    ]
}

I need to make sure that every item's itemNumber is unique globally in the collection.

In SQL database I would have a separate table for items and the query for checking if numbers are unique would be something like

select count(1) 
from (
   select itemNumber, count(itemNumber) as cnt 
   from items 
   group by itemNumber) sel 
where cnt>1;

Resulting 0 would mean that all itemNumbers are unique. (Probably there are better ways to make that check in SQL)

With MongoDB the only solution that I can come to is

a) use forEach to extract all items to separate collection

b) make a simple aggregation 

db.items.aggregate(
    { $group : { _id : '$itemNumber', count : {$sum : 1} } },
    { $out : "cnt" }
)

c) db.cnt.find({count: {$gt: 1}}).count()

Is there any one-query way to do it?

Performace notice: the collection is about 3M documents, 2,2KB each. I have noticed that aggreations that contain $group run like forever on this collection.

Upvotes: 0

Views: 47

Answers (1)

dnickless
dnickless

Reputation: 10918

How about something like that:

db.items.aggregate(
    { $unwind: "$items" }    ,
    { $group : { _id : '$items.itemNumber', count : { $sum : 1 } } },
    { $match: { "count": { $gt: 1 } } }
)

Upvotes: 1

Related Questions