I am creating a view in Mongo Db in my Springboot application.Below is the code of same
[{
$sort: {
event_trigger_date: -1
}
}, {
$group: {
_id: {
"profile_id": "$profile_id"
},
data: {
$first: "$$ROOT"
}
}
}, {
$unset: "_id"
}, {
$replaceRoot: {
newRoot: "$data"
}
}, {
$project: {
"profile_id": 1
}
}, {
$lookup: {
from: 'profile_event',
localField: 'profile_id',
foreignField: 'profile_id',
as: 'profile_event_data'
}
}, {
$group: {
_id: {
"profile_id": "$profile_id"
},
data: {
$first: "$$ROOT"
}
}
}, {
$replaceRoot: {
newRoot: "$data"
}
}, {
$project: {
profile_id: 1,
profile_event_data: 1,
event_type_set: {
$concatArrays: ["$profile_event_data.event_type"]
}
}
}, {
$addFields: {
_id: {
$concat: ["ACTIONS_NOT_COMPLETED_0X:", "$profile_id"]
},
event_type: "ACTIONS_NOT_COMPLETED_NX",
event_trigger_date: "$$NOW",
event_occurence: 0,
trigger_status: "SILENT"
}
}, {
$unset: "event_exists"
}, {
$lookup: {
from: 'profile_personal_info',
localField: 'profile_id',
foreignField: 'profile_id',
as: 'personal_info'
}
}, {
$project: {
profile_id: 1,
event_type: 1,
event_trigger_date: 1,
event_occurence: 1,
trigger_status: 1,
event_type_set: 1,
personal_info: {
$arrayElemAt: ["$personal_info", 0]
}
}
}, {
$addFields: {
oldest_personal_info_created_date: {
$trunc: {
$divide: [{
$subtract: ["$$NOW", '$personal_info.created_date']
}, 1000 * 60 * 60 * 24]
}
}
}
}, {
$addFields: {
created_date: {
$trunc: {
$divide: [{
$subtract: ["$$NOW", '$event_trigger_date']
}, 1000 * 60 * 60 * 24]
}
}
}
}, {
$project: {
event_type: 1,
profile_id: 1,
event_trigger_date: 1,
profile_event_data: 1,
event_type_set: 1,
event_occurence: 1,
trigger_status: 1,
category_value: {
$cond: {
if: {
$eq: ["$oldest_personal_info_created_date", null]
},
then: "$created_date",
else: "$oldest_personal_info_created_date"
}
}
}
}, {
$project: {
profile_id: 1,
event_type: 1,
event_type_set: 1,
event_trigger_date: 1,
event_occurence: 1,
trigger_status: 1,
category_value: 1,
"event_exists": {
$in: ["ACTIONS_NOT_COMPLETED_NX", "$event_type_set"]
}
}
}, {
$match: {
event_exists: {
$ne: true
}
}
}, {
$unset: ["event_exists", "event_type_set"]
}]
I want to add allowDiskUse: true condition as i get following error
Stacktrace: |/ java.lang.Exception: [profile_event_view@stage [replica set: ]] Database error! |___/ Mongo Server error (MongoQueryException): Query failed with error code 292 and error message 'PlanExecutor error during aggregation :: caused by :: Sort exceeded memory limit of 33554432 bytes, but did not opt in to external sorting.'
How can i add allowDiskUse: true in my code in order to avoid above error?
Really a huge pipeline. I think there is a lot room for optimizing.
For example if I an not mistake, these stages
you can simply write as below. It does not make your aggregation pipeline faster but it will be easier to read:
Note, every field which is not present in
$projectis removed. You may also use"$$REMOVE"instead of separate$unsetstage, e.g.Another example:
becomes
You run 12 times
Try to run it only once, reuse the result (i.e.
datafield) several times. Operation$setWindowFieldsmay provide the same with less effort.The same applies for 18(!)
$lookup. Join every collection only once and reuse the joined data.Reformatted pipeline: