I have these 2 collections:
power_usages_month
{
"_id" : ObjectId("5f1029d4a25f8180630896ad"),
"device_sn" : "ST9L0CY4A2AVY7HFWWUE",
"switch_id" : ObjectId("5f0ec823580cf34423ccb13d"),
"name" : "Lampu",
"current" : 0.6299,
"time_minutes" : NumberInt(1040),
"created_at" : ISODate("2020-07-16T17:20:04.403+0000"),
"updated_at" : ISODate("2020-07-16T17:20:04.403+0000")
}
{
"_id" : ObjectId("5f1029d4a25f8180630896ae"),
"device_sn" : "ST9L0CY4A2AVY7HFWWUE",
"switch_id" : ObjectId("5f0ec8eb580cf34423ccb13f"),
"name" : "Kipas Angin",
"current" : 0.1597,
"time_minutes" : NumberInt(1040),
"created_at" : ISODate("2020-07-16T17:20:04.404+0000"),
"updated_at" : ISODate("2020-07-16T17:20:04.404+0000")
}
{
"_id" : ObjectId("5f1029daa25f8180630896b1"),
"device_sn" : "ST9L0CY4A2AVY7HFWWUE",
"switch_id" : ObjectId("5f0ec823580cf34423ccb13d"),
"name" : "Lampu",
"current" : 0.7354,
"time_minutes" : NumberInt(1040),
"created_at" : ISODate("2020-07-16T17:20:10.400+0000"),
"updated_at" : ISODate("2020-07-16T17:20:10.400+0000")
}
{
"_id" : ObjectId("5f1029daa25f8180630896b2"),
"device_sn" : "ST9L0CY4A2AVY7HFWWUE",
"switch_id" : ObjectId("5f0ec8eb580cf34423ccb13f"),
"name" : "Kipas Angin",
"current" : 0.1365,
"time_minutes" : NumberInt(1040),
"created_at" : ISODate("2020-07-16T17:20:10.401+0000"),
"updated_at" : ISODate("2020-07-16T17:20:10.402+0000")
}
users
{
"_id" : ObjectId("5f0c64e4dd0a36b93c7deafa"),
"name" : "Asd",
"email" : "asd@asd.com",
"created_at" : ISODate("2020-07-13T20:43:00.871+0000"),
"updated_at" : ISODate("2020-07-13T23:08:26.149+0000"),
"family_members" : [
//
],
"rooms" : [
{
"_id" : ObjectId("5f0c98826f0321f6986755da"),
"name" : "Ruang Makan",
"created_at" : ISODate("2020-07-14T00:23:14.839+0000"),
"updated_at" : ISODate("2020-07-14T00:23:14.840+0000"),
"devices" : [
]
},
{
"_id" : ObjectId("5f0c98876f0321f6986755dd"),
"name" : "Ruang Tamu",
"created_at" : ISODate("2020-07-14T00:23:19.693+0000"),
"updated_at" : ISODate("2020-07-14T19:00:08.281+0000"),
"devices" : [
{
"serial_number" : "ST9L0CY4A2AVY7HFWWUE",
"used_relay" : NumberInt(0),
"created_at" : ISODate("2020-07-14T16:56:22.156+0000"),
"updated_at" : ISODate("2020-07-14T16:56:22.156+0000"),
"sensors" : [
//
],
"switches" : [
{
"_id" : ObjectId("5f0ec823580cf34423ccb13d"),
"name" : "Lampu",
"relay" : NumberInt(1),
"voltage" : 220.0,
"created_at" : ISODate("2020-07-15T16:10:59.840+0000"),
"updated_at" : ISODate("2020-07-15T16:10:59.840+0000")
},
{
"_id" : ObjectId("5f0ec8eb580cf34423ccb13f"),
"name" : "Kipas Angin",
"relay" : NumberInt(2),
"voltage" : 220.0,
"created_at" : ISODate("2020-07-15T16:14:19.181+0000"),
"updated_at" : ISODate("2020-07-15T16:14:19.181+0000")
}
]
}
]
}
]
}
I want to join those two colletions using $lookup from power_usages_month referring to rooms.devices.switches._id from users collection. I have tried this, this, this, and this, but none of them results as i expected.
The desired result is to get to voltage and sum of current from switches array grouped by each switch_id like this:
{
"_id" : ObjectId("5f0ec8eb580cf34423ccb13f"),
"switch_id" : ObjectId("5f0ec8eb580cf34423ccb13f"),
"device_sn" : "ST9L0CY4A2AVY7HFWWUE",
"sum_current" : <sum of current in corresponded switch_id>,
"voltage" : 220.0,
"watt" : <voltage * sum_current>
}
{
"_id" : ObjectId("5f0ec823580cf34423ccb13d"),
"switch_id" : ObjectId("5f0ec823580cf34423ccb13d"),
"device_sn" : "ST9L0CY4A2AVY7HFWWUE",
"sum_current" : <sum of current in corresponded switch_id>,
"voltage" : 220.0,
"watt" : <voltage * sum_current>
}
Query that i have tried:
db.getCollection("power_usages_month").aggregate([
{
"$lookup": {
// "localField": "switch_id",
"from": "users",
// "foreignField": "rooms.devices.switches._id",
"let": { "id": "$rooms.devices.switches._id" },
"pipeline": [
{ "$unwind": "$rooms" },
{ "$unwind": "$rooms.devices" },
{ "$unwind": "$rooms.devices.switches" },
{ "$match": { "$expr": { "$eq": ["$switch._id", "$$id"] }}},
{
"$project": { "rooms.devices.switches": 1 }
},
{ "$group": { "_id": "$rooms.devices.switches._id" }}
],
"as": "switch"
}
},
{
"$unwind": "$switch"
},
{
"$group": {
"_id": "$switch._id",
"switch_id": { "$first": "$switch._id" },
"device_sn": { "$first": "$switch.device_sn" },
"current": {"$first": "$current"},
"sum_current": {
"$sum": "$current"
},
"voltage": { "$first": "$switch.voltage" },
}
},
{
"$project": {
"_id": 1,
"switch_id": 1,
"device_sn": 1,
"voltage": 1,
"current": 1,
"sum_current": 1,
"watt": { "$multiply": [ "$voltage", "$sum_current" ] }
}
}
])
I would appreciate your help..
