CODEWITHSUNDEEP
mongodbmongoosemongodb-queryfindnor
lokers
lokers

Reputation: 2198

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',
        },
      },
    ],
  }
]

Upvotes: 1

Views: 126

Answers (1)

jQueeny
jQueeny

Reputation: 8291

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 
});

Upvotes: 1

Related Questions