Access same field of every object in array of objects in mongodb aggregation

Question

I have the following documents in collection of mongodb:

banks:[{name:"ABC", amt:0},{name:"PQR", amt:-1},{name"XYZ", amt:3400}]

banks:[{name:"ABC", amt:-2},{name:"PQR", amt:2344},{name"XYZ", amt:7600}]

Like this say I have 10 documents and each document contains one banks array. Each banks array has 30 objects in it as shown above.

I am trying to write aggregation query in mongodb to get the count of objects that have "amt" less than equal to zero and greater than zero but so far unable to get it. Please help. Thanks in advance!

The output for above sample documents should be

{"greaterThanZero": 1, "lessThanEqualToZero": 2 }

{"greaterThanZero": 2, "lessThanEqualToZero": 1 }

AlexisG · Accepted Answer · 2020-09-08 13:48:38Z

1

First you have to separate yours documents with $unwind

Then with a $project and a $cond you tell for each document if it's greaterThanZero or lessThanEqualToZero

Finally you sum up greaterThanZero and lessThanEqualToZero with a $group

You can test it here : Mongo Playground

[
  {
    "$unwind": "$banks"
  },
  {
    "$project": {
      "greaterThanZero": {
        "$cond": [
          {
            "$gt": [
              "$banks.amt",
              0
            ]
          },
          1,
          0
        ]
      },
      "lessThanEqualToZero": {
        "$cond": [
          {
            "$lte": [
              "$banks.amt",
              0
            ]
          },
          1,
          0
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "greaterThanZero": {
        "$sum": "$greaterThanZero"
      },
      "lessThanEqualToZero": {
        "$sum": "$lessThanEqualToZero"
      }
    }
  }
]

answered Sep 8, 2020 at 13:48

AlexisG

2,4843 gold badges13 silver badges25 bronze badges

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

1 Comment

CoCo Over a year ago

Thank you for answering. Actually unwinding will not work as I have to further process the counts per document.

turivishal · Accepted Answer · 2020-09-08 13:48:51Z

0

You can do it with $reduce,

it checks condition using $cond if match then add one to value,

db.collection.aggregate([
  {
    $project: {
      lessThanEqualToZero: {
        $reduce: {
          input: "$banks",
          initialValue: 0,
          in: {
            $cond: [
              { $lte: ["$$this.amt", 0] },
              { $add: ["$$value", 1] },
              "$$value"
            ]
          }
        }
      },
      greaterThanZero: {
        $reduce: {
          input: "$banks",
          initialValue: 0,
          in: {
            $cond: [
              { $gt: ["$$this.amt", 0] },
              { $add: ["$$value", 1] },
              "$$value"
            ]
          }
        }
      }
    }
  }
])

Playground

answered Sep 8, 2020 at 13:48

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

1 Comment

CoCo Over a year ago

Thank you for your answer. I got my required results.

Collectives™ on Stack Overflow

Access same field of every object in array of objects in mongodb aggregation

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related