Mongodb aggregation - count arrays with elements having integer value greater than

Question

I need to write a MongoDB aggregation pipeline to count the objects having arrays containing two type of values:

>=10
>=20

This is my dataset:

[
 { values: [ 1, 2, 3] },
 { values: [12, 1, 3] },
 { values: [1, 21, 3] },
 { values: [1, 2, 29] },
 { values: [22, 9, 2] }
]

This would be the expected output

{
  has10s: 4,
  has20s: 3
}

Mongo's $in (aggregation) seems to be the tool for the job, except I can't get it to work.

This is my (non working) pipeline:

db.mytable.aggregate([
  {
    $project: {
      "has10s" : { 
         "$in": [ { "$gte" : [10, "$$CURRENT"]}, "$values"]} 
    },
     "has20s" : { 
         "$in": [ { "$gte" : [20, "$$CURRENT"]}, "$values"]} 
    }
  },
  { $group: { ... sum ... } }
])

The output of $in seems to be always true. Can anyone help?

Akrion · Accepted Answer · 2019-06-20 19:25:23Z

1

You can try something like this:

db.collection.aggregate([{
    $project: {
      _id: 0,
      has10: {
        $size: {
          $filter: {
            input: "$values",
            as: "item",
            cond: { $gte: [ "$$item", 10 ] }
          }
        }
      },
      has20: {
        $size: {
          $filter: {
            input: "$values",
            as: "item",
            cond: { $gte: [ "$$item", 20 ] }
          }
        }
      }
    }
  },
  {
    $group: {
      _id: 1,
      has10: { $sum: "$has10" },
      has20: { $sum: "$has20" }
    }
  }
])

Using $project with $filter to get the actual elements and then via $size to get the array length.

See it working here

answered Jun 20, 2019 at 19:25

Akrion

18.6k1 gold badge38 silver badges58 bronze badges

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

1 Comment

snovelli Over a year ago

I was afraid this was going to be the answer :( I'll wait and see if anyone has any more declarative solution, otherwise I'll accept your. For not, thanks! :)

Collectives™ on Stack Overflow

Mongodb aggregation - count arrays with elements having integer value greater than

This is my dataset:

This would be the expected output

This is my (non working) pipeline:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

This is my dataset:

This would be the expected output

This is my (non working) pipeline:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related