Count size from an array that matches condition

user_14673069 0
Viewed 353

I have a document like:

[
  {_id:1, field: {array: [1,2,3,4,1,1] }},
  {_id:2, field: {array: [5,1,1,1,1,1] }},
  {_id:3, field: {array: [3,2,3,4,1,2] }}
]

I want to count the array elements which eq 1.

The result is:

[
  {_id: 1, count: 3},
  {_id: 2, count: 5},
  {_id: 3, count: 1}
]
mongodb
2 Answers

You can try an aggregation query,

  • $filter to iterate loop of an array and check condition if the value is 1
  • $size to get total elements of the filtered array
db.collection.aggregate([
  {
    $project: {
      count: {
        $size: {
          $filter: {
            input: "$field.array",
            cond: { $eq: ["$$this", 1] }
          }
        }
      }
    }
  }
])

Playground

The second possible option,

  • $reduce to iterate loop of array
  • $cond to check if the value is equal to 1
  • if it is 1 then $add plus one in initialValue otherwise return the same number
db.collection.aggregate([
  {
    $project: {
      count: {
        $reduce: {
          input: "$field.array",
          initialValue: 0,
          in: {
            $cond: [
              { $eq: ["$$this", 1] },
              { $add: ["$$value", 1] },
              "$$value"
            ]
          }
        }
      }
    }
  }
])

Playground

user_89871280

Here is an extended solution that will get the distribution of the value of all elements of field.array.

db.foo.aggregate([
    {$addFields: {distrib: {$reduce: {
        input: "$field.array",
        initialValue: {"1":0,"2":0,"3":0,"4":0,"5":0},
            in: {
        "1":{$add:["$$value.1",{$toInt:{$eq:[1,"$$this"]}}]},
        "2":{$add:["$$value.2",{$toInt:{$eq:[2,"$$this"]}}]},
        "3":{$add:["$$value.3",{$toInt:{$eq:[3,"$$this"]}}]},
        "4":{$add:["$$value.4",{$toInt:{$eq:[4,"$$this"]}}]},
        "5":{$add:["$$value.5",{$toInt:{$eq:[5,"$$this"]}}]}
            }
        }}
    }}
]);

And typically the follow-on question is how to get distributions across multiple docs, which in a way is "easier" because $bucket works across a pipeline of documents:

db.foo.aggregate([
    {$unwind: "$field.array"},
    {$bucket: {
        groupBy: "$field.array",
        boundaries: [1,2,3,4,5,6,7,8,9,10],
        output: {
            n: {$sum:1}
        }
    }}
]);

Alternately, you can add this stage after the $addFields/$reduce stage. It yields messy arrays of 1 object of which only field n is interesting but you can easily get the value in the client-side with doc['1'][0]['n'], doc['2'][0]['n'], etc.

    ,{$facet: {
        "1": [ {$group: {_id:null, n:{$sum:"$distrib.1"}}} ],
        "2": [ {$group: {_id:null, n:{$sum:"$distrib.2"}}} ],
        "3": [ {$group: {_id:null, n:{$sum:"$distrib.3"}}} ],
        "4": [ {$group: {_id:null, n:{$sum:"$distrib.4"}}} ],
        "5": [ {$group: {_id:null, n:{$sum:"$distrib.5"}}} ]
    }}

If you really want to make the return structure simple, add this stage at the end to collapse the [0]['n'] data:

      ,{$addFields: {
          "1": {$let:{vars:{q:{$arrayElemAt:["$1",0]}},in: "$$q.n"}},
          "2": {$let:{vars:{q:{$arrayElemAt:["$2",0]}},in: "$$q.n"}},
          "3": {$let:{vars:{q:{$arrayElemAt:["$3",0]}},in: "$$q.n"}},
          "4": {$let:{vars:{q:{$arrayElemAt:["$4",0]}},in: "$$q.n"}},
          "5": {$let:{vars:{q:{$arrayElemAt:["$5",0]}},in: "$$q.n"}}
      }}

In MongoDB 5.0, the new $getField function makes this a little more straightforward:

      ,{$addFields: {
          "1": {$getField:{input:{$arrayElemAt:["$1",0]}, field:"n"}}
...
user_25021260
Related