Perform an aggregate nested group operation in MongoDB to retrieve the most recent updated document

Within my collection, I have assigned a person and status as shown in the data below.

[
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411771", "STATUS": 1, "UPDATE_DATE": ISODate("2016-12-21T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411772", "STATUS": 4, "UPDATE_DATE": ISODate("2016-12-22T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411774", "STATUS": 4, "UPDATE_DATE": ISODate("2016-12-23T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322398", "SPEC_ID" : "58411774", "STATUS": 3, "UPDATE_DATE": ISODate("2016-12-24T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322311", "SPEC_ID" : "58411775", "STATUS": 1, "UPDATE_DATE": ISODate("2016-12-25T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322311", "SPEC_ID" : "58411779", "STATUS": 3, "UPDATE_DATE": ISODate("2016-12-23T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322322", "SPEC_ID" : "58411777", "STATUS": 1, "UPDATE_DATE": ISODate("2016-12-20T04:10:23.000Z")},
 {"ASSIGN_ID": "583f84bce58725f76b322322", "SPEC_ID" : "58411778", "STATUS": 4, "UPDATE_DATE": ISODate("2016-12-21T04:10:23.000Z")}
]

I aim to group this data by ASSIGN_ID, then within each assignment group by STATUS, and provide the count of each STATUS along with the most recently updated SPEC_ID based on the UPDATE_DATE. The desired result should look like the following:

[  
   {  
      "ASSIGN_ID":"583f84bce58725f76b322398",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":2
         }
      ],
      "SPEC_ID": "58411774"
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322311",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         }
      ],
      "SPEC_ID": "58411775"
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322322",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":1
         }
      ],
      "SPEC_ID": "58411778"
   }
]

Despite being able to write a nested grouping by STATUS and ASSIGN_ID, I struggled to retrieve the most recent SPEC_ID. Refer to the query below that was used:

            Modal.aggregate([
              {"$group": {
                "_id": {
                    "INSPECTED_BY": "$INSPECTED_BY",
                    "STATUS": "$STATUS"
                },
                "total": { "$sum": 1 }
              }},
              {"$group": {
                "_id": "$_id.INSPECTED_BY",
                "data": { "$push": {
                    "STATUS": "$_id.STATUS",
                    "total": "$total"
                }}
              }}]);

The output dataset from the above query is structured as follows:

[  
   {  
      "ASSIGN_ID":"583f84bce58725f76b322398",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":2
         }
      ],
   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322311",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":3,
            "COUNT":1
         }
      ],

   },
   {  
      "ASSIGN_ID":"583f84bce58725f76b322322",
      "STATUS_GROUP":[  
         {  
            "STATUS":1,
            "COUNT":1
         },
         {  
            "STATUS":4,
            "COUNT":1
         }
      ],
   }
]
javascriptmongodbmongoosemongodb-queryaggregation-framework

Answer №1

To achieve the desired outcome, it is important to first use the $sort operator on the documents entering the pipeline. Subsequently, in the $group stage, return the latest value of the SPEC_ID field for each group using the $first accumulator.

To obtain the desired result, execute the following pipeline:

Model.aggregate([
    { "$sort": { "ASSIGN_ID": 1, "STATUS": 1, "UPDATE_DATE": -1 } },
    {
        "$group": {
            "_id": {
                "ASSIGN_ID": "$ASSIGN_ID",                
                "STATUS" : "$STATUS",
            },
            "SPEC_ID": { "$first": "$SPEC_ID" },
            "COUNT": { "$sum": 1 }
        }
    },
    {
        "$group": {
            "_id": "$_id.ASSIGN_ID",
            "STATUS_GROUP": { 
                "$push": {
                    "STATUS": "$_id.STATUS",
                    "COUNT": "$COUNT"
                }
            },
            "SPEC_ID": { "$first": "$SPEC_ID" }
        }
    }
]).exec(callback);

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

Tips for handling the RETURN value in an AJAX call to a server-side script

Seeking guidance on how to properly validate an email address on the server side using PHP and jQuery. Currently, I am utilizing the "$.post()" function in jQuery for this task, but I am aware that it can also be done with '$.ajax()'. The issue ...

javascriptphpjqueryajax

Greetings: Obtaining an array of text within the <td> tags

Here is the HTML Source: <td bgcolor="#ffffbb" colspan=2><font face="Verdana" size=1>2644-3/4<br>QPSK<br><font color="darkgreen">&nbsp;&nbsp;301</font> - 4864</td> I am looking to extract text array wit ...

javascriptnode.jscheerio

Eliminate jQuery's delayed blinking effect with the use of an event

Utilizing the mouseenter and mouseleave events, I have implemented a functionality to add a button (not actually a button) to an <li>. However, there seems to be a problem with my code. The button appears and disappears on mouseleave and mouseenter, ...

javascriptjqueryhtmlcss

