How to compare two elements inside nested arrays using mongodb?

Question

I am trying to compare the price between the first and last element inside a nested array.

The following is a part of my data:

priceRange:[
   [1600732800000,5.28424438726287],
   [1600819200000,5.08434522342211],
   [1600905600000,3.491229168216385],
   [1600992000000,4.573890580327729],
   [1601078400000,4.702973876095848]
]

So in essence I want to know if the second element inside the first array (5.28424438726287) is bigger than the second element inside the last array (4.702973876095848)

I have hard time selecting the last array inside the nested array: The folowing didn't work:

$expr: { $gt: [{ $last: "$priceRange" }, 0.000001] }

I can select the first element:

priceRange.0.1

And then there is the question how do you compare them while also having other filter constraints?

{name: "Some stock", comparing the prices}

Buzz Moschetti · Accepted Answer · 2022-01-26 12:52:02Z

3

You can add this aggregation stage:

This query create a fields called isBigger where compare the values you want:

The last (second) value from the first array
The last (second) value from the last array

And the output will be true or false if is bigger the number or not.

db.collection.aggregate([
  {
    "$project": {
      "isBigger": {
        "$cond": {
          "if": {
            "$gt": [
              {
                "$last": {
                  "$first": "$priceRange"
                }
              },
              {
                "$last": {
                  "$last": "$priceRange"
                }
              }
            ]
          },
          "then": true,
          "else": false
        }
      }
    }
  }
])

Example here

edited Jan 26, 2022 at 12:52

Buzz Moschetti

7,6433 gold badges27 silver badges41 bronze badges

answered Jan 26, 2022 at 12:45

J.F.

15.3k10 gold badges41 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BitByBit Over a year ago

Thanks. What I would add on top of that is if you need to use find with multiple contraints then the following is an options: myquery = { "$expr": { "$and": [ {"$lt": [{"$last": {"$first": "$priceRange"}},{"$last": {"$last": "$priceRange"}}]}, {"$gt": ["$market_data.market_cap.usd", 10000000]} ] } }

Collectives™ on Stack Overflow

How to compare two elements inside nested arrays using mongodb?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related