MongoDB match documents with elements inside an array of nested arrays

Question

I have a collection like the following, and the target is to delete each document which matches to it's items.fruits.color AND items.fruits.type The problem is, I need to find the fruit which has both conditions together.

Also, if the items key is empty, i need to select that document

This is the state of my aggregation:

{
    type: "x"
    color: "red"
    items => [
        {
          item_id: 6   
          fruits: [
                {
                    color: "red",
                    type: "x"
                },
                {
                    color: "red",
                    type: "y"
                }
            ]
        },
        {
            item_id: 7   
            fruits: [
                {
                    color: "orange",
                    ref_id: "h"
                },
                {
                    color: "green",
                    ref_id: "x"
                }
            ]
        }
     ]
},{
    
    type: "o"
    color: "red"
    items => []

},{
    
    type: "w"
    color: "red"
    items => [
        {
            fruits: [
                {
                    type: "w",
                    color: "black"
                }
            ]
        }
    ]
}

So the first document is invalid, because attributes of items[fruits][0] is exactly match.

But the second one is valid, because the items array is empty.

The third one is also valid because there is no matching fruit's inside it's items.

The last query i tried was:

$match: {
   "items.fruits": {
      $not: {
          $elemMatch: {
              color: "$color",
              type: "$type"
          }
      }
  }
}

But it's not working. instead it's showing all documents. (I think it's because the $color statement is not recognized as a variable)

Answer 1

If you want to match on any document that does NOT have the fruit with a given color and type, this should work:

[{$match: {
   "items.fruits": {
      $not: {
          $elemMatch: {
              color: "red",
              type: "x"
          }
      }
  }
}}]

Answer 2

I've headed in another direction. I think this will get you started and you can add other $project or $group stages to get the final results in the format you need.

[
{$unwind: {
  path: "$items"
}}, 
{$unwind: {
  path: "$items.fruits"
}}, 
{$addFields: {
  "items.fruits.isAMatch": 
    { $and: [
      {$eq: ["$items.fruits.type", "$type"] },
      {$eq: ["$items.fruits.color", "$color"] }
      ]
    }
}}, 
{$group: {
  _id: 
    {"id": "$_id",
    "itemid": "$items.item_id"
  },
  type: {$first: "$type"},
  color: {$first: "$color"},
  fruits: { $push: "$items.fruits" }
}}, {}]

The first stage unwinds the items array. The second stage unwinds the fruits array. The third stage checks to see if the fruit and color match and creates a new field named isAMatch to indicate whether they do. The final stage groups the documents back together based on the _id and the items.item_id. The documents resemble the originals, but they have added fields in the fruits array objects indicating whether they were a match.