mongodbmongoosemongodb-queryfindnor

Mongoose exclude documents that contain specific nested object in array


I have collection of objects similar to this (simplified for the purpose of this question):

_id: '6509e5f504613ddc1d92c62f',
matchingHistory: [
  {
    color: 'red',
    shape: 'square',
    size: 'large',
    _id: '1509e5f504613ddc1d92c62g',
    updatedAt: '2023-09-21T16:16:00.000Z'
  },
  {
    color: 'red',
    shape: 'square',
    size: 'small',
    _id: '2509e5f504613ddc1d92c62h',
    updatedAt: '2023-09-21T16:10:00.000Z'
  },
]

I want to EXCLUDE all the documents from the find query that don't have in matchingHistory an object that is red, square and large but ALL and only ALL these conditions must match. Having said that, additionally, I'd like to ignore specific fields from search, such as _id and updatedAt. matchingHistory is initially not set and when reset later it becomes empty array, so those documents should be included in results. TLDR; I only want to EXCLUDE documents that contain an exact object (red + square + large) in the array of objects.

I have tried multiple combinations of $not, $nor, but they don't produce the expected results.

My last attempt was the below but the problem is, it actually also excludes "red + square + small" (as it seems to care about "at least" one property match:

$or: [
  {
    matchingHistory: {
      $exists: true,
      $eq: [],
    },
  },
  {
    matchingHistory: {
      $exists: false,
    },
  },
  {
    $and: [
      {
        matchingHistory.color: {
          $ne: 'red',
        },
      },
      {
        matchingHistory.shape: {
          $ne: 'square',
        },
      },
      {
        matchingHistory.size: {
          $ne: 'large',
        },
      },
    ],
  }
]

Solution

  • This is a fairly straightforward find. Just imagine you want to find the documents with a matchingHistory that has within it's array an object matching { color: 'red', shape: 'square', size: 'large'} and then negate it like so:

    Mongosh Shell

    db.collection.find({
      $or: [
        {
          matchingHistory: {
            $exists: true,
            $eq: []
          }
        },
        {
          matchingHistory: {
            $exists: false
          }
        },
        {
          matchingHistory: {
            $not: {              //< All important not
              $elemMatch: {      //< Exact element match
                color: "red",
                shape: "square",
                size: "large"
              }
            }
          }
        }
      ]
    }, {'matchingHistory._id': 0, 'matchingHistory.updatedAt': 0 });
    

    Node Driver:

    db.collection.find({
      $or: [
        {
          matchingHistory: {
            $exists: true,
            $eq: []
          }
        },
        {
          matchingHistory: {
            $exists: false
          }
        },
        {
          matchingHistory: {
            $not: {              //< All important not
              $elemMatch: {      //< Exact element match
                color: "red",
                shape: "square",
                size: "large"
              }
            }
          }
        }
      ]
    }).project({
       'matchingHistory._id': 0, 
       'matchingHistory.updatedAt': 0 
    });