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