Hi @steevej ,
Thank you to point out my mistake.
I changed “group lookup” to
[
{
$match: {
year: 2024,
month: 8,
day: 1,
"attributes.country": {
$gte: "AA",
$lte: "ZZ"
}
}
},
{
$group: {
_id: {
appId: "$appId",
os: "$os",
country: "$attributes.country"
}
}
},
{
$lookup: {
from: "device",
let: {
id_appId: "$_id.appId",
id_os: "$_id.os",
id_country: "$_id.country"
},
as: "result",
pipeline: [
{
$match: {
year: 2024,
month: 8,
day: 1,
$expr: {
$and: [
{
$eq: ["$appId", "$$id_appId"]
},
{
$eq: ["$os", "$$id_os"]
},
{
$eq: [
"$attributes.country",
"$$id_country"
]
}
]
},
"attributes.lang": {
$gte: "aa",
$lte: "zz"
}
}
},
{
$group: {
_id: {
lang: "$attributes.lang"
},
value: {
$sum: 1
}
}
}
]
}
},
{
$unwind: {
path: "$result",
preserveNullAndEmptyArrays: true
}
},
{
$group: {
_id: {
appId: "$_id.appId",
os: "$_id.os",
country: "$_id.country",
lang: "$result._id.lang"
},
value: {
$sum: "$result.value"
}
}
}
]
It still takes around 2 minutes.