Ways to conduct a comparison of elements within an array within the same document

Question

Ways to conduct a comparison of elements within an array within the same document

Is there a way to compare elements, by their index, within the same array in a MongoDB document? For example, consider the following entry:

{ "_id" : ObjectId("1"), "arr" : [ { "int" : 100 }, { "int" : 10 } ] }

I have a collection with numerous similar entries and I'm interested in querying the collection to identify those documents where the value of arr[0].int is greater than arr[1].int. It would also be helpful to determine the percentage difference between these two elements. For instance, in the given example,

index 1 is 10 times less than index 0

.

The query below successfully identifies elements in mongoDocs that are greater than a specified value:

db.collection.find( { "arr.0.int" : { $gt: 10 }})

I've experimented with different approaches but haven't found one that meets my requirements effectively, especially considering the large size of the dataset. Any insights on performance optimization would be greatly appreciated!

Thank you!

javascript mongodb performance

Answer 1

Answer №1

One way to handle this situation is by utilizing the $redact operator along with $arrayElemAt. This allows you to evaluate each index based on a specific condition.

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$arrayElemAt": ["$arr.int", 0] },
          { "$arrayElemAt": ["$arr.int", 1] }
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

The $cond operator within the pipeline stage checks if the specified condition is met, and includes or excludes documents accordingly. This method relies on native operators for optimal performance.

In MongoDB 3.6, a more concise syntax using $expr is available:

db.collection.aggregate([
  { "$match": { 
    "$expr": {
      "$gt": [
        { "$arrayElemAt": ["$arr.int", 0] },
        { "$arrayElemAt": ["$arr.int", 1] }
      ]
    }
  }}
])

This can also be used in a .find() operation:

db.collection.find({
  "$expr": {
    "$gt": [
      { "$arrayElemAt": ["$arr.int", 0] },
      { "$arrayElemAt": ["$arr.int", 1] }
    ]
  }
})

MongoDB supports JavaScript evaluation through $where, but it may not offer the same level of performance as native operators:

db.collection.find({
  "$where": "return this.arr[0].int > this.arr[1].int"
})

To calculate a ratio in the results, an aggregation pipeline must be used:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$arrayElemAt": ["$arr.int", 0] },
          { "$arrayElemAt": ["$arr.int", 1] }
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }},
  { "$addFields": {
    "ratio": {
      "$divide": [
        { "$arrayElemAt": ["$arr.int", 1] },
        { "$arrayElemAt": ["$arr.int", 0] }
      ]         
    }
  }}
])

Consider storing common logic directly in your documents to avoid repetitive calculations. If that's not feasible, calculating conditions dynamically may be the only option, though it's important to assess the necessity of such calculations.

Answer 2

One way to handle this situation is by utilizing the $redact operator along with $arrayElemAt. This allows you to evaluate each index based on a specific condition.

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$arrayElemAt": ["$arr.int", 0] },
          { "$arrayElemAt": ["$arr.int", 1] }
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }}
])

The $cond operator within the pipeline stage checks if the specified condition is met, and includes or excludes documents accordingly. This method relies on native operators for optimal performance.

In MongoDB 3.6, a more concise syntax using $expr is available:

db.collection.aggregate([
  { "$match": { 
    "$expr": {
      "$gt": [
        { "$arrayElemAt": ["$arr.int", 0] },
        { "$arrayElemAt": ["$arr.int", 1] }
      ]
    }
  }}
])

This can also be used in a .find() operation:

db.collection.find({
  "$expr": {
    "$gt": [
      { "$arrayElemAt": ["$arr.int", 0] },
      { "$arrayElemAt": ["$arr.int", 1] }
    ]
  }
})

MongoDB supports JavaScript evaluation through $where, but it may not offer the same level of performance as native operators:

db.collection.find({
  "$where": "return this.arr[0].int > this.arr[1].int"
})

To calculate a ratio in the results, an aggregation pipeline must be used:

db.collection.aggregate([
  { "$redact": {
    "$cond": {
      "if": {
        "$gt": [
          { "$arrayElemAt": ["$arr.int", 0] },
          { "$arrayElemAt": ["$arr.int", 1] }
        ]
      },
      "then": "$$KEEP",
      "else": "$$PRUNE"
    }
  }},
  { "$addFields": {
    "ratio": {
      "$divide": [
        { "$arrayElemAt": ["$arr.int", 1] },
        { "$arrayElemAt": ["$arr.int", 0] }
      ]         
    }
  }}
])

Consider storing common logic directly in your documents to avoid repetitive calculations. If that's not feasible, calculating conditions dynamically may be the only option, though it's important to assess the necessity of such calculations.

Ways to conduct a comparison of elements within an array within the same document

Answer №1

Similar questions

How to dynamically set a computed background image in Vue 2

JavaScript and PHP are successfully displaying a success message despite the data not being saved to the database

Removing a dynamic component in Angular

Divide the Javascript code from the HTML Canvas

Purge ajax result upon completion of server operation

A guide on how to alternate between ng-hide and ng-show using separate controllers by utilizing a shared factory toggle state instance

Pusher authentication issue: socket ID not defined

Switch between showing the Font Awesome TitleText and its associated Class with a single click

ways to undo modifications made to a value within an input field using jquery or javascript

Storing the results of an Ajax call in a global variable

AngularJS modifying shared factory object across controllers

Encountering a MODULE NOT FOUND error when using express.js

In React, when utilizing the grid system, is there a way to easily align items to the center within a 5 by

Utilizing conditional statements within the array.forEach method to select specific sub-objects within an array of objects

Tips for deactivating data monitoring in a Vue.js component attribute

Automatically refresh the Redux state in ReactJS at regular intervals to ensure the data is always up-to

Is it possible to modify a portion of the zod schema object according to the value of a

How come I am unable to assign a value to my dropdown element following an AJAX request?

What is the best way to eliminate all instances of the period symbol from an array?

Expanding div width with jQuery as a percentage