Get count of records with field existing in MongoDB

Question

I have a MongoDB collection with records in the following format:

[
  { "item1": { "a": 1 }, "item2": { "a": 2 } },
  { "item1": { "a": 3 }, "item3": { "a": 4 } },
  { "item1": { "a": 5 }, "item2": { "a": 6 } },
]

I want to get a count of records having the fields item1, item2, and item3 (They don't need to be dynamic. I have only a finite set of items). What I need is a count of records with field existing in the following fashion:

{ "item1": 3, "item2": 2, "item3": 1 }

For getting the count for item1, I do this:

db.collection.find({ "item1": { $exists: true }}).count()

Is there an easy way to aggregate the count of all three items in a single query?

mickl · Accepted Answer · 2020-08-05 06:43:52Z

3

You can use $objectToArray and $arrayToObject to count your keys dynamically:

db.collection.aggregate([
    {
        $project: { root: { $objectToArray: "$$ROOT" } }
    },
    {
        $unwind: "$root"
    },
    {
        $group: { _id: "$root.k", total: { $sum: 1 } }
    },
    {
        $group: { _id: null, obj: { $push: { k: "$_id", v: "$total" } } }
    },
    {
        $replaceRoot: { newRoot: { $arrayToObject: "$obj" } }
    },
    {
        $project: { _id: 0 }
    }
])

Mongo Playground

answered Aug 5, 2020 at 6:43

mickl

50.1k9 gold badges71 silver badges99 bronze badges

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

4 Comments

Yedhu Krishnan Over a year ago

This is great! Is there an easy way to exclude empty keys, say item5: {}?

mickl Over a year ago

@YedhuKrishnan sure, you can try this way (additional $match): mongoplayground.net/p/2Y4RD0mgTZd

Yedhu Krishnan Over a year ago

Sorry to bother. I have one last question. Can I get the same count array grouped by a single field? For example, if I have a field called itemGroup with values 1, 2, 3 etc. Can I get count per itemGroup?

mickl Over a year ago

Not sure if I understand but if you want to follow this approach then just match by root.k to filter out all the keys that are different than itemGroup

Collectives™ on Stack Overflow

Get count of records with field existing in MongoDB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related