I have a chat application in MongoDB that stores documents with the following structure:
{
"_id": "63dd49fbf24c2721fc4b3a83",
"from": "user1",
"to": "user2",
"creation": {
"$date": {
"$numberLong": "1675446779129"
}
},
"message": "Text of the message"
}
I want to have a chart in MongoDB Charts that gives me the amount of chats created between pairs of users (doesn't matter how much messages they exchanged nor which one of the two initiated the chat). The graph will be of grouped bar type, with the number of chats created on the Y axis and the day on the X axis.
So far I tried the following pipeline code, but with not much success:
[
{
$group: {
_id: {
$cond: [
{ $gt: [{ $cmp: ["$from", "$to"] }, 0] },
{ from: "$from", to: "$to" },
{ from: "$to", to: "$from" }
]
},
count: { $sum: 1 }
}
},
{
$project: {
_id: 0,
from: "$_id.from",
to: "$_id.to",
count: 1
}
}
]