Say I have these documents in my collection:
{ foo: [ {bar: 1, baz: 2}, {bar: 3, baz: 4} ] }
{ foo: [ {bar: 5, baz: 6}, {bar: 7, baz: 8} ] }
Each document contains an array foo of sub-documents. Now, this query allows me to find all documents where foo is a superset of [{bar: 1, baz: 2}, {bar: 3, baz: 4}], i.e. where all of the given (sub) documents are in the foo array:
db.examples.find({
$and: [
{ foo: { $elemMatch: { bar: 1, baz: 2 } } },
{ foo: { $elemMatch: { bar: 3, baz: 4 } } }
]
})
This returns the first document as expected.
However, how would I go about finding the documents were foo is a subset of the given search term? Meaning, my search term is:
[{bar: 1, baz: 2}, {bar: 3, baz: 4}, {bar: 5, baz: 6}]
And I want it to match the first document in the collection, but not the second (all sub-documents in foo must be in the given search term).
You will need to make your search term a two dimensional array that looks like this.
let searchTerms = [ [1, 2], [3, 4], [5, 6] ];
This is because as you know, an object is an unordered set of name/value pairs.
From there you need to use the $redact operator to perform a logical $condition processing.
Your condition here is as $setIsSubset which returns true when the element in the "input" array is a subset of your searchTerms array.
When $setIsSubset returns true, you $$KEEP the document and discard it when it returns false using the $$PRUNE variable.
Of course the first expression in the $setIsSubset needs to resolve to a two dimensional array like your "searchTerms" array. And to do that, you need to use the $map array operator which allows you to apply an expression to each subdocuments in the "foo" array. The expression here uses the [] operator new in version 3.2 to return a 2d array where the first element in each sub-array is the the value of "bar" and the last element the value of "bar".
db.collection.aggregate([
{ "$redact": {
"$cond": [
{ "$setIsSubset": [
{ "$map": {
"input": "$foo",
"as": "f",
"in": [ "$$f.bar", "$$f.baz" ]}
},
searchTerms
]},
"$$KEEP",
"$$PRUNE"
]
}}
])
Finally, note that $redact does not use indexes.
$elemMatch with $nor selects all the documents with foo sub documents not matching search term and $not to select the documents where foo sub documents is subset of search term.
You can use $nin instead of $nor too.
You may want to include $exist condition if you have sub documents that don't contain the search fields.
db.examples.find({
foo: {
$not: {
$elemMatch: {
$nor: [{bar: 1,baz: 2},{bar: 3,baz: 4}, {bar: 5,baz: 6}]
}
}
}
});
You can use setIsSubset in the aggregation pipeline.
db.example.aggregate([
{
$project : {
foo : 1,
criteria : {
$literal : [{bar: 1, baz: 2}, {bar: 3, baz: 4}, {bar: 5, baz: 6}]
}
}
},
{
$project : {
foo : 1,
subset : {
$setIsSubset : ["$foo", "$criteria"]
}
}
},
{
$match : {
subset : true
}
},
{
$project : {
foo : 1
}
}
])
Output:
{ "_id" : ..., "foo" : [ { "bar" : 1, "baz" : 2 }, { "bar" : 3, "baz" : 4 } ] }
Note: You can use a $match phase before first $project that may use an index.