Question 116288 in Mongodb Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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"
  }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search