I have this mongoose schema.

const storeSchema = mongoose.Schema({
    name: {
        type: String,
        required: true,
    },
    fruits: {
        type: [String],
        required: true,
    },
});

And I need to get all the objects that have 2 or more bananas in the fruits array, like this.

{
    "name": "Beautiful Store",
    "fruits": ["banana", "apple", "banana", "pear"]
}

I was trying something like this, but with no success...

const query = await StoreModel.find({ fruits: { $gd: 2 } })

CodePudding user response：

Somethign like this:

Option 1: ( less efective but intuitive )

  db.collection.aggregate([
  {
   $addFields: {
    numBananas: {
    $size: {
      $filter: {
        input: "$fruits",
        as: "item",
        cond: {
          $eq: [
            "$$item",
            "banana"
          ]
         }
       }
      }
     }
    }
   },
  {
   $match: {
     numBananas: {
        $gte: 2
      }
    }
   },
  {
    $project: {
       name: 1,
       fruits: 1
   }
  }
 ])

Explained:

1. Add field "numBananas" with the number of bananas
2. Filter only the document having >=2 banas
3. Project only the necessary output

playground

Option 2: ( Best , inspired by Buzz below :) )

 db.collection.aggregate([
 {
  $match: {
   $expr: {
    $gte: [
      {
        $size: {
          $filter: {
            input: "$fruits",
            as: "item",
            cond: {
              $eq: [
                 "$$item",
                 "banana"
                ]
              }
             }
            }
         },
         2
        ]
     }
    }
  },
 {$sort:{name:1}}
])

Explained: Just match those having >=2 bananas , best if index created on fruits ( I think this solution is in 50/50 performance competition with the $reduce option from Buzz below , I would bet on this one )

playground

CodePudding user response：

Here are two variations on the theme.

The first uses $reduce to directly count the number of bananas (essentially, the {$size:{$filter...}} as above. For extra performance, we bring the calc and the $match together into a single stage:

db.foo.aggregate([
    {$match: {$expr: {$gte: [
        {$reduce: {
            input: '$fruits',
            initialValue: 0,
                in: {$cond:[{$eq: ['$$this','banana']}, {$add:['$$value',1]}, '$$value']}
        }}, 2]}
    }}
]);

The second is a general purpose fruit counter in case you want to have more complex expressions, e.g. >=2 bananas or (1 pear and 1 apple). It does come at a cost of $unwind/$group.

c=db.foo.aggregate([
    {$unwind: '$fruits'}
    ,{$group: {_id: {id: '$_id', f: '$fruits'}, N: {$sum:1}}}

    ,{$match: {'_id.f': 'banana', N:{$gte:2}} }
]);