Hello, I have the following pipeline, which works as I want, but it takes too many seconds, about 25 seconds, when I change it and remove the $facet and leave the skip, limit and sort and obviously the $count, each one as another stage, It goes fast, but when you add the $facet inside with the $count it returns to 25s, I want the count, so I can know how much data there is in that pipeline, but it adds the skip and limit so that it is limited to only querying in the first n documents that I find, but I emphasize that I need to know at least the number of docs that comply with the pipeline.
I have the Product collection, which contains an attribute called list_method which is an array of ids that point to a record of another collection called Shipping Method, and this last collection has an attribute called ubigeo, this is an attribute that is an array of objects which usually has around 2000 items
[
{
$match: {
tenant: "207033693952",
supplier: {
$exists: true
},
deleted: false,
archive: false,
stock: {
$gt: 0
}
}
},
{
$lookup: {
from: "207033693952__MethodSend",
let: {
supplierId: {
$arrayElemAt: ["$list_method", 0]
}
},
pipeline: [
{
$match: {
$and: [
{
$expr: {
$eq: ["$_id", "$$supplierId"]
}
},
{
active: true
}
]
}
},
{
$project: {
ubigeo: {
$filter: {
input: "$ubigeo",
as: "zona",
cond: {
$and: [
{
$eq: ["$$zona.use", true]
},
{
$eq: [
"$$zona.district",
"Barranco"
]
},
{
$eq: [
"$$zona.ubigeo",
"150104"
]
}
]
}
}
}
}
},
{
$redact: {
$cond: {
if: {
$gt: [
{
$size: {
$ifNull: ["$ubigeo", []]
}
},
0
]
},
then: "$$KEEP",
else: "$$PRUNE"
}
}
}
],
as: "delivery_area"
}
},
{
$lookup: {
from: "207033693952__LDVDetail",
localField: "currency",
foreignField: "_id",
as: "currency"
}
},
{
$unwind: "$currency"
},
{
$lookup: {
from: "207033693952__Campaign",
let: {
campaignId: "$campaign"
},
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", "$$campaignId"]
}
}
},
{
$lookup: {
from: "207033693952__CategoryGroup",
let: {
groupId: "$group"
},
pipeline: [
{
$match: {
$expr: {
$eq: ["$_id", "$$groupId"]
}
}
},
{
$project: {
friendly_url: 1,
name: 1
}
}
],
as: "group"
}
},
{
$unwind: {
path: "$group"
}
},
{
$project: {
_id: 1,
discount_name: 1,
active: 1,
group: 1,
friendly_url: 1
}
}
],
as: "campaign"
}
},
{
$unwind: {
path: "$campaign",
preserveNullAndEmptyArrays: true
}
},
{
$lookup: {
from: "207033693952__Category",
localField: "categories",
foreignField: "_id",
as: "categories"
}
},
{
$lookup: {
from: "207033693952__CategoryGroup",
localField: "group",
foreignField: "_id",
as: "group"
}
},
{
$unwind: {
path: "$group",
preserveNullAndEmptyArrays: true
}
},
{
$match: {
deleted: false,
archive: false,
active: true,
$or: [
{
campaign: {
$exists: false
}
},
{
$and: [
{
campaign: {
$exists: true
}
},
{
$or: [
{
"campaign.active": false
},
{
"campaign._id": "null"
}
]
}
]
}
]
}
},
{
$facet: {
paginatedResult: [
{
$sort: {
name: 1
}
},
{
$skip: 0
},
{
$limit: 20
},
{
$addFields: {
brand_name: "$brand_name",
supplier_name: "$supplier_name",
"currency.ref1": "$currency.ref1"
}
}
],
totalCount: [
{
$count: "totalCount"
}
]
}
}
]
After modifying the last stage, remove the $facet
{
$skip: 0
},
{
$limit: 20
},
{
$addFields: {
brand_name: "$brand_name",
supplier_name: "$supplier_name",
"currency.ref1": "$currency.ref1"
}
},
I only want those first 20 or n documents, but I also want to have the total number of documents processed but their data outside the 20 does not matter, but only the number of documents, how could I improve or change my pipeline?
PS: I’m somewhat new to aggregates in mongo, I’m just learning.