Sort in MongoDB by the larger value of two parameters

Sample Example Documents:

[
 {"subdoc": {"val":100,"extraVal":300}},
 {"subdoc": { "val":100 }                         // extraVal is not present in this one
]

Desired Outcome: after the aggregation process:

{
 "largest": // The entire first item with 300 as highest value
 "smallest": // The entire second item with 100 as smallest average
 "average": 150 // Average of 200 from first item and 100 from second item makes 150
}

Approach Used:

{ $sort: { 'subdoc.val': -1 } },
        {
          $group: {
            _id: null,
            average: { $avg: '$subdoc.val' },
            items: { $push: '$$ROOT' },
          },
        },
        { $set: { largest: { $first: '$items' } } },
        { $set: { smallest: { $last: '$items' } } },
        { $project: { largest: 1, smallest: 1, average: 1 } },

Current Approach does not account for extraVal field. Unable to find a way to incorporate a "larger one" logic

Considering: average: 

{ $max: { $greater: ['$subdoc.val', 'subdoc.extraVal'] }},

IMPORTANT NOTE: Optional presence of extraVal field.

javascriptmongodbsortingmongodb-queryaggregation-framework

Answer №1

Executing the operation twice on the given values.

db.collection.aggregate([
  {
    $group: {
      _id: null,
      largest: {
        $max: {
          $max: [
            "$subdocument.value",
            "$subdocument.additionalValue"
          ]
        }
      },
      smallest: {
        $min: {
          $min: [
            "$subdocument.value",
            "$subdocument.additionalValue"
          ]
        }
      },
      average: {
        $avg: {
          $avg: [
            "$subdocument.value",
            "$subdocument.additionalValue"
          ]
        }
      }
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        l: "$largest"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$l",
                {
                  $max: [
                    "$subdocument.value",
                    "$subdocument.additionalValue"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "largestItems"
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "let": {
        s: "$smallest"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$s",
                {
                  $min: [
                    "$subdocument.value",
                    "$subdocument.additionalValue"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "smallestItems"
    }
  }
])

Mongo Playground

Answer №2

Here is my approach to solving the problem, combining elements from @ray's solution with a filter instead of lookup:

{ $sort: { 'subdoc.value': -1 } },
        {
          $group: {
            _id: null,
            min: { $min: '$subdoc.value' },
            average: {
              $avg: { $avg: ['$subdoc.value', '$subdoc.additionalValue'] },
            },
            max: {
              $max: { $max: ['$subdoc.additionalValue', '$subdoc.value'] },
            },
            items: { $push: '$$ROOT' },
          },
        },
        {
          $set: {
            largest: {
              $first: {
                $filter: {
                  input: '$items',
                  as: 'item',
                  cond: {
                    $eq: [
                      {
                        $max: [
                          '$$item.subdoc.value',
                          '$$item.subdoc.additionalValue',
                        ],
                      },
                      '$max',
                    ],
                  },
                },
              },
            },
          },
        },
        { $set: { smallest: { $last: '$items' } } },
        { $project: { largest: 1, smallest: 1, averaget: 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

Tips for transmitting one or multiple data using jquery.ajax

I found this code on stackoverflow and it is working well for uploading multiple files. However, I am facing an issue where I cannot send additional parameters along with the file upload. Despite trying various methods, I have been unsuccessful in sending ...

javascriptphpjqueryhtmlcodeigniter

Shutting down the server following the completion of the mocha test, yet the data remains intact

Currently, I am working on testing some data using a REST API. The data is stored as an array within the project. My goal is to have the data "reset" every time a test suite runs, so that I can accurately determine the amount of data in the array. Howeve ...

javascriptnode.jsexpressmocha.js

Simple Method for Converting JSON to CSV (Even with Arrays as Fields)

Is there a way to convert my parse.com JSON data into a CSV file where each number within an array is saved within its own field? I have tried using various online converters, but they fail due to the large size of my data. I am looking for a solution that ...

javascriptjsoncsvparse-platformexport-to-csv

Supabase's newly uploaded audio blobs are experiencing difficulties when it comes to

I've integrated Supabase storage for storing audio blobs. After retrieving the blob from an API call, it is uploaded successfully to the storage bucket. However, when attempting to play the audio file, nothing happens and the duration shows as 0:00. S ...

javascriptdatabasenext.jsblobsupabase

What could be causing setState to not function correctly in React JS after an item is selected?

Having trouble with my setState function not properly rendering the component. To reproduce the issue: Select any area, for example, see the image below in the first row of the second column. Observe the updated row data in the console. I'm attempt ...

javascriptreactjs

Hapi/Node.js Reference Error for Request: Troubleshooting the Issue

I am facing an issue with my two API handlers: module.exports.loginWeb = function (request, reply, next) { if (request.auth.isAuthenticated) { return reply.redirect('/'); } if(request.method === 'get'){ rep ...

javascriptnode.jshapi.js

Executing a Function within UseEffect

I need help calling the function onSaveInputValue within the useEffect to make sure that the value is passed to the childInputValue hook whenever the page loads. const onSaveInputValue = (value) => { setChildInputValue(value); consol ...

javascriptreactjsreact-hooksreact-functional-component

Storing information in a database via a web API using AngularJS

I am new to the world of AngularJS and Web API. I am currently facing challenges when trying to send data to a database through Web API and Angular. I have successfully managed to load JSON data from the Web API. Any assistance on this matter would be grea ...

javascriptjsonangularjspostwebapi

Locate a designated number of data entries in mongoDB using Python

I've been attempting to retrieve the initial 1000 records from my mongodb collection using Python and the find() method. for rooms in range(1000): rooms = list(db.rooms.find()) print(rooms) However, I'm encountering an issue where it pr ...

pythonmongodbmongodb-querypymongo

Attempting to eliminate redundant data retrieved from an XML file being presented on my webpage

I need help deleting duplicate artists that appear when retrieving information from an XML file using JQuery. How can I achieve this? Check out the JS file below: $(function(){ $(window).load(function(){ $.ajax({ url: 'http://imagination ...

javascriptjqueryxmlajax

How to display nested arrays in AngularJs

Within my array contacts[], there are multiple contact objects. Each of these contact objects contain an array labeled hashtags[] consisting of various strings. What is the best way to display these hashtags using ng-repeat? ...

javascriptarraysangularjs

Tips for generating a fresh array by conditionally including an extra property based on the comparison of two arrays

I am working with two different arrays: lockers: [ { locker_id: 1, label: { size: 1, label: "small" } }, { locker_id: 2, label: { size: 3, label: "medium" } }, { locker_id: 3 ...

javascriptarraysreactjsecmascript-6merge

Tips on dynamically updating the data-bs-slide-to value using Bootstrap 5 and Vue.js 2

I have been working on creating a dynamic carousel and I've almost got it to work. However, I am facing an issue with populating the 'data-bs-slide-to' property in Bootstrap 5 automatically based on a given array. Here is the complete code: ...

javascripthtmlvue.jsbootstrap-carousel

Retrieving Parts of a URL in AngularJS

Is there a way to extract all parts of a URL after "/"? For example: http://myadress.com/#/example/url/here/param1 Result: ['example', 'url', 'here']. Currently, $routeParams only returns an object with route parameters lik ...

javascriptangularjs

Preventing MongoCursorTimeoutException in PHP by setting an infinite timeout duration

Currently, I have a PHP script that retrieves data from a MongoDB database. The issue I am facing is related to handling a large amount of data in my database, and it's resulting in the following exception being thrown: PHP Fatal error: Uncaught excep ...

phpmongodbphp-mongodb

Step-by-Step Guide on Monitoring alterations within a division in Vue 3

Is there a way to monitor changes such as class modifications or text updates within a div using Vue 3? from: <p class="font-bold">My text</p> to: <p class="font-bold color-red">My updated text.</p> I attempte ...

javascriptvue.jsvuejs3watch

Integrating a new column into the MeanJS database

Is it possible to add a new column like Balance to the mongodb database using the default structure of a meanjs.org database? { "_id" : ObjectId("597695d922fa1025a453ed39"), "salt" : "aGHDfk3+7Bg1lR8YI2JTAg==", "displayName" : "sample sample ...

mongodb

The jQuery newsfeed is powered by the $.each() method

I'm having trouble setting up a newsfeed on my website. It's supposed to display all the current news and exclude any expired ones. I have a webpage where I can add news, so the JSON data is constantly changing. Here's an example of the PHP ...

javascriptjqueryjson

Is it possible to utilize reference storage in mongoDB for data storage purposes?

My goal is to prevent data duplication by avoiding embedded information, as well as steering clear of field relations using IDs. For example: { Books: [ { id: 1, name: "Foo1", author: referenceToTheAuthorWithId1 }, { id: 2, name: "Foo2", ...

databasemongodbnosql

Can anyone help explain how to use Javascript to reverse the order of table row results that are being output

I am struggling to reverse the order of a table returned from a PHP database using JavaScript. Despite trying to use the reverse() method, I can't seem to get it to work. I would greatly appreciate any guidance you can provide. Below is the JavaScrip ...

javascriptphparrays