i'm trying to display a mongodb aggregation result via react chartjs. part of my problem is i'm not achieving the correct aggregation output in the backend.
current aggregation output which contains unwanted empty curly braces
[{
"_id":"Fubar",
"A_set":[{"A":"Y"},{"A":"N"},{},{}],
"A_count_set":[{"A_count":1},{"A_count":1},{},{}],
"B_set":[{},{},{"B":"N"},{"B":"Y"}],
"B_count_set":[{},{},{"B_count":1},{"B_count":1}]},
{
"_id":"Fubar2",
"A_set":[{"A":"Y"},{"A":"N"},{},{}],
"A_count_set":[{"A_count":1},{"A_count":1},{},{}],
"B_set":[{},{},{"B":"N"},{"B":"Y"}],
"B_count_set":[{},{},{"B_count":1},{"B_count":1}]
}]
i'm trying to achieve this target aggregation output which lacks the empty curly braces
[{
"_id":"Fubar",
"A_set":[{"A":"Y"},{"A":"N"}],
"A_count_set":[{"A_count":1},{"A_count":1}],
"B_set":[{"B":"N"},{"B":"Y"}],
"B_count_set":[{"B_count":1},{"B_count":1}]},
{
"_id":"Fubar2",
"A_set":[{"A":"Y"},{"A":"N"}],
"A_count_set":[{"A_count":1},{"A_count":1}],
"B_set":[{"B":"N"},{"B":"Y"}],
"B_count_set":[{"B_count":1},{"B_count":1}]
}]
pipeline manipulation
{$facet: {
A_branch: [
{$group: {
_id: {
Q_id: "$A_id",
A_id: "$A_id"
},
A_count: {$sum: 1}
}}
],
B_branch: [
{$group: {
_id: {
Q_id: "$Q_id",
B_id: "$B_id"
},
B_count: {$sum: 1}
}}
]
}},
{$project: {
combined_group: {$setUnion: ['$A_branch','$B_branch']}
}},
{$unwind: '$combined_group'},
{$lookup:
{
from: "Q",
localField: "combined_group._id.Q_id",
foreignField: "_id",
as: "QRef"
}
},
{$unwind: "$QRef" },
{$lookup:
{
from: "A",
localField: "combined_group._id.A_id",
foreignField: "_id",
as: "ARef"
}
},
{$unwind: {path:"$ARef", preserveNullAndEmptyArrays: true} },
{$lookup:
{
from: "B",
localField: "combined_group._id.B_id",
foreignField: "_id",
as: "BRef"
}
},
{$unwind: {path:"$BRef", preserveNullAndEmptyArrays: true} },
{$group: {
_id: "$QRef.text",
A_set: {
$push: {
A: "$ARef.value"
}
},
A_count_set: {
$push: {
A_count: "$combined_group.A_count"
}
},
B_set: {
$push: {
B: "$BRef.value"
}
},
B_count_set: {
$push: {
B_count: "$combined_group.B_count"
}
}
}}
aggregation input
{
"_id" : ObjectId("618..."),
"Q_id" : ObjectId("618..."),
"B_id" : ObjectId("618..."),
"A_id" : ObjectId("618...")
}
{
"_id" : ObjectId("618..."),
"Q_id" : ObjectId("618..."),
"B_id" : ObjectId("618..."),
"A_id" : ObjectId("618...")
}
{
"_id" : ObjectId("618..."),
"Q_id" : ObjectId("618..."),
"B_id" : ObjectId("618..."),
"A_id" : ObjectId("618...")
}
{
"_id" : ObjectId("618..."),
"Q_id" : ObjectId("618..."),
"B_id" : ObjectId("618..."),
"A_id" : ObjectId("618...")
}
CodePudding user response:
use $filter
in the end of pipeline
db.collection.aggregate([
{
"$set": {
"A_set": {
"$filter": {
"input": "$A_set",
"as": "x",
"cond": { "$ne": [ "$$x", {}] }
}
}
}
},
{
"$set": {
"A_count_set": {
"$filter": {
"input": "$A_count_set",
"as": "x",
"cond": { "$ne": [ "$$x", {}] }
}
}
}
},
{
"$set": {
"B_set": {
"$filter": {
"input": "$B_set",
"as": "x",
"cond": { "$ne": [ "$$x", {}] }
}
}
}
},
{
"$set": {
"B_count_set": {
"$filter": {
"input": "$B_count_set",
"as": "x",
"cond": { "$ne": [ "$$x", {}] }
}
}
}
}
])