Count in aggregation MongoDB

Question

I have a document that contains companies that have offices all over the world. These offices are in an array. One of the values is the city and I need to know exactly which companies have 2 offices in "San Francisco".

The Document:

_id:52cdef7c4bab8bd675297d8b
name:"AdventNet"
.
.
.
.
Offices:Array
   0:
     Description:"Headquarters"
     .
     .
     city:"San Francisco"

My Solution:

db.companies.aggregate(
      {$group:{_id:{city:"$offices.city"},count:{$sum:1}}},
      {$match:{$and: [{"_id.city":"San Francisco"},{count:2}]}})

But it doesn't work. Any Idea?

Tushar Gupta - curioustushar · Accepted Answer · 2021-05-16 09:49:28Z

Demo - https://mongoplayground.net/p/qnTm42c0Y1S

db.collection.aggregate([
  {
    $match: { "Offices.city": "San Francisco" } // filter to reduce records going to $map in the next pipeline 
  },
  {
    $set: {
      haveOfficeInCIty: {
        $size: { // get the size of the filtered array
          $filter: { // filter the array
            input: "$Offices",
            as: "office",
            cond: {
              $eq: [ "$$office.city", "San Francisco" ] // filter by city
            }
          }
        }
      }
    }
  },
  {
    $match: {
      haveOfficeInCIty: { $eq: 2 } // filter where count is 2
    }
  }
])

turivishal · Accepted Answer · 2021-05-16 09:54:02Z

$filter to iterate loop of city and filter city by "San Francisco"
$size to get size of filtered city
match expression $expr condition size of city is 2

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $eq: [
          {
            $size: {
              $filter: {
                input: "$Offices.city",
                cond: { $eq: ["$$this", "San Francisco"] }
              }
            }
          },
          2
        ]
      }
    }
  }
])

Playground

Collectives™ on Stack Overflow

Count in aggregation MongoDB

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related