MongoDB Aggregate combine two lookups?
So my schema is split between nodes and links. The links collection has documents like
{src: A, dst:B, value:int}
and the Nodes collections has
{name:A, ...other info}
I'm trying to aggregate information for nodes to find sent vs received value data with only one aggregate funciton.
My aggregate function looks like
{
"$lookup":{
"from":"links",
"localField":"name",
"foreignField":"dst",
"as":"recv"
}
},
{
"$lookup":{
"from":"links",
"localField":"name",
"foreignField":"src",
"as":"sent"
}
},
then I just use a $project
to sum up the sent/recv arrays for value, resulting in
{name:A, sent_value:x, recv_value:y}
This works okay, but I noticed that if I took away one of the lookups, the runtime significantly decreases. I'm looking for a way to potentially combine both lookups into one stage of the pipline. I can very well just issue two aggregations for sent and recv separately, but I want to see if it's possible to only use one aggregate. Thanks
Edit: Example data
Nodes: {name: "A"}, {name: "B"},{name: "C"}
Links: {src: "A", dst: "B",value:25}, {src: "A", dst: "C", value:3}, {src: "B",dst: "C", value:19}
I want from an aggregation, with what I described above:
{name: "A", sent:28, recv: 0}, {name: "B", sent:19, recv:25}, {name: "C",sent:0,recv:22}
You can do one single $lookup
to get all dst and src data at once. Then, use $reduce
to perform conditional sum on the array.
db.nodes.aggregate([
{
"$lookup": {
"from": "links",
let: {
name: "$name"
},
pipeline: [
{
$match: {
$expr: {
$or: [
{
$eq: [
"$$name",
"$src"
]
},
{
$eq: [
"$$name",
"$dst"
]
}
]
}
}
}
],
"as": "linksLookup"
}
},
{
"$project": {
name: 1,
recv: {
"$reduce": {
"input": "$linksLookup",
"initialValue": 0,
"in": {
"$add": [
"$$value",
{
"$cond": {
"if": {
$eq: [
"$$this.dst",
"$name"
]
},
"then": "$$this.value",
"else": 0
}
}
]
}
}
},
sent: {
"$reduce": {
"input": "$linksLookup",
"initialValue": 0,
"in": {
"$add": [
"$$value",
{
"$cond": {
"if": {
$eq: [
"$$this.src",
"$name"
]
},
"then": "$$this.value",
"else": 0
}
}
]
}
}
}
}
}
])
Here is the Mongo playground for your reference.