MongoDB Count Items in array by name

Question

I have documents like this:

{
    "_id" : ObjectId("5b3ced158735f1196d73a743"),
    "cid" : 1,
    "foo" : [
        {
            "k" : "sport",
            "v" : "climbing"
        },
        {
            "k" : "sport",
            "v" : "soccer"
        },
        {
            "k" : "sport",
            "v" : "soccer"
        }
    ]
}

This Query just return the documents which contains a soccer field.

db.coll.find({foo:{$elemMatch:{ v: "soccer"}} }, {"foo.$" : 1,cid:1})

returns:

{ "_id" : ObjectId("5b3ced158735f1196d73a743"), "cid" : 1, "node" : [ { "k" : "sport", "v" : "climbing" } ] }

But I want to know, how many soccer-Elements are in each returned document. How can I count them?

Just wanted to know if I got your question. You expect output : 2 ? Am I right? — kapilpatwa93
– kapilpatwa93, Commented Jul 5, 2018 at 12:41
Yeah, cid and count. There are a lot of documents like this. And I need for example: {cid: 1,count:4}, {cid:3,count: 2} — pats
– pats, Commented Jul 5, 2018 at 12:48

Rubin Porwal · Accepted Answer · 2018-07-08 11:34:21Z

db.coll.aggregate(

    // Pipeline
    [
        // Stage 1
        {
            $match: {
                foo: {
                    $elemMatch: {
                        v: 'soccer'
                    }
                }
            }
        },

        // Stage 2
        {
            $unwind: {
                path: '$foo'
            }
        },

        // Stage 3
        {
            $project: {
                cid: 1,
                count: {
                    $cond: {
                        if: {
                            $eq: ['$foo.v', 'soccer']
                        },
                        then: {
                            $sum: 1
                        },
                        else: 0
                    }
                }
            }
        },

        // Stage 4
        {
            $group: {
                _id: '$cid',
                total_count: {
                    $sum: '$count'
                }
            }
        }

    ]



);

s7vr · Accepted Answer · 2018-07-05 12:53:43Z

1

You can use below query to $filter and $size the filtered array to count no of matching occurrences.

db.coll.aggregate([
  {"$project":{
    "cid":1,
    "count":{
      "$size":{
        "$filter":{
          "input":"$foo",
          "cond":{"$eq":["$$this.v","soccer"]
          }
        }
      }
    }
  }}
])

answered Jul 5, 2018 at 12:53

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

2 Comments

pats Over a year ago

Cool, but how can I just return fields with count > 0?

s7vr Over a year ago

You can add match stage after project. Something like {"$match":{"count":{"$gt":0}}}

Collectives™ on Stack Overflow

MongoDB Count Items in array by name

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related