Hello Jason,
Here is the facet stage we are trying to process:
/**
* outputFieldN: The first output field.
* stageN: The first aggregation stage.
*/
{
"measures": [
{
"$unwind": {
"path": "$timeSeriesCustomFieldValues.27405",
"preserveNullAndEmptyArrays": true
}
},
{
"$unwind": {
"path": "$timeSeriesCustomFieldValues.27407",
"preserveNullAndEmptyArrays": true
}
},
{
"$group": {
"_id": {
"display-by_id": {
"$filter": {
"input": "$timeSeriesCustomFieldValues",
"cond": { "$eq": [ "$$this.definitionId", "27405" ] }
}
},
"display-by_display": {
"$filter": {
"input": "$timeSeriesCustomFieldValues",
"cond": { "$eq": [ "$$this.definitionId", "27405" ] }
}
},
"display-by_sort": {
"$filter": {
"input": "$timeSeriesCustomFieldValues",
"cond": { "$eq": [ "$$this.definitionId", "27405" ] }
}
},
"group-by_id": {
"$filter": {
"input": "$timeSeriesCustomFieldValues",
"cond": { "$eq": [ "$$this.definitionId", "27407" ] }
}
},
"group-by_display": {
"$filter": {
"input": "$timeSeriesCustomFieldValues",
"cond": { "$eq": [ "$$this.definitionId", "27407" ] }
}
},
"group-by_sort": {
"$filter": {
"input": "$timeSeriesCustomFieldValues",
"cond": { "$eq": [ "$$this.definitionId", "27407" ] }
}
}
},
"result": {
"$sum": 1
}
}
},
{
"$sort": {
"_id.display-by_sort.value.value": 1
}
},
{
"$sort": {
"_id.group-by_sort.value.value": 1
}
},
{
"$group": {
"_id": null,
"display-by_labels": {
"$push": {
"$ifNull": [
"$_id.display-by_display.value", null
]
}
},
"display-by_ids": {
"$push": {
"$ifNull": [
"$_id.display-by_id.value", null
]
}
},
"group-by_labels": {
"$push": {
"$ifNull": [
"$_id.group-by_display.value", null
]
}
},
"group-by_ids": {
"$push": {
"$ifNull": [
"$_id.group-by_id.value", null
]
}
},
"result_push": {
"$push": "$result"
}
}
},
{
"$project": {
"_id": 0,
"x-group": "$display-by_labels",
"x-group-ids": "$display-by_ids",
"x-name": "$group-by_labels",
"x-name-ids": "$group-by_ids",
"data": [
{
"result": "$result_push",
"option": "count"
}
]
}
}
]
}
As for the explain plan, it says:
{
"stage": "COLLSCAN",
"nReturned": 236,
"executionTimeMillisEstimate": 0,
"works": 238,
"advanced": 236,
"needTime": 1,
"needYield": 0,
"saveState": 0,
"restoreState": 0,
"isEOF": 1,
"direction": "forward",
"docsExamined": 236
}
Here is an example document:
{ "_id": "3892", "type": "opportunities", "weightedAllocatedValue": 0, "probability": 0.01, "createdTimestamp": { "$date": { "$numberLong": "1278086242000" } }, "classificationType": "New Investment", "weightedValue": 15000, "isErisa": false, "expectedInvestmentDate": { "$date": { "$numberLong": "1291183200000" } }, "allocatedAmount": 0, "name": "Allen Investments Opp", "requestedAmount": 1500000, "currencyCode": "USD", "effectiveDate": { "$date": { "$numberLong": "1278086242000" } }, "timeSeriesCustomFieldValues": [ { "definitionId": "27405", "name": "Element", "fieldType": "select", "values": [ { "id": "92171", "effectiveDate": { "$date": { "$numberLong": "1664600400000" } }, "value": [ { "lovSet": 13124, "code": "1", "value": "Earth" } ] }, { "id": "92173", "effectiveDate": { "$date": { "$numberLong": "1667278800000" } }, "value": [ { "lovSet": 13124, "code": "2", "value": "Wind" } ] }, { "id": "92175", "effectiveDate": { "$date": { "$numberLong": "1669874400000" } }, "value": [ { "lovSet": 13124, "code": "3", "value": "Water" } ] } ] } ], "investor": { "_id": "788709", "type": "contacts", "birthday": { "$date": { "$numberLong": "-491857200000" } }, "lastName": "Allen", "isEmployee": false, "website": "http://www.allenfunds.com", "gender": "UNSPECIFIED", "otherId": "1010", "jobTitle": "CIO", "createdTimestamp": { "$date": { "$numberLong": "1272386954000" } }, "firstName": "George", "name": "Allen, George", "contactSource": { "_id": "3668", "type": "contact-sources", "name": "3P-MKT 3", "description": "3P-MKT 3" }, "contactLocations": [ { "_id": "25203", "type": "contact-locations", "country": "United States", "city": "New York", "postalCode": "10011", "locationTitle": "Business", "isPrimaryLocation": true, "state": "NY" } ], "clientDefinedEntityType": { "_id": "52", "type": "entity-types", "pluralName": "People", "name": "Person", "resourceType": "people" }, "permissionBucket": { "_id": "2451", "type": "permission-buckets", "name": "Public" } }, "stage": { "_id": "974", "type": "opportunity-stages", "sortOrder": { "$numberLong": "3" }, "name": "Committed/Processing", "closed": false }, "createdBy": { "_id": "51443", "type": "system-users", "lastName": "SuperAdmin", "firstName": "Training", "fullName": "Training SuperAdmin", "disabled": false, "userName": "SuperAdmin" }, "primaryContact": { "_id": "788709", "type": "contacts", "birthday": { "$date": { "$numberLong": "-491857200000" } }, "lastName": "Allen", "isEmployee": false, "website": "http://www.allenfunds.com", "gender": "UNSPECIFIED", "otherId": "1010", "jobTitle": "CIO", "createdTimestamp": { "$date": { "$numberLong": "1272386954000" } }, "firstName": "George", "name": "Allen, George", "contactSource": { "_id": "3668", "type": "contact-sources", "name": "3P-MKT 3", "description": "3P-MKT 3" }, "contactLocations": [ { "_id": "25203", "type": "contact-locations", "country": "United States", "city": "New York", "postalCode": "10011", "locationTitle": "Business", "isPrimaryLocation": true, "state": "NY" } ], "clientDefinedEntityType": { "_id": "52", "type": "entity-types", "pluralName": "People", "name": "Person", "resourceType": "people" }, "permissionBucket": { "_id": "2451", "type": "permission-buckets", "name": "Public" } }, "clientDefinedEntityType": { "_id": "56", "type": "entity-types", "pluralName": "Opportunities", "name": "Opportunity", "resourceType": "opportunities" }, "permissionBucket": { "_id": "2451", "type": "permission-buckets", "name": "Public" }, "investorType": { "_id": "12106", "type": "investor-types", "classificationType": "Endowment / Foundation", "investorType": "endowment" }, "_index": [ { "k": "_id", "v": "3892" }, { "k": "type", "v": "opportunities" }, { "k": "investor._id", "v": "788709" }, { "k": "investor.type", "v": "contacts" }, { "k": "stage._id", "v": "974" }, { "k": "stage.type", "v": "opportunity-stages" }, { "k": "createdBy._id", "v": "51443" }, { "k": "createdBy.type", "v": "system-users" }, { "k": "primaryContact._id", "v": "788709" }, { "k": "primaryContact.type", "v": "contacts" }, { "k": "clientDefinedEntityType._id", "v": "56" }, { "k": "clientDefinedEntityType.type", "v": "entity-types" }, { "k": "permissionBucket._id", "v": "2451" }, { "k": "permissionBucket.type", "v": "permission-buckets" }, { "k": "investorType._id", "v": "12106" }, { "k": "investorType.type", "v": "investor-types" }, { "k": "effectiveDate", "v": { "$date": { "$numberLong": "1278086242000" } } }, { "k": "probability", "v": 0.01 }, { "k": "currencyCode", "v": "USD" }, { "k": "createdTimestamp", "v": { "$date": { "$numberLong": "1278086242000" } } }, { "k": "expectedInvestmentDate", "v": { "$date": { "$numberLong": "1291183200000" } } }, { "k": "requestedAmount", "v": 1500000 }, { "k": "allocatedAmount", "v": 0 }, { "k": "name", "v": "Allen Investments Opp" }, { "k": "isErisa", "v": false } ]}
The Mongo version is 4.4.4 Community Edition.
As for the low document size, we actually set the facet document size low so we could test locally. The original problem was happening in production, on a much larger dataset, several thousand documents.