We have some documents in mongo that are essentially saved filters consisting of a name string and query JSON, along with some other properties, so for example:
{
name: "myFilter",
query: { prop1: "val1", prop2: "val2" },
groupId: 'xxxyyy'
}
What I want to be able to do is have an aggregation that applies each filter's query so we can find which ones have matches without fetching and executing each individually.
The closest I've gotten is this:
$lookup {
from: "users",
localField: "groupId",
foreignField: "groupId",
as: "users",
let: {
q: "$query",
},
pipeline: [
{ $match: { $expr: "$$q" } },
}
the problem is that this matches all users every time, I think because $$q is interpreted as a json blob which $expr treats as truthy without parsing it as if it was part of the query. I get the same result if I replace "$$q" with {foo: "bar"}, "foo" or true.
What I need is the equivalent of an exec() function that will make mongo actually treat this JSON as if it had been typed into the pipeline definition.
Recognizing that there may be security concerns around this, does anyone know if it's possible?
EDIT by request, adding example docs and expected output.
First, I neglected to include the groupId prop in the Filter example above, which I've now corrected. The groupId is just a way to separate users into large cohorts, which we then subdivide with filters.
Here's a complete example:
Filters:
[
{
name: "malesOver20",
query: { gender: "M", age: { $gte: 21 } },
groupId: "xxxyyy"
},
{
name: "allUnder21",
query: { age: { $lt: 21 } },
groupId: "xxxyyy"
}
]
Users:
[
{
name: "Joe Schmoe",
gender: "M",
age: 30,
groupId: 'xxxyyy'
},
{
name: "Kerry Berry",
gender: "F",
age: 19,
groupId: 'xxxyyy'
},
{
name: "Sanjay Manjay",
gender: "M",
age: 21,
groupId: 'xxxyyy'
}
{
name: "Jimmy Pimmy",
gender: "M",
age: 8,
groupId: 'aaabbb'
}
]
By applying a filter like this to the filters collection...
[
{
$lookup: {
from: "users",
localField: "groupId",
foreignField: "groupId",
as: "users",
let: {
q: "$query",
},
pipeline: [
{ $match: { $expr: "$$q" } },
]
}
}
]
I am trying to get a result like this:
[
{
name: "malesOver20",
query: { gender: "M", age: { $gte: 21 } },
groupId: "xxxyyy",
users:
[
{ name: "Joe Schmoe", gender: "M", age: 30, groupId: "xxxyyy" },
{ name: "Sanjay Manjay", gender: "M", age: 21, groupId: "xxxyyy" },
],
},
{
name: "allUnder21",
query: { age: { $lt: 21 } },
groupId: "xxxyyy",
users: [{ name: "Kerry Berry", gender: "F", age: 19, groupId: "xxxyyy" }],
},
]
Normally there would be further filters applied in that pipeline, but this is the part where we're stuck. I hope that helps clarify things!
