What kind of MongoDB query finds same value multiple times in an array?

Question

Say I have documents like this in a MongoDB

{
    "mylist":[
        {"value": 1},
        {"value": 2},
        {"value": 3}
    ]
},
{
    "mylist":[
        {"value": 1},
        {"value": 2},
        {"value": 1}
    ]
}

How can I query the documents that have {"value": 1} multiple times (ie. the latter) in the list?

Looks like a good candidate for aggregation pipeline treatment. — Sergio Tulentsev
– Sergio Tulentsev, Commented Sep 21, 2015 at 12:36
possible duplicate of MongoDB - How to find all objects within an array? — Neo-coder
– Neo-coder, Commented Sep 21, 2015 at 12:48
@SergioTulentsev Actually not a good candiate currently at all. The best you can do without altering things is $unwind and $project. Which is not a good solution. Will get better in the next MongoDB release though. — Blakes Seven
– Blakes Seven, Commented Sep 21, 2015 at 13:08

Blakes Seven · Accepted Answer · 2015-09-21 13:25:57Z

2

Probably best handled with a JavaScript evaluation using $where:

db.colletion.find({
    "$where": function() {
        return this.mylist.filter(function(el) {
            return el.value == 1;
        }).length >= 1;
    }
})

Or currently not so greatly handled in aggregation:

db.collection.aggregate([
    { "$redact": {
        "if": { "$eq": [ { "$ifNull": [ "$value", 1 ] }, 1 },
        "then": "$$DESCEND",
        "else": "$$PRUNE"
    }},
    { "$redact": {
        "if": { "$gt": [{ "$size": "$mylist" }, 1 ] },
        "then": "$$KEEP",
        "else": "$$PRUNE"
    }}
])

Or better handled in the future with $filter:

db.collection.aggregate([
    { "$redact": {
        "if": {
            "$gt": [
                { "$size": { "$filter": {
                    "input": "$mylist",
                    "as": "el",
                    "cond": {
                        "$eq": [ "$$el.value", 1 ]
                    }
                }}},
                1
            ]
        },
        "then": "$$KEEP",
        "else": "$$PRUNE"
    }}
])

Where that latter operator is not available yet until the next release of MongoDB. But in general you want to be able to filter out and "count" the occurences of matches. These are a couple of ways to do this.

edited Sep 21, 2015 at 13:25

answered Sep 21, 2015 at 12:58

Blakes Seven

50.5k14 gold badges132 silver badges136 bronze badges

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

2 Comments

user541905 Over a year ago

How efficient is the $where statement in comparison with using a bit more complex aggregation command like the latter ones are? The first one is by far clearest in terms of readability at least.

Blakes Seven Over a year ago

@user541905 The main thing to understand about aggregation pipelines is that in most cases ( this is one of them ) each pipeline stage represents a pass through all of the data in the collection, or at least those documents remaining after processing a stage. So as stated, the best that can presently be produced is two passes, and that is likely to add time. That said, MongoDB 3.2 is in release candidate stage as of writing so the last would be most efficient when released. The $where should be the current fastest way. But you should really always test and benchmark on your own data.

James Baker · Accepted Answer · 2015-09-21 12:44:46Z

0

You could use an aggregation pipeline - I'd suggest unwinding the arrays, then grouping by original document and value, then filtering to only leave those results that have a count greater than 1.

answered Sep 21, 2015 at 12:44

James Baker

1,2582 gold badges20 silver badges43 bronze badges

Collectives™ on Stack Overflow

What kind of MongoDB query finds same value multiple times in an array?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Collectives™ on Stack Overflow

What kind of MongoDB query finds same value multiple times in an array?

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related