Nodejs MongoDb add column to query that is a count of specific records

Question

Considering the following document structure:

{_id: 1, name: 'joe', snapshot: null, age: 30}
{_id: 2, name: 'joe', snapshot: 'snapshot1', age: 30}
{_id: 3, name: 'joe', snapshot: 'snapshot15', age: 30}
{_id: 4, name: 'joe', snapshot: 'snapshot23', age: 30}

How would I perform a query that groups on the name field and adds an additional field that is a count of the remaining records containing subtree: 'additionalinfo'. It would look like this:

{_id: 1, name: 'joe', snapcount: 3, age: 30}

I've been able to group using aggregations but I can't quite get it like this.

My own solution:

I ultimately restructured my data to look like this instead:

{
  _id: 1, 
  name: 'joe', 
  snapshots: [
    {name: 'snap17', id: 1},
    {name: 'snap15', id: 2},
    {name: 'snap14', id: 3}
  ], 
  age: 30
}

This allows me to just check snapshots.length to solve my original problem. However; the answers in this post where very helpful and answered the original question.

ambianBeing · Accepted Answer · 2022-11-07 19:20:09Z

1

Adding another aggregation query to do it: playground link: try it

db.collection.aggregate([
  {
    $match: {
      "snapshot": {
        $exists: true,
        $ne: null
      }
    }
  },
  {
    $group: {
      _id: "$name",
      snapcount: {
        $sum: 1
      },
      age: {
        "$first": "$age"
      },
      name: {
        "$first": "$name"
      }
    }
  },
  {
    "$unset": "_id"
  }
])

Based on the comments, the query worked for OP:

db.collection.aggregate([
  {
    $match: {
      "snapshot": {
        $exists: true,
        $ne: null
      }
    }
  },
  {
    $group: {
      _id: "$name",
      snapcount: {
        $sum: 1
      },
      age: {
        "$first": "$age"
      },
      name: {
        "$first": "$name"
      },
      id: {
        "$first": "$_id"
      }
    }
  },
  {
    "$unset": "_id"
  }
])

edited Nov 7, 2022 at 19:20

answered Nov 6, 2022 at 23:03

ambianBeing

3,5492 gold badges17 silver badges28 bronze badges

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

3 Comments

Emme Over a year ago

I like this answer but it doesn't keep the original _id field. Anyway to do that?

ambianBeing Over a year ago

@Emme What is your use case of keeping the _id field in a grouped result. Keeping the _id in this case would just get you the first id found while grouping. Also in a group stage an explicit _id field is required so we cannot keep it as _id. You could try projecting it as id try this query

Emme Over a year ago

I use the _id field as the query parameter to update the record when needed. Projecting it as id should solve this problem though. Thanks for your answer.

rickhg12hs · Accepted Answer · 2022-11-06 22:55:21Z

1

Here's one way you could do it.

db.collection.aggregate([
  {
    "$group": {
      "_id": "$name",
      "name": {"$first": "$name"},
      "age": {"$first": "$age"},
      "snapcount": {
        "$sum": {
          "$cond": [
            {"$eq": [{"$type": "$snapshot"}, "string"]},
            1,
            0
          ]
        }
      }
    }
  },
  {"$unset": "_id"}
])

Try it on mongoplayground.net.

answered Nov 6, 2022 at 22:55

rickhg12hs

12k6 gold badges31 silver badges50 bronze badges

2 Comments

Emme Over a year ago

I like this answer but it doesn't keep the original _id field. Anyway to do that?

rickhg12hs Over a year ago

@Emme What would be the criteria for assigning the "_id" field for this aggregation?

Collectives™ on Stack Overflow

Nodejs MongoDb add column to query that is a count of specific records

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related