Playground link: MONGO PLAYGROUND
I would like to end up with the following query result.
One card that matches the beginning match where filters, which starts as an array of strings and ends up with new objects from the filters collection.
The filters collection has a primary filter with enabled, and archived fields and a list of sub filters. The CARD filters [string] list will only ever have sub filter string values to start, but those values could come from several primary filter lists (sub filter value).
"cards": [
{
"cardId": "one-two-three",
"filters": [
"one",
"two",
"five"
]
}
],
"filters": [
{
"label": "filter-one-primary",
"enabled": true,
"archived": false,
"list": [
{
"label": "One",
"value": "one",
"disabled": false
},
{
"label": "Four",
"value": "four",
"disabled": false
}
]
},
{
"label": "filter-two-primary",
"enabled": true,
"archived": false,
"list": [
{
"label": "Two",
"value": "two",
"disabled": false
},
{
"label": "Five",
"value": "five",
"disabled": false
}
]
}
]
For example in the above data set, a card may have a beginning filters array of ['one', 'two', 'five'].
I would expect in the beginning part of the pipeline to first select all needed filters based on the primary filter object "enabled": true, and "archived": false and also if the list contains any of the sub filters. So in the above data set, it would select both.
After we get the filters we need to transform them into the following in side of the first returned CARD.
So using the above example, the CARD returned would have this.
[
{
"_id": ObjectId("5a934e000102030405000000"),
"cardId": "one-two-three",
"filters": [
{
primary: "filter-one-primary", // Used to be from filter object.label
secondary: "One", // Used to be from filter object.[list].value
id: "one", // Used to be from filter object.[list].value
disabled: false // Used to be from filter object.[list].disabled
},
primary: "filter-two-primary", // Used to be from filter object.label
secondary: "Two", // Used to be from filter object.[list].value
id: "two", // Used to be from filter object.[list].value
disabled: false // Used to be from filter object.[list].disabled
},
{
primary: "filter-two-primary", // Used to be from filter object.label
secondary: "Five", // Used to be from filter object.[list].value
id: "five", // Used to be from filter object.[list].value
disabled: false // Used to be from filter object.[list].disabled
}
]
}
]
In the $lookup pipeline, we would match:
// DATA
db={
"cards": [
{
"cardId": "one-two-three",
"filters": [
"four",
"five",
"six"
]
}
],
"filters": [
{
"label": "filter-one-primary",
"enabled": true,
"archived": false,
"list": [
{
"label": "One",
"value": "one",
"disabled": false
},
{
"label": "Four",
"value": "four",
"disabled": false
}
]
},
{
"label": "filter-two-primary",
"enabled": true,
"archived": false,
"list": [
{
"label": "Two",
"value": "two",
"disabled": false
},
{
"label": "Five",
"value": "five",
"disabled": false
}
]
},
{
"label": "filter-three-primary",
"enabled": true,
"archived": false,
"list": [
{
"label": "Three",
"value": "three",
"disabled": false
},
{
"label": "SiX",
"value": "six",
"disabled": false
}
]
}
]
}
db.cards.aggregate([
{
$match: {
"cardId": "one-two-three"
}
},
{
$lookup: {
from: "filters",
"let": {
"id": "filters"
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$in: [
"$$id",
"$list.value"
]
},
"enabled": true,
"archived": false
}
]
}
}
],
as: "filters"
}
}
])
In your $lookup pipeline:
$match - Your $$id is an array field from the filters array value. So you should not use the $in operator, but work with the $setIntersection, $size operators to check if there is any intersected/matched element between arrays.
$unwind - Deconstruct the list array into multiple documents.
$match - Filter the document by matching the list.value.
$project - Decorate the document to be outputed in the filters array.
db.cards.aggregate([
{
$match: {
"cardId": "one-two-three"
}
},
{
$lookup: {
from: "filters",
"let": {
"id": "$filters"
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$gt: [
{
$size: {
$setIntersection: [
"$$id",
"$list.value"
]
}
},
0
]
},
"enabled": true,
"archived": false
}
]
}
},
{
$unwind: "$list"
},
{
$match: {
$expr: {
$in: [
"$list.value",
"$$id"
]
}
}
},
{
$project: {
_id: 0,
primary: "$label",
secondary: "$list.label",
id: "$list.value",
disabled: "$list.disabled"
}
}
],
as: "filters"
}
}
])