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.

CodePudding user response：

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.

CodePudding user response：

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"
  }
])