aggregation framework - Display the conditionnal size of an array with the others fields of a mongodb document

Question

I have a collection of fridges and I would like to have some fields from each fridge matching a condition plus the 'conditionnal size' of the items in this fridge.

This is an example of my DB :

db={
  "fridges": [
    {
      _id: 1,
      items: [
        {
          itemId: 1,
          name:"beer"
        },
        {
          itemId: 2,
          name: "chicken"
        }
      ],
      brand:"Bosch",
      size:195,
      cooler:true,
      color:"grey"
    },
    
    {
      _id: 2,
      items: [
        {
          itemId: 1,
          name:"beer"
        },
        {
          itemId: 2,
          name: "chicken"
        },
        {
          itemId: 3,
          name: "lettuce"
        }
      ],
      brand:"Electrolux",
      size:200,
      cooler:true,
      color:"white"
    },
  ]
}

I want to get fridges with these mutuals conditions ('and' condition):

• brand is $in ["Bosch","Samsung"]
• color is $in ["grey","white"]

In addition : The number of items with a name $in ["beer","lettuce"]

And finally : Removing some fields like the size and items of the result.

In our example, the excepted output would be :

{
  _id:1
  itemsNumber:1,
  brand:"Bosch",
  cooler:true,
  color:"grey"
}

Explanations : We removed the field items and size, itemsNumber counts the number of beers and lettuce from items array. And we only keep the first fridge its brand is Bosch and it's grey.

This what I have so far :

db.fridges.aggregate([
  {
    "$match": {
      $and: [
        {
          "brand": {
            $in: [
              "Bosch",
              "Samsung"
            ]
          }
        },
        {
          "color": {
            $in: [
              "grey",
              "white"
            ]
          }
        }
      ]
    }
  },
  {
    "$project": {
      "itemsNumber": {
        $size: "$items" // This is not good
      },
      brand: 1,
      cooler: 1,
      color: 1
    }
  }
])

Which returns me :

[
  {
    "_id": 1,
    "brand": "Bosch",
    "color": "grey",
    "cooler": true,
    "itemsNumber": 2
  }
]

Counting the items matching with either beer or lettuce is my main problem. This is an executable example.

Thanks in advance !

Answer 1

I found out how to make it work. Thank you @joe for suggesting to use filter this was indeed the solution.

Here is the complete query :

db.fridges.aggregate([
  {
    $match: {
      $and: [
        {
          "brand": {
            $in: [
              "Bosch",
              "Samsung"
            ]
          }
        },
        {
          "color": {
            $in: [
              "grey",
              "white"
            ]
          }
        }
      ]
    }
  },
  {
    $project: {
      "itemsNumber": {
        "$filter": {
          "input": "$items",
          "as": "item",
          "cond": {
            $in: [
              "$$item.name",
              [
                "beer",
                "lettuce"
              ]
            ]
          }
        }
      },
      brand: 1,
      cooler: 1,
      color: 1
    }
  }
])

Runnable example.