Aggregation framework in MongoDB for group, sum, and sub

How can I create a comprehensive country grouping with the total sum of subdetails/subdocuments?

[{
      Country: 'USA',
      amount: 10,
      detailA: [{
        Country: 'USA',
        amount: 20,
      },{
        Country: 'MEX',
        amount: 20,
      },{
        Country: 'RUSK',
        amount: 30,
      }],
      detailB: [{
        Country: 'USA',
        amount: 90,
      }]
    },{
      Country: 'RUSK',
      amount: 30,
      detailA: [{
        Country: 'USA',
        amount: 50,
      },{
        Country: 'MEX',
        amount: 60,
      }],
      detailB: [{
        Country: 'MEX',
        amount: 10,
      }]
    }];

Desired output after grouping by country: USA 170 RUSK 60 MEX 90

javascriptmongodb

Answer №1

Script:

Utilizing the aggregation pipeline method:

db.getCollection('countries').aggregate([
    {
        $project: {
            'root.Country': '$Country',
            'root.amount': '$amount',
            detail: {
                $concatArrays: [ '$detailA', '$detailB'],
            },
        },
    },
    {
        $project: {
            detail: {
                $concatArrays: [ '$detail', ['$root'] ],
            },
        },
    },
    { $unwind: '$detail' },
    {
        $group: {
            _id: '$detail.Country',
            amount: { $sum: '$detail.amount' }
        }
    }
]);

Description:

The objective is to standardize the data structure for easy grouping with the $group stage. The ideal document format before this stage should be:

{ Country, amount }

In the initial projection phase, the arrays detailA and detailB are merged into a single array labeled as detail. Additionally, the fields Country and amount are encapsulated within a root subdocument.

Subsequently, the root object is incorporated into the detail array during the second projection step.

The individual elements in the detail array are then expanded into separate documents through the 'unwind' process.

To finalize, we group the data by Country and calculate the total sum of the amounts.

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

innerhtml does not display the entire array contents

I'm facing an issue with my innerHTML output where it seems to be displaying one less value than expected from the array. I've been unable to pinpoint the exact problem. await fetch(urlbody, bodyrequestOptions) .then((response) => response ...

javascripthtmldom

Get the "first" item using the $lookup command, then merge it with additional fields while excluding certain fields

In my database, I have a collection named "Notes" which includes a reference to a question index within a nested array of questions from another collection called "Chapters". While I have successfully created an aggregation for this, I am now wondering if ...

mongodbmongodb-query

Ways to refresh the main frame

Here is an example of parent code: <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Parent</title> </head> <body> <iframe src="https://dl.dropboxusercontent.com/u/4017788/Labs/child.html" width ...

javascriptjqueryhtmliframe

Trigger an event in Angular using TypeScript when a key is pressed

Is it possible to activate a function when the user presses the / key in Angular directly from the keydown event? <div (keydown.\)="alerting()"> </div> <div (keydown.+)="alerting()"> </div> Both of these ...

javascriptangulartypescript

Unable to get Node.js, socket.io, and base64-to-image to function properly

Currently, I am working on a project that involves using node.js and socket.io to send an image from a server to a client. Below are the snippets of code that I am using: renderer.js: const imgFile = fs.readFileSync(screenShotPath); const im ...

javascriptnode.jssocket.io

Enhancing webpage styles with AngularJS

Just starting out with AngularJS and eager to get the best approach to tackle this challenge. Describing my dilemma: I have an anchor element that, when clicked, needs to toggle between classes "show-all" and "hide-all" while also updating the styling of ...

javascripthtmlcssangularjsstyling

What is the proper way to implement v-model for a custom component within the Vue render function?

Below is the code that I am working with: ... var label_key_map = { a: "1", b: "2", c: "3", d: "4" } render: (h) => { var form_data = {} for (let key in label_key_map) { var form_item = h( 'FormItem', {props: {prop: key}}, ...

javascriptvue.jsvuejs2

Semi-transparent HTML5 Canvas illustrations

Can I generate partially transparent elements dynamically in canvas? I am currently adjusting the opacity of the entire canvas element through CSS, but I need certain elements to be more translucent than others. My research has not turned up any evidence ...

javascripthtmlcanvastransparencyopacity

Error: StalePageException occurred in the wicket framework

I am currently using Wicket version 6.20. Within a Wicket page, I have implemented an AbstractDefaultAjaxBehavior to capture mouse clicks and their x,y coordinates: class CallFromJavaScript extends AbstractDefaultAjaxBehavior { private static final l ...

javascriptjavaajaxwicket

Having trouble getting basic JavaScript function to conceal a div element?

Looking to expand my knowledge in JavaScript and would greatly appreciate any assistance as I navigate this new world of learning. Despite exploring various resources on this site and trying out suggestions from other users, none have addressed my specific ...

javascriptjqueryhtml

I sought out a way to incorporate a hyperlink within the Material Data Grid

Take a look at this sample data grid for reference: here. I added an image like this: see here. However, I couldn't create a clickable link. ...

javascriptreactjsdatagridviewhyperlinkmaterial-ui

issue with displaying content with useEffect hook

Displaying a list of coupons based on the selected category using useEffect and saga. const initialState = { is_loading: true, coupon_data: [], error_msg: '', } This is the initial state. is_loading is set to true by default so that ...

javascriptreactjsreduxuse-effectsaga

Creating routes in ExpressJs and rendering pages using Vanilla JavaScript

When I send a request to app.get("/auth/login", (req, res) => { res.sendFile(path.join(__dirname + "/authentication/login.html")); }); using fetch("/auth/login", { method: "GET" }); I'm able to receive the HTML page as a response. ...

javascriptexpress

Dynamic field validation using jQuery

I am currently developing a wizard feature that retrieves employees dynamically from the backend. The employee table is created with a radio input field and then inserted into my HTML code: $.ajax({ method: "get", url: '/fetchEmployees/' ...

javascriptjqueryajaxformvalidation.io

Personalize your Datatable export options with Jquery

I am working with a datatable that contains columns with data in the format 'XXXX unit'. For my export, I need to remove the 'unit' part of the data. What specific rule should I implement for this task? exportOptions: { columns: ...

javascriptjquerydatatables

iOS 8: mysterious void found at the bottom of the Safari home screen page in full screen mode

Hello everyone, I hope you can assist me with a dilemma I am facing. Despite being a long-time reader of this forum, this is my first time posting here. I have searched extensively online for solutions to my issue but have not found anything recent or effe ...

javascripthtmlios8fullscreenstatusbar

Contrasts between the storage of data in a static function versus a static object

Trying to figure out the best way to reset a react class component back to its initial state, I came across two different implementations. In my own version, I created an object with initial values and set the component's state equal to it: // My imp ...

javascriptreactjsecmascript-6

Obtain the value or values of radio buttons based on certain conditions

Recently, I have been delving into .JS and grappling with pre-existing code. One of the challenges I face involves extracting data from radio buttons after a particular selection is made. The gist of it is related to transportation requirements. Upon indi ...

javascriptjqueryasp.net

Load texture using ImageUtils with callback in Canvas Renderer

Currently, I am utilizing three.js revision 53. While attempting to load a texture in Canvas Renderer (specifically on Win7) and incorporating a callback for the onLoad event, the texture fails to display. Surprisingly enough, removing the callback functi ...

javascriptcanvaswebglthree.js

React Alert: Unable to update state in an unmounted React component

I have encountered an issue while using Redux in this particular file. When I click on the second component, I receive the following error message: Warning: Can't perform a React state update on an unmounted component. This is a no-op, but it ind ...

javascriptreactjsreduxreact-reduxreact-router