Find all documents in MongoDB where a certain field does not exist, and if it does exist, apply the $max field operator condition

I am searching for a query to use in the $match stage of my aggregation that works similarly to the one mentioned in this particular question on Stack Overflow, but with a twist...

  • If a specific field (in my case, it's called rank) does not exist in the document, I want to include it in the results.
  • However, if the field exists, I want to apply a $operator condition (such as $max in my case) to this field and include all documents that meet this condition in the results.

Check out this example collection on MongoPlayground.

The desired result should look like this:

[
  {
    "method": 3,
    "item": 1,
    "rank": 3 // This document has the rank field and meets the {rank: $max} condition
  },
  {
    "method": 4,
    "item": 1 // This is included because the document doesn't have the rank field at all
  },
  {
    "method": 5,
    "item": 1 // This document is also included for the same reason as above
  }
]

Here are some approaches I've already attempted:

            
{
                $match: {
                    $or: [
                        {item: id, rank: {$exists: true, $max: "$rank"}}, 
                        {item: id, rank: {$exists: false}}
                    ]
                }
            }

UPDATE: Currently, I might not be restrained to just using the $match stage. The $project stage could also prove useful after the initial match, allowing me to retrieve every document based on the id regardless of whether or not they have the rank field. Then, during the $project stage, I can differentiate based on the existence of the rank field.

javascriptmongodbmongoosemongodb-queryaggregation-framework

Answer №1

Here is a useful query for your MongoDB collection:

db.collection.aggregate([
  {
    $match: {
      item: id
    }
  },
  {
    $group: {
      _id: "$item",   // Modify this to match your searching field
      max: {
        $max: "$rank" // Update this to the field where you want to apply $max
      },
      data: {
        $push: "$$ROOT"
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $match: {
      $expr: {
        $or: [
          {
            $eq: [
              {
                $type: "$data.rank"
              },
              "missing"
            ]
          },
          {
            $eq: [
              "$data.rank",
              "$max"
            ]
          }
        ]
      }
    }
  },
  {
    $replaceWith: "$data"
  }
])

Check it out on MongoPlayground

Answer №2

I have come across a solution that differs from @Valijon's approach, yet still builds upon the same underlying logic. Here is my query:

db.collection.aggregate([
  {
    $match: {
      item: id
    }
  },
  {
    $project: {
      method: 1,
      item: 1,
      rank: {
        $ifNull: [
          "$rank",
          0
        ]
      }
    }
  },
  {
    $group: {
      _id: "$item",
      data: {
        $addToSet: "$$ROOT"
      },
      min_value: {
        $min: "$rank"
      },
      max_value: {
        $max: "$rank"
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $match: {
      $or: [
        {
          $expr: {
            $eq: [
              "$data.rank",
              "$max_value"
            ]
          }
        },
        {
          $expr: {
            $eq: [
              "$data.rank",
              "$min_value"
            ]
          }
        },
      ]
    }
  }
])

In my query, I leverage the $project stage to assign empty fields a value of 0. This value could also be -1 or any other unused value within the collection. Subsequently, I filter and analyze the results accordingly.

MongoPlayground

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

The generation of React Native Jest coverage results may vary depending on how an ES6 module is exported

I am working with a React Native button component that consists of its own index.js and styles.js files. The content of styles.js is as follows: import { StyleSheet } from "react-native"; export default StyleSheet.create({ container: { borderRadius ...

javascriptreact-nativejestjs

Step-by-step guide to mocking or spying on an imported function during Angular unit testing

Imagine you have an Angular 6 component with a method test that returns a certain value: import { doSomething } from './helper'; @Component({ ... }) export class AppComponent { test() { const data = doSomething(1); retur ...

javascriptangularunit-testingmockingspy

Prevent certain dates from being selected in a designated input field

I am facing an issue with disabling certain array dates for a specific input field in a datepicker calendar. Even though I have included the script to exclude those dates, they are not getting disabled for that particular input field. html <input cla ...

javascriptjquery

Experiencing difficulties coding SVGs

Struggling with manipulating SVGs in JavaScript and looking to extend a line by clicking a button? Check out this code snippet I've included in the head tag: <script type="text/javascript"> x=135; y=135; var container = document.getElementById( ...

javascriptsvg

Passing a date string from the Controller to JavaScript using the AJAX method

Below is the controller I have written: [HttpPost] public JsonResult GetTrackList(POS_model item) { //item.item_code //item.track_type if (item.track_type != "Regular") { POS pos = new POS(); ...

javascriptjqueryajaxasp.net-mvc-4

Trying out a React component that relies on parameters for connection

Having an issue while attempting to test a connected react component that requires a props.params.id in order to call action creators. During the testing process, when checking if the component is connected to the store, an error "Uncaught TypeError: Canno ...

javascriptreactjsmocha.jsreduxkarma-mocha

Using AngularJS, what methods can I use with ng-show to filter through an array and identify specific values?

I have a model called Program, which has the following structure: var ProgramSchema = new Schema({ permissions: [{ user: { type: Schema.ObjectId, ref: 'User' }, roles: { type: [{ type: Stri ...

javascriptjsonangularjsangularjs-directivemeanjs

Guide on making a Key Object in Node.js

I've been struggling for the past few days to decode RSA base64 strings in a Node.js environment. Unfortunately, many of the available modules lack proper documentation, or I might be overlooking something crucial. The issue arises from the fact that ...

javascriptnode.jsrsa

Is it true that Firefox fails to display javascript errors on AJAX 'threads'?

Currently, I have a function called test_fn that is being called in two ways: (a) Through an onclick event of a button; or (b) from the success callback within a jQuery AJAX request, as shown here: $.ajax({ type: 'POST', ... succes ...

javascriptajax

Leveraging IntersectionObserver to identify the video in view on the screen

Our Objective I aim to implement a swipe functionality for videos where the URL changes dynamically based on the ID of the currently displayed video. Challenges Faced Although I managed to achieve this with code, there is an issue where the screen flashe ...

javascriptreactjstypescriptintersection-observer

Angular service that iterates through each $q.promise

Here's the function I am working with: this.getBenchmarkData = function () { var benchmarkData = []; var d = $q.defer(); users.forEach(function(user){ var dataArray = []; modules.forEach(function (module) { ...

javascriptangularjsasynchronouspromiseangular-promise

Tips on utilizing ajax to load context without needing to refresh the entire page

As a beginner in AJAX, I have some understanding of it. However, I am facing an issue on how to refresh the page when a new order (order_id, order_date, and order_time) is placed. I came across some code on YouTube that I tried implementing, but I'm n ...

javascriptphpjqueryajax

Exploring the process of querying two tables simultaneously in MySQL using PHP

I currently have a search box in my PHP file that only searches from the "countries" table. However, I also have another table called "continent" and I would like the search box to retrieve results from both the "countries" and "continent" tables. Here is ...

javascriptphphtmlmysql

Issue with Typescript Application not navigating into the node_modules directory

After attempting to load the app from the root directory of our server, it became clear that this was not a practical solution due to the way our application uses pretty URLs. For instance, trying to access a page with a URL like http://www.website.com/mod ...

javascripttypescriptangular

MongoDB's addToSet function does not include duplicates when adding values

I have a MongoDB collection that looks like this: [ { "acronym": "front", "references": [ { "date": "2020-03-04", "value": "5.6" }, { ...

javascriptnode.jsmongodbcrud

Combining two fields in Prisma to create a distinct and exclusive link

Within my PostgreSQL database, I have a table that includes a column for the server's ID and a column for the user's ID, along with additional columns detailing punishments assigned to the user. In the 'schema.prisma' file: model users ...

javascripttypescriptprisma

Customizing translations for various domains in Vue-i18n

Our app has a global reach and our company is undergoing a rebranding process in certain markets. For instance, we are currently known as "Acme Company" in the U.S. and Canada, but now we aim to be recognized as "Acme Company" in the U.S. and "Foo Company ...

javascriptvue.jsvuejs2internationalizationvue-i18n

Inquiries regarding JavaScript syntax in a nutshell

I have come across this syntax numerous times, but my attempts to search for it on Google have been unsuccessful. I am hoping to find some help here: <script> (function(){ //code goes here })(); </script> Can someone explain ...

javascriptsyntax

Implementing mouse hover functionality for fieldset in EXTJS

I am looking to enhance the following code snippet by adding a mouse hover event that changes the background color of the fieldset item and displays an image next to it. Can someone assist me with this modification? var mainGroup = { ...

javascriptcssextjsmouseover

Error encountered during JSON parsing: unexpected non-whitespace character found after the JSON data on line 1

I have implemented a simple JavaScript code (using AngularJS, but that's not important) like this: app = angular.module('app', []); app.controller("NavCtrl",function($scope,$http){ var serviceBase = 'api/'; $http.get(serv ...

javascriptphpjson