I would like to end up with the following query result.
One card that matches the beginning match where card.filters, which starts as an array of strings, ends up with new objects from the filters collection.
The filters collection has a primary filter that have enabled, and archived field 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"
}
}
])