dzm
dzm

Reputation: 23574

MongoDB sum with match

I have a collection with the following data structure:

{
  _id: ObjectId,
  text: 'This contains some text',
  type: 'one',
  category: {
     name: 'Testing',
     slug: 'test'
  },
  state: 'active'
}

What I'm ultimately trying to do is get a list of categories and counts. I'm using the following:

  const query = [
    {
      $match: {
        state: 'active'
      }
    },
    {
      $project: {
        _id: 0,
        categories: 1
      }
    },
    {
      $unwind: '$categories'
    },
    {
      $group: {
        _id: { category: '$categories.name', slug: '$categories.slug' },
        count: { $sum: 1 }
      }
    }
  ]

This returns all categories (that are active) and the total counts for documents matching each category.

The problem is that I want to introduce two additional $match that should still return all the unique categories, but only affect the counts. For example, I'm trying to add a text search (which is indexed on the text field) and also a match for type.

I can't do this at the top of the pipeline because it would then only return categories that match, not only affect the $sum. So basically it would be like being able to add a $match within the $group only for the $sum. Haven't been able to find a solution for this and any help would be greatly appreciated. Thank you!

Upvotes: 1

Views: 272

Answers (1)

mickl
mickl

Reputation: 49995

You can use $cond inside of your $group statement:

{
    $group: {
        _id: { category: '$categories.name', slug: '$categories.slug' },
        count: { $sum: { $cond: [ { $eq: [ "$categories.type", "one" ] }, 1, 0 ] } }
    }
}

Upvotes: 1

Related Questions