MongoDB: Group Documents by Calendar Week and Year, then Sum the Values of a single field

{
    "GU" : "Person2",
    "CW" : 2,
    "Year" : 2015,
    "_id" : "01"
},
{
    "GU" : "Person2",
    "CW" : 2,
    "Year" : 2015,
    "_id" : "02"
},
{
    "GU" : "Person1",
    "CW" : 2,
    "Year" : 2015,
    "_id" : "03"
},
{
    "GU" : "Person1",
    "CW" : 3,
    "Year" : 2015,
    "_id" : "04"
},
{
    "GU" : "Person1",
    "CW" : 51,
    "Year" : 2014,
    "_id" : "05"
},
{
    "GU" : "Person1",
    "CW" : 51,
    "Year" : 2014,
    "_id" : "06"
}

Is there a way to merge the data based on CW and Year, counting the occurrences of each person for each week?

In summary, the desired output should be:

{Year: 2014, CW:51, GU:{Person1:2}},
{Year: 2015, CW:2, GU:{Person1:1, Person2:2}},
{Year: 2015, CW:3, GU:{Person1:1}}
javascriptmongodbmeteormongodb-query

Answer №1

To accomplish this, simply group two consecutive timestamps together:

db.mycol.aggregate([
    {$group:{
        _id: {Year: "$Year", CW:"$CW", GU: "$GU"},
        total:{  
            $sum:1
        }
    }},
    {$group:{
        _id: {Year: "$_id.Year", CW:"$_id.CW"},
        GUs: { $push:  { GU: "$_id.GU", total: "$total" } }
    }},
    {$sort: {"_id.Year" : 1, "_id.CW" : 1}}
])

Edit: included sorting in the aggregation pipeline

The resulting output will show:

{
    "result" : [
            {
                    "_id" : {
                            "Year" : 2014,
                            "CW" : 51
                    },
                    "GUs" : [
                            {
                                    "GU" : "Person1",
                                    "total" : 2
                            }
                    ]
            },
            {
                    "_id" : {
                            "Year" : 2015,
                            "CW" : 2
                    },
                    "GUs" : [
                            {
                                    "GU" : "Person1",
                                    "total" : 1
                            },
                            {
                                    "GU" : "Person2",
                                    "total" : 2
                            }
                    ]
            },
            {
                    "_id" : {
                            "Year" : 2015,
                            "CW" : 3
                    },
                    "GUs" : [
                            {
                                    "GU" : "Person1",
                                    "total" : 1
                            }
                    ]
            }
    ],
    "ok" : 1
}

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

Ways to split a string using jQuery

