I'm trying to make a $and clause within a $match clause. This $and clause should have a full complete comparison against the string I'm providing. I'd like to make sure that the comparisong with attributes.value should be exact the string I'm passing. With the following expression:
db.getCollection('cards').aggregate([
{
"$match": {
'attributes.key': 'setCode',
'attributes.value': 'qc'
}
},
{
"$match": {
'attributes.key': 'code',
'attributes.value': 'potions'
}
}]);
Some sample data that is returned with the query above:
[
{
"_id": {"$oid": "66ab6eec2982eff57de4423a"},
"attributes": [
{
"key": "name",
"displayText": "",
"value": "Weakness Potion",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "code",
"displayText": "",
"value": "weakness-potion",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "text",
"displayText": "",
"value": "To play this card, discard 1 of your Potions Lessons from play. Do 5 damage to your opponent. During your opponent's next turn, prevent all damage done to you by your opponent's Creatures.",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "illustrator",
"displayText": "",
"value": "Keith Garletts",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "orientation",
"displayText": "",
"value": "vertical",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "set",
"displayText": "",
"value": "Quidditch Cup",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "setCode",
"displayText": "",
"value": "qc",
"values": [],
"searchable": true,
"visible": false,
"language": ""
},
{
"key": "type",
"displayText": "",
"value": "Spell",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "typeCode",
"displayText": "",
"value": "spell",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "rarity",
"displayText": "",
"value": "Uncommon",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "rarityCode",
"displayText": "",
"value": "uncommon",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonType",
"displayText": "",
"value": "Potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonTypeCode",
"displayText": "",
"value": "potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonCost",
"displayText": "",
"value": "7",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "actionCost",
"displayText": "",
"value": "1",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "cardNumber",
"displayText": "",
"value": "50",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "subType",
"displayText": "",
"value": "",
"values": [],
"searchable": true,
"visible": true,
"language": ""
}
],
"language": "en"
},
{
"_id": {"$oid": "66ab6eec2982eff57de4423d"},
"attributes": [
{
"key": "name",
"displayText": "",
"value": "Bruisewort Balm",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "code",
"displayText": "",
"value": "bruisewort-balm",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "text",
"displayText": "",
"value": "Shuffle up to 5 non-Healing cards from your discard pile into your deck.",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "illustrator",
"displayText": "",
"value": "Alex Horley",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "orientation",
"displayText": "",
"value": "vertical",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "set",
"displayText": "",
"value": "Quidditch Cup",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "setCode",
"displayText": "",
"value": "qc",
"values": [],
"searchable": true,
"visible": false,
"language": ""
},
{
"key": "type",
"displayText": "",
"value": "Spell",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "typeCode",
"displayText": "",
"value": "spell",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "rarity",
"displayText": "",
"value": "Common",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "rarityCode",
"displayText": "",
"value": "common",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonType",
"displayText": "",
"value": "Potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonTypeCode",
"displayText": "",
"value": "potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonCost",
"displayText": "",
"value": "3",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "actionCost",
"displayText": "",
"value": "1",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "cardNumber",
"displayText": "",
"value": "53",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "subType",
"displayText": "",
"value": "",
"values": ["Healing"],
"searchable": true,
"visible": true,
"language": ""
}
],
"language": "en"
},
{
"_id": {"$oid": "66ab6eec2982eff57de44256"},
"attributes": [
{
"key": "name",
"displayText": "",
"value": "Potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "code",
"displayText": "",
"value": "potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "text",
"displayText": "",
"value": "Provides 1 Potions lesson.",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "illustrator",
"displayText": "",
"value": "Shanth Enjeti, Melissa Ferreira",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "orientation",
"displayText": "",
"value": "horizontal",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "set",
"displayText": "",
"value": "Quidditch Cup",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "setCode",
"displayText": "",
"value": "qc",
"values": [],
"searchable": true,
"visible": false,
"language": ""
},
{
"key": "type",
"displayText": "",
"value": "Lesson",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "typeCode",
"displayText": "",
"value": "lesson",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "rarity",
"displayText": "",
"value": "Common",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "rarityCode",
"displayText": "",
"value": "common",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonType",
"displayText": "",
"value": "Potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "lessonTypeCode",
"displayText": "",
"value": "potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "actionCost",
"displayText": "",
"value": "1",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "cardNumber",
"displayText": "",
"value": "78",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "providesLesson",
"displayText": "",
"value": "Potions",
"values": [],
"searchable": true,
"visible": true,
"language": ""
},
{
"key": "subType",
"displayText": "",
"value": "",
"values": [],
"searchable": true,
"visible": true,
"language": ""
}
],
"language": "en"
}
]
As you can see, I get results that match with the code like potion, potions, cauldron potion. I'm also getting results where other attributes with different keys, have the word potions within.
So my $match clause is not respecting the attributes.key with values setCode and code, because it's comparing with other attributes.
How can I make sure that the above $match condition matches only the given keys and given values?
Thank you all!
For your current example data, use $elemMatch with $all:
db.cards.aggregate([
{
$match: {
"attributes": {
$all: [
{ $elemMatch: { "key": "setCode", "value": "qc" } },
{ $elemMatch: { "key": "code", "value": "potions" } }
]
}
}
}
])
And this also works with find:
db.cards.find({
"attributes": {
$all: [
{ $elemMatch: { "key": "setCode", "value": "qc" } },
{ $elemMatch: { "key": "code", "value": "potions" } }
]
}
})