MongoDB aggregate query: $size count item only if equal to certain value

Question

I know how to write a query such that I can count the number of items in an array per document using $size.

db.collection.aggregate(
   {project : {"name" : 1, _id:0, sizeOf : {$size : "$People.Age"}}}
)

How do I write a query using $size, but I only want to count the field if it equals a certain value (for example People.Ages.Current_Age : 21). I'm assuming I'd use $eq for that?

Sample Code:

{
    "People" : [
        {
            "Name" : "Jen",
            "Ages" : {
                "Current_Age" : 32,
                "Graduate_Age" : 26,
            }
        },
        {
            "Name" : "Bill",
            "Ages" : {
                "Current_Age" : 30,
                "Graduate_Age" : 22,
            }
        },
        {
            "Name" : "Josh",
            "Ages" : {
                "Current_Age" : 27,
                "Graduate_Age" : 24,
            }
        }
    ]
}

Ashh · Accepted Answer · 2018-12-10 20:57:25Z

1

You can use below aggregation

db.collection.aggregate([
  { "$project": {
    "name": 1, "_id": 0,
    "sizeOf": {
      "$size": {
        "$filter": {
          "input": "$People",
          "as": "people",
          "cond": { "$eq": ["$$people.Ages.Current_Age", 21] }
        }
      }
    }
  }}
])

edited Dec 10, 2018 at 20:57

answered Dec 10, 2018 at 3:24

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

6 Comments

James Mitchell Over a year ago

What is the meaning of the dollar sign when used for "$People" and "$$people.age", I understand why it is used for operators but not other things.

Ashh Over a year ago

$ represents the document fields and the $$ represents the let variables which we assume in the query.

Ashh Over a year ago

Here in the above query. We have taken your $People documents array as input parameter of the $filter and taken $$people as let variable which contains single element from the array. It is same as javascript filter. Something like People.filter((people) => people.age === 21)

James Mitchell Over a year ago

So what if Age (call it ages) is an object itself that has a single element in it that I want to access? For example I want to access People.Ages.CurrentAge. How would that change the above code? If I want CurrentAge = 21.

Ashh Over a year ago

Updated my answer

|

gokultp · Accepted Answer · 2018-12-10 02:45:00Z

0

You can use filter operator here, filter the array before taking size.

db.collection.aggregate(
    {project : {"name" : 1, _id:0, sizeOf : {$size : {
        $filter: {
            input: "$People.Age",
            as: "age",
            cond: { $eq:[$$age, 21] }
        }
    }}}}
)

answered Dec 10, 2018 at 2:45

gokultp

1651 silver badge7 bronze badges

2 Comments

James Mitchell Over a year ago

I'm getting $$age is not defined (same if I make it $age)

gokultp Over a year ago

Can you add an example document with the question.

Collectives™ on Stack Overflow

MongoDB aggregate query: $size count item only if equal to certain value

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related