I am working with a jQuery string array that contains the following elements: ["$1#Structure$2#Accounting$3Acc#$1Programming"] My task is to split the strings after the '#' symbol and provide the following result: ["Structure","Accounting","Ac ...

javascriptjquerystring

When hovering over slick text, it becomes hidden because of non-responsive CSS. Are there any solutions to make it responsive?

I can't seem to get the on-hover dates to appear, even though they are rendered when inspecting the page. I suspect it could be related to a responsive CSS issue or class breaking. How can I resolve this? https://i.stack.imgur.com/jyEJb.png https:// ...

javascriptphphtmlcss

What is the best way to display additional content in a Django application?

My Django application has a template for viewing group lists. However, I have noticed that as the number of groups increases, the page scrolls down and I am unable to see all the names of the groups. What I want is to display only 4 group names initially ...

javascriptpythonhtmldjango

"Learn how to properly configure JSON data on x and y axes in the c3.js library

I rely on c3 to produce uncomplicated graphs. I am in search of a way to extract data from a Json file and use it to create my Line Graph. In order for the graph to display correctly, I need my Y values to be represented by "Labels" and my X values to be ...

javascriptjqueryjsond3.jsc3.js

Alert: It is not possible to update the React state on a component that is already unmounted after updating the context and switching to a different page

I am facing a challenge that requires your assistance In the /editpage, there is a changeProfile function that should be triggered upon clicking const appContext = useContext(AppContext); const [userLogin, setUserLogin] = appContext.userLogin; const [use ...

javascriptreactjsreact-hooks

After running the `npm run build` command in a Svelte (not Svelte Kit) application, the index.html file displays as

When I run the npm run dev server, it displays the default counter app that is built. However, if I build a plain HTML, CSS, and JavaScript project using npm run build in the dist folder, then open the index.html file, it shows a blank page even though the ...

javascriptnpmsvelte

What is the reason behind the failure of this code to check for anagrams?

I am attempting to create a JavaScript function that checks for anagrams. To keep things simple, let's assume this function only works with lowercase strings that do not contain spaces, numbers, or symbols. Why isn't the following code functionin ...

javascriptarrayssortingsplitanagram

Converting image bytes to base64 in React Native: A step-by-step guide

When requesting the product image from the backend, I want to show it to the user. The issue is: the API response contains a PNG image if the product has an image, but returns a (204 NO Content) if the product does not have an image. So, I need to display ...

javascriptreactjstypescriptreact-nativeexpo

Having trouble with WebRTC video streaming on Firefox?

I have implemented one-way broadcasting in my Dot Net MVC website for video streaming using the example found at https://github.com/muaz-khan/WebRTC-Experiment/blob/master/webrtc-broadcasting/index.html. While it works perfectly in Google Chrome, unfortuna ...

javascriptjqueryasp.net-mvcfirefoxvideo-streaming

Using importNode in the context of Microsoft Edge involves transferring a

I am facing an issue with a dynamic page that has the ability to change its main div content using a bar button. The pages are mostly static except for one which contains JavaScript (RGraph charts). To make it work, I am currently using the following code ...

javascriptgoogle-chromemicrosoft-edge

What is the best way to invoke a controller method using jQuery within a cshtml file?

I am working on a project where I need to add user information to the database by calling a function in my controller class. The user's information is entered through a form created in a .cshtml file that interacts with an external JavaScript file. Is ...

javascriptjqueryajaxasp.net-mvcasp.net-mvc-4

The Gridsome website deployed on Netlify seems to be experiencing some issues. When clicking on links, the pages are not loading properly despite

After deploying my site on Netlify, I encountered an issue where the URL updates when clicking on links on the landing page, but the content does not show unless the page is refreshed. Interestingly, this issue does not occur on my local development server ...

javascriptvue.jsfrontendnetlifygridsome

What's the quickest method for duplicating an array?

What is the quickest method for duplicating an array? I wanted to create a game, but I found that Array.filter was performing too slowly, so I developed a new function: Array.prototype.removeIf = function(condition: Function): any[] { var copy: any[] ...

javascriptarraystypescriptcopy

Unable to retrieve JSON data in servlet

I am having trouble passing variables through JSON from JSP to a servlet using an ajax call. Despite my efforts, I am receiving null values on the servlet side. Can someone please assist me in identifying where I may have gone wrong or what I might have ov ...

javascriptjavajqueryjsonservlets

`Enhance Image with a Fresh Hue`

Let me explain my dilemma: I'm dealing with a two-tone png image - one tone is black and the other is transparent. Right now, I'm relying on the background color attribute to dynamically change the color of the transparent section. However, I ...

javascriptcsshtml

Utilize node.js on your local machine and leverage gulp to monitor any modifications

I recently copied a repository from https://github.com/willianjusten/bootstrap-boilerplate and followed these steps. git clone git://github.com/willianjusten/bootstrap-boilerplate.git new_project cd bootstrap-boilerplate npm install gulp The gulp comman ...

javascriptnode.jsnpmgulp

Issue with POST request failure in Mongodb, React, Node, and Express

I've been attempting to execute a post request from a React front-end using jQuery Ajax calls. However, no matter what I try with app.post, it appears that no data is being sent to the database. All I get is an empty {} when "completed send" is displa ...

mongodbapiexpressreactjs

Unable to output nested arrays as props in React JS to the console

Having retrieved data from a 3rd party API and stored the response in a state, I proceeded to pass an object within the response as a prop to another component. This represents the prop: { "count": 20, "games": [ { "id": 66947, "game_ ...

javascriptarraysreactjsreact-props

Is it possible to dynamically adjust the number of children in an array?

As a beginner in React, I've been exploring ways to manage an array of components from a parent component. My task involves creating a site where I can dynamically add or remove names to a list. However, I'm facing a challenge in figuring out the ...

javascriptarraysreactjs

Retrieving information from an API and transferring it to the state in a React component

I am currently working on a random quote generator project, and I'm facing some difficulties in passing the API data to my state and also accessing it in the QuoteComponent through props. Despite following all the correct procedures, whenever I try to ...

javascriptreactjs