title: MongoDB aggregate pipeline to get number of employees for each distinct state
body: <p>I am trying to show the total number of employees for each state, only for companies in the USA.
  So it would look like this but for all the states included in the dataset: (Sample numbers)</p>
  
  <pre><code>AZ : 1234
  CA : 30000
  FL : 43439
  </code></pre>
  
  <p>A have a collection with documents structured like so:</p>
  
  <p><a href="https://i.stack.imgur.com/Bor2M.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Bor2M.png" alt="enter image description here"></a></p>
  
  <p>My first step was to simply use $match to only get companies with an office.country_code of "USA".
  Now I need to count all the employees for each state and group them. I also need to list each state without duplication. I have tried $group but the nested offices array is giving me trouble. There are also situations where some companies have multiple offices in the offices array.</p>
  
  <p><a href="https://i.stack.imgur.com/xkKJr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xkKJr.png" alt="enter image description here"></a></p>
  
  <p>I guess my first step would be to display the distinct states using $group. How would I get the distinct list of states when the offices.state_code is nested in an array element, and sometimes there are multiple array elements. </p>
  
  <p><a href="https://mongoplayground.net/p/LA9tQxxthYt" rel="nofollow noreferrer">MongoPlayground</a></p>
  
  <p>Hopefully, that makes sense. Thanks.</p>