Finding the value within a specified range of two numbers in Vue.js

When my code generates a result of 0, I need to determine the corresponding value based on the basic_salary. For example, if the basic_salary is 40,000, it should return the value of "ee": 400. <table class="table table-hover table-borde ...

javascriptvue.js

Inspect the data attribute and modify the class

I am looking to determine if a data attribute has a specific value and then update the class associated with it. For example, in my HTML code: <li class="country active" data-country-code="ca"><div class="flag ca"& ...

javascriptjqueryhtml

What is the best way to modify a React ref when the child elements of ref.current are altered?

I'm currently utilizing React refs for managing click events outside of an element. import { useEffect } from 'react'; export const useClickOutside = (ref, callback = () => {}) => { const handleClick = e => { if (ref.current ...

javascriptreactjsreact-hooks

Convert Ajax null value to NoneType in web2py

Every time I save information on a page, an AJAX request is sent with all the necessary data to be stored in the database. The format of this data looks similar to this example: {type: "cover", title: "test", description: null, tags: null} However, when ...

javascriptpythonajaxweb2py

Error: Angular encountered an unexpected token in the syntax

I am encountering an issue while trying to incorporate a user's profile updater on my website. Whenever I attempt to access a user's profile on the site, I run into this Angular error: main.ts:6 ERROR SyntaxError: Unexpected token 'e', ...

javascripthtmlangulartypescriptprofile

Display data in an HTML table using PHP and AJAX within a single file

My project involves using PHP to populate an HTML table with random numbers upon page load. What I'm aiming to achieve is the ability to click a button and have the table populated with new numbers by invoking PHP through an ajax request. The challeng ...

javascriptphpajax

What is the best way to manage various versions of JS libraries across different branches?

As a novice developer, I dabble in creating applications for personal use. My go-to tools are the Quasar framework for the front end and Python for the back end. I maintain a git repository where the master branch houses my "production code," but now I am ...

javascriptgitvue.jsquasar-framework

Extracting raw data from the dojo.xhrGet request

When working with a JSP and servlet, I encountered an issue. In the JSP, I make an ajax call to the servlet which in turn calls a REST API to fetch JSON data. Using json.serialize(true);, I format the JSON data in the servlet before sending it to the front ...

javascriptajaxjsonjspdojo

What steps should I follow to incorporate this filter into my code?

Currently, I am working with a list of names obtained from an array using the Fetch method. I have implemented a searchHandler method which is triggered by a button click, and it logs the input data in the console: https://codesandbox.io/s/jovial-lovelac ...

javascript

Transmit a file using multipart with XMLHttpRequest

Is it possible to use XMLHttpRequest to send a multipart file to a servlet? I am currently working on a form that needs to be submitted as multipart, but I am not receiving a response after successfully uploading it. It is important that the process happe ...

javascriptajaxservletsfile-uploadmultipart

retrieve data from an asynchronous request

Utilizing the AWS Service IotData within an AWS Lambda function requires the use of the AWS SDK. When constructing the IotData service, it is necessary to provide an IoT endpoint configuration parameter. To achieve this, another service is utilized to obta ...

javascriptnode.jsaws-lambda

SolidJS createEffect will only trigger on changes after the initial rendering

When I was searching for a solution, I came across the need to only trigger a reaction after my component had been rendered for the first time. For instance: function InputName() { const [name, setName] = createSignal(""); const [nameError, ...

javascriptsolid-js

Display information from a mysql database table within a selection menu

Currently, I am working on a dropdown menu that should display data from a MySQL table. However, I am facing an issue which is outlined below: In my PHP script, I have approached it in the following manner: <label class="col-form-label" for="formGrou ...

javascriptphpjqueryajaxmysqli

Aggregated data from multiple elements and sub-documents in MongoDB using group aggregation technique

As someone new to MongoDB, I am currently exploring how to group data based on two elements within a cube structure. One element is time and the other is a sub-document. Here's an example of my data structure: { "_id" : ObjectId("52d931f9f613 ...

mongodb

Expanding Submenu Width

Currently working on developing a Dynamic Sub-menu for Wordpress, similar to the one shown here: . However, I am facing an issue with the width as it is set to 'auto' but not aligning the sub-menu properly. I would like the sub-menus to float lef ...

javascriptcsswordpress

Toggle the visibility of a component by clicking on a specific title within a table, dependent on the column title in Angular 9

Currently, I am developing an Angular application focused on creating a COVID-19 tracking app. In this project, I have designed 2 components - Component A displays a list of all states, while Component B lists all districts within a particular state. To ...

javascriptangulartypescriptangular2-template

Intermittent shimmer of translucent hues appearing as solid

I have a collection of curved see-through lines stacked inside a rotating container. The opacity settings on the lines are inconsistent, working at times but not always. It's puzzling, as I can't determine what causes it to function in certain s ...

javascriptthree.js