Count occurrences in an array in Mongodb

Question

I have such documents in Mongo:

{
  _id: 1,
  cat: ['a', 'b, 'c', 'd', 'e']
},
{
  _id: 2,
  cat: ['f', 'g, 'h', 'i', 'j']
},
{
  _id: 3,
  cat: ['a', 'b, 'f', 'g', 'h']
},
{
  _id: 4,
  cat: ['i', 'j, 'c', 'd', 'e']
}

and I have to filter the documents that have at least n occurrences (let' say 3) of 'cat' in a set I have, i.e.:

['a', 'b', 'c', 'f']

So in this case only the documents with _id equal to 1 and 3 should be returned because both of them have at least 3 occurrences of the categories present in the requested array.

What is the best way to solve this problem? Should I relay on aggregation framework or is there any easy way to do without it?

felix · Accepted Answer · 2019-05-29 05:29:58Z

3

you can achive this using $setIntersection operator

db.collection.aggregate(
   [
     {$project: {cat: 1, inter: { $setIntersection: [ "$cat", ['a', 'b', 'c', 'f'] ] } } }, 
     {$project: {cat: 1, size: {$size: "$inter"}}},
     {$match: {size: {$gte: 3}}}
   ]
)

output:

{ "_id" : 1, "cat" : [ "a", "b", "c", "d", "e" ], "size" : 3 }
{ "_id" : 3, "cat" : [ "a", "b", "f", "g", "h" ], "size" : 3 }

try it online: mongoplayground.net/p/Bv-Tdl5ii7l

edited May 29, 2019 at 5:29

answered Mar 14, 2017 at 11:17

felix

9,3158 gold badges44 silver badges65 bronze badges

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

1 Comment

pinturic Over a year ago

Really nice and clean solution!

s7vr · Accepted Answer · 2017-03-14 11:52:58Z

1

You can try $redact with $setIntersection for your query.

$setIntersection to compare the cat array with input array and return array of common names documents followed by $size and $redact and compare result with 3 to keep and else remove the document.

db.collection.aggregate(
    [{
        $redact: {
            $cond: {
                if: {
                    $gte: [{
                        $size: {
                            $setIntersection: ["$cat", ['a', 'b', 'c', 'f']]
                        }
                    }, 3]
                },
                then: "$$KEEP",
                else: "$$PRUNE"
            }
        }
    }]
)

edited Mar 14, 2017 at 11:52

answered Mar 14, 2017 at 11:20

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

1 Comment

pinturic Over a year ago

Really nice and clean solution!

Collectives™ on Stack Overflow

Count occurrences in an array in Mongodb

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related