I have 2 related collection which i want to do a $lookup.
{
"_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"relay" : NumberInt(1),
"name" : "Lampu Tengah",
"voltage" : 80.0,
"duration" : null,
"status" : true,
"triggered_by" : ObjectId("5e5fd642fce106005319e884"),
"created_at" : ISODate("2020-04-01T15:41:36.588+0000"),
"updated_at" : ISODate("2020-04-01T22:59:39.261+0000")
}
{
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"relay" : NumberInt(2),
"name" : "Kipas Angin",
"voltage" : 100.0,
"duration" : null,
"status" : true,
"triggered_by" : ObjectId("5e5fd642fce106005319e884"),
"created_at" : ISODate("2020-04-01T15:45:48.099+0000"),
"updated_at" : ISODate("2020-04-01T15:45:48.099+0000")
}
{
"_id" : ObjectId("5e87edffffba850e8d72ce27"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 19.345,
"time_minutes" : NumberInt(123),
"created_at" : ISODate("2020-04-02T15:01:37.521+0000"),
"updated_at" : ISODate("2020-04-02T15:01:37.521+0000")
}
{
"_id" : ObjectId("5e87ee06ffba850e8d72ce28"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 17.5,
"time_minutes" : NumberInt(123),
"created_at" : ISODate("2020-04-03T20:35:09.870+0000"),
"updated_at" : ISODate("2020-04-03T20:35:09.871+0000")
}
{
"_id" : ObjectId("5e87ee0cffba850e8d72ce29"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 19.345,
"time_minutes" : NumberInt(124),
"created_at" : ISODate("2020-04-04T01:45:00.000+0000"),
"updated_at" : ISODate("2020-04-04T01:45:00.000+0000")
}
{
"_id" : ObjectId("5e87ee13ffba850e8d72ce2a"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 17.5,
"time_minutes" : NumberInt(124),
"created_at" : ISODate("2020-04-04T01:45:00.000+0000"),
"updated_at" : ISODate("2020-04-04T01:45:00.000+0000")
}
{
"_id" : ObjectId("5e87ee18ffba850e8d72ce2b"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 19.345,
"time_minutes" : NumberInt(125),
"created_at" : ISODate("2020-04-04T01:45:00.000+0000"),
"updated_at" : ISODate("2020-04-04T01:45:00.000+0000")
}
{
"_id" : ObjectId("5e87ee20ffba850e8d72ce2c"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 17.5,
"time_minutes" : NumberInt(125),
"created_at" : ISODate("2020-04-04T01:45:00.000+0000"),
"updated_at" : ISODate("2020-04-04T01:45:00.000+0000")
}
{
"_id" : ObjectId("5e87ee26ffba850e8d72ce2d"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 19.345,
"time_minutes" : NumberInt(126),
"created_at" : ISODate("2020-04-04T01:45:00.000+0000"),
"updated_at" : ISODate("2020-04-04T01:45:00.000+0000")
}
{
"_id" : ObjectId("5e87ee2dffba850e8d72ce2e"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"current" : 17.5,
"time_minutes" : NumberInt(126),
"created_at" : ISODate("2020-04-04T01:45:00.000+0000"),
"updated_at" : ISODate("2020-04-04T01:45:00.000+0000")
}
Now i want to "join" these collections using $lookup and $project the field i want to show, but apparently the $project value won't show after i do a $group. Here's my query
db.getCollection("power_usages_month").aggregate(
[
{
"$project" : {
"_id" : NumberInt(0),
"power_usages_month" : "$$ROOT"
}
},
{
"$lookup" : {
"localField" : "power_usages_month.switch_id",
"from" : "switches",
"foreignField" : "_id",
"as" : "switches"
}
},
{
"$unwind" : {
"path" : "$switches",
"preserveNullAndEmptyArrays" : false
}
},
{
"$group" : {
"_id" : "$power_usages_month.switch_id",
"sum_current" : {
"$sum" : "$power_usages_month.current"
}
}
},
{
"$project" : {
"switch_id" : "$_id",
"device_id" : "$switches.device_id",
"sum_current" : "$sum_current",
"voltage" : "$switches.voltage",
}
}
],
{
"allowDiskUse" : true
}
);
The "device_id" : "$switches.device_id", and "voltage" : "$switches.voltage" cannot appear as expected according to the result:
{
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"sum_current" : 70.0
}
{
"_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"sum_current" : 77.38
}
How could it be? Please tell me the fault of the query..
Edit: Here's my desired result
{
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"sum_current" : 70.0,
"voltage" : 80.0
}
{
"_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"sum_current" : 77.38,
"voltage" : 100.0
}
you can do the project as the last step after grouping and $lookup
also you can handle all of that in the group,
what you will include in the $group will be only available with you in the query,
but lets say you need to gather the power_usages_month docs used by each switch in one array in that switch,
but you don't need all the info from that power_usages_month, you just need the current and the time_minutes for example
you can do something like that
db.powerUsageMonth.aggregate(
[
{
$match: {}
},
{
$lookup: {
from: 'switches',
localField: 'switch_id',
foreignField: '_id',
as: 'switch'
}
},
{
$unwind: '$switch'
},
{
$group: {
_id: '$switch._id',
sum_current: {
$sum: '$current'
},
switchInfo: {
$first: '$switch'
},
powerUsageDocs: {
$addToSet: '$$ROOT'
}
}
},
{
$project: {
_id: 1,
sum_current: 1,
switchInfo: 1,
'powerUsageDocs.current': 1,
'powerUsageDocs.time_minutes': 1,
}
}
]
)
this will return an array of switches with their total current and the switch info (you can do some projection here too if you don't need all the switch info), and the power usages docs related to this switch (after projection)
the result will be something like that
{
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"sum_current" : 70,
"switchInfo" : {
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"relay" : 2,
"name" : "Kipas Angin",
"voltage" : 100,
"duration" : null,
"status" : true,
"triggered_by" : ObjectId("5e5fd642fce106005319e884"),
"created_at" : ISODate("2020-04-01T15:45:48.099Z"),
"updated_at" : ISODate("2020-04-01T15:45:48.099Z")
},
"powerUsageDocs" : [
{
"current" : 17.5,
"time_minutes" : 125
},
{
"current" : 17.5,
"time_minutes" : 123
},
{
"current" : 17.5,
"time_minutes" : 126
},
{
"current" : 17.5,
"time_minutes" : 124
}
]
}
{
"_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"sum_current" : 77.38,
"switchInfo" : {
"_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"relay" : 1,
"name" : "Lampu Tengah",
"voltage" : 80,
"duration" : null,
"status" : true,
"triggered_by" : ObjectId("5e5fd642fce106005319e884"),
"created_at" : ISODate("2020-04-01T15:41:36.588Z"),
"updated_at" : ISODate("2020-04-01T22:59:39.261Z")
},
"powerUsageDocs" : [
{
"current" : 19.345,
"time_minutes" : 124
},
{
"current" : 19.345,
"time_minutes" : 126
},
{
"current" : 19.345,
"time_minutes" : 123
},
{
"current" : 19.345,
"time_minutes" : 125
}
]
}
Update
If you just need info about your switch, also you need to get the watt which is = total current * voltage of each switch, we can do that in the
$project, we can add the$multiplyoperator to the$projectpipeline, which multiplies the voltage of the switch by the total current we just calculated in the$grouppipeline
db.powerUsageMonth.aggregate(
[
{
$match: {}
},
{
$lookup: {
from: 'switches',
localField: 'switch_id',
foreignField: '_id',
as: 'switch'
}
},
{
$unwind: '$switch'
},
{
$group: {
_id: '$switch._id',
switch_id: { $first: '$switch._id' },
device_id: { $first: '$switch.device_id' },
voltage: { $first: '$switch.voltage' },
sum_current: {
$sum: '$current'
}
}
},
{
$project: {
_id: 1,
switch_id: 1,
device_id: 1,
voltage: 1,
sum_current: 1,
watt: { $multiply: [ "$voltage", "$sum_current" ] }
}
}
]
)
this will result an array of the form
{
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"voltage" : 100,
"sum_current" : 70,
"watt" : 7000
}
{
"_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"),
"device_id" : ObjectId("5e7d83efd62c242a11e3ca5e"),
"voltage" : 80,
"sum_current" : 77.38,
"watt" : 6190.4
}