Using the MongoDB aggregate framework to determine the total employee count per unique state

I'm currently working on displaying the total number of employees for each state within companies located in the USA. I aim to showcase this information for all states included in the dataset using sample numbers as a reference:

AZ : 1234
CA : 30000
FL : 43439

Within my data collection, documents are structured as shown here:

https://i.sstatic.net/Bor2M.png

To begin, I used $match to filter out companies with an office.country_code of "USA". Now, my next step is to calculate the total number of employees per state and group them accordingly. The challenge lies in dealing with the nested offices array, especially when companies have multiple offices stored within it.

https://i.sstatic.net/xkKJr.png

One approach I'm considering is utilizing $group to display the distinct states. However, extracting the distinct list of states proves to be tricky due to the nested structure of offices.state_code within an array, and the presence of multiple array elements in certain cases.

MongoPlayground

I hope this explanation clarifies my question. Thank you.

javascriptmongodbaggregation-framework

Answer №1

Utilizing the $unwind operator is essential to flatten out the nested data in the offices field.

db.collection.aggregate([
  {
    $unwind: "$offices"
  },
  {
    $match: {
      "offices.country_code": "USA"
    }
  },
  {
    $group: {
      _id: "$offices.state_code",
      employeesTotal: {
        $sum: "$number_of_employees"
      }
    }
  },
  {
    $sort: {
      _id: 1
    }
  }
])

Check out this MongoPlayground link for more insights.

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

When fetching data from a parse object, JavaScript displayed [object, Object] as the result

When trying to access information from my parse data, I am able to display user table data on my document without any issues. However, when I attempt to query another object and insert it into an id element using jQuery with functions like text();, html(); ...

javascriptjqueryhtmljsonparse-platform

"Headers cannot be set once they have been sent to the client... Error handling for unhandled promise rejection

Having trouble with cookies in the header - specifically, encountering an error at line number 30. The error message reads: "Cannot set headers after they are sent to the client." Additionally, there is an UnhandledPromiseRejectionWarning related to a prom ...

javascriptnode.jsmongodbexpresscookies

When using jQuery's POST method, the "done" event is triggered, however, no data is being sent

I've been working on implementing AJAX form submission and wrote a function to handle it when the button is clicked: function putUser() { $('button#putUser').on('click', function() { var user = $('input#user' ...

javascriptjqueryajaxforms

Node.js expressing caution about the use of backslashes in console logging statements

While this issue may not be considered crucial, I have observed an unexpected behavior when it comes to logging backslashes to the console. To verify the results, please try the following two examples in your terminal. I experimented with versions 0.10 an ...

javascriptnode.jsbackslash

npm: generate new script directive

When I start up my NodeJs (ES6) project, I usually enter the following command in the console: ./node_modules/babel/bin/babel-node.js index.js However, I wanted to streamline this process by adding the command to the scripts section of my package.json fi ...

javascriptnode.jsnpm

Did you manage to discover a foolproof method for the `filesystem:` URL protocol?

The article on hacks.mozilla.com discussing the FileSystem API highlights an interesting capability not previously mentioned. The specification introduces a new filesystem: URL scheme, enabling the loading of file contents stored using the FileSystem API. ...

javascriptfilesystemsw3chtml5-filesystem

Detecting the specific button that was selected with PHP

I am in the process of developing a website for a production company where, upon clicking on a director's name from the menu, all other menu items disappear and only the selected director's biography and work are displayed. My current challenge ...

phpjavascriptjquerymysqlcss

Create an interactive visualization using d3.js

Currently, I am on the hunt for a technology that can help me render a tree structure based on Json data. After researching options like graphViz and d3, it seems like d3 is more optimized for modern browsers. With the extracted Json data, my goal is to c ...

javascriptphpjsond3.jsgraphviz

Positioning tooltip arrows in Highcharts

I'm attempting to modify the Highcharts tooltip for a stacked column chart in order to have the arrow on the tooltip point to the center of the bar. I understand that I can utilize the positioner callback to adjust the tooltip's position, but it ...

javascriptjqueryhtmlcsshighcharts

What is the best way to align text extracted from an API using JavaScript?

I'm currently exploring content generation through APIs, but I'm facing an issue where the text generated is not aligning properly within the container on the screen. The main problem lies in getting the card to be centered on the screen with al ...

javascripttextbootstrap-5api-design

Vertical alignment of content over image is not in sync

I am attempting to center my div container .home-img-text vertically in the middle of its parent div .home-img. Despite trying various methods such as setting .home-img-text to position: absolute, relative, adding padding-top, and several others, I haven&a ...

javascriptjqueryhtmlcssmargin

Rotation snapping feature 'control.setRotationSnap' in TransformControls.js (Three.js) is not functioning properly

Attempting to utilize the functionality of "control.setRotationSnap" from the script "TransformControls.js", but unfortunately, it is not working as expected. After conducting some research, I came across a forum post suggesting that the code might not be ...

javascriptmaththree.jsrotationcontrols

What could be causing my images not to show up when I use string interpolation for src links in Vue.js?

I've encountered an issue while working on Vue.js where I'm struggling to render a couple of images from the local directory. What puzzles me is that this problem arises when using string interpolation, like in the code snippet below: <img :s ...

javascriptvue.js

What can I do to resolve a node server issue with the error message "npm ERR! code ELIFECYCLE npm ERR! errno 1"?

npm ERROR! code ELIFECYCLE npm ERROR! errno 1 npm ERROR! [email protected] start: node server npm ERROR! Exit status 1 npm ERROR! npm ERROR! Task failed at the [email protected] start script. npm ERROR! This may not necessarily be an ...

javascriptnode.jsexpressnpm

How can I force a Kendo UI Chart to resize in VueJS?

When integrating Kendo UI's Chart component within a Vue application, how can we trigger the chart to refresh or redraw? Although the chart automatically adjusts to changes in width by filling its parent container horizontally, noticeable stretching ...

javascriptvue.jskendo-uitelerikwindow-resize

Need help with updating React component state within a Meteor.call callback?

I've constructed this jsx file that showcases a File Uploading Form: import React, { Component } from 'react'; import { Button } from 'react-bootstrap'; class UploadFile extends Component { constructor(){ super() this. ...

javascriptreactjsmeteormeteor-react

The output is generated by React useContext with a delay

When using the data obtained from useContext to verify if the logged-in user is an Admin, there seems to be a delay where it initially returns "undefined" and then after about 5 seconds, it provides the actual value. This causes the code to break since it ...

javascriptreactjsreact-reduxreact-router

Issue encountered with AJAX request using JavaScript and Express

I'm brand new to this and have been searching online for a solution, but I can't seem to figure it out. It's possible that I'm making a basic mistake, so any assistance would be greatly appreciated. I'm trying to create a simple f ...

javascriptjqueryajaxnode.jsexpress

Ways to access states from a Vuex store within a Vuetify list in VueJs

Here is a snippet from my Vue file: import store from '@/store' export default{ name: 'myList', data: () => ({ show: true, listContent: [{ name: '1', icon: 'pers ...

javascriptvue.jsvuexvuetify.js

Pass data from JavaScript to PHP using AJAX

Using Leaflet to display a map, I have incorporated ajax into my onEachFeature function in order to retrieve variables to pass to PHP. Here is the code snippet: function onEachFeature(feature, layer) { layer.bindPopup(feature.properties.IDLo); layer. ...

javascriptphpajaxleaflet