Hi every one!
I have aggregation like below, but it’s tank over 10s to return result, hope someone help optimize pipeline, thank so much!
[
{
$match: {
$or: [
{ shop: "bike-com-vn.myshopify.com" },
{ shop: "57082511458" },
],
country: { $exists: true },
dateCreated: {
$gte: new Date("2024-07-05T17:00:00.000Z"),
$lte: new Date("2024-07-12T16:59:59.999Z"),
},
},
},
{
$facet: {
topPages: [
{
$addFields: {
name: { $ifNull: ["$p", "Unknown"] },
},
},
{
$group: {
_id: "$name",
count: { $sum: 1 },
},
},
],
sumTotal: [
{
$group: {
_id: "total",
count: { $sum: 1 },
},
},
],
response: [
{ $match: { dur: { $exists: true } } },
{ $set: { dur: { $toInt: "$dur" } } },
{
$group: {
_id: "avgResponse",
avg: { $avg: "$dur" },
},
},
],
devices: [
{
$addFields: {
device: {
$ifNull: ["$dv", "Unknown"],
},
},
},
{
$group: {
_id: "$device",
count: { $sum: 1 },
},
},
],
browsers: [
{
$addFields: {
browser: {
$ifNull: ["$brw", "Unknown"],
},
},
},
{
$group: {
_id: "$browser",
count: { $sum: 1 },
},
},
],
responseTypes: [
{ $unwind: "$category" },
{
$addFields: {
category: {
$ifNull: ["$category", "Other"],
},
},
},
{
$set: {
category: { $toLower: "$category" },
},
},
{
$group: {
_id: "$category",
count: { $sum: 1 },
},
},
],
countByEvent: [
{
$group: {
_id: "$event",
count: { $sum: 1 },
},
},
],
countByCountry: [
{
$group: {
_id: "$country",
count: { $sum: 1 },
},
},
{
$project: {
_id: "$_id",
country: "$_id",
count: 1,
},
},
{ $sort: { count: -1 } },
{ $limit: 5 },
],
chartData: [
{
$group: {
_id: {
event: "$event",
date: {
$dateToString: {
format: "%m-%d",
date: "$dateCreated",
},
},
},
count: { $sum: 1 },
},
},
],
},
},
]
Explain
