$project$lookup值未显示在$group之后

我有两个相关的集合，我想做一个$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")
}

power_usage_month

{ 
"_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")
}

现在，我想使用$lookup和$project(我想显示的字段("连接"这些集合，但显然，在我执行$group之后，$project值不会显示。这是我的查询

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
}
);

根据结果，"device_id" : "$switches.device_id",和"voltage" : "$switches.voltage"无法按预期出现：

{ 
"_id" : ObjectId("5e8454bc95c85ca0c33a9463"), 
"switch_id" : ObjectId("5e8454bc95c85ca0c33a9463"), 
"sum_current" : 70.0
}
{ 
"_id" : ObjectId("5e8453c095c85ca0c33a9461"), 
"switch_id" : ObjectId("5e8453c095c85ca0c33a9461"), 
"sum_current" : 77.38
}

怎么可能呢？请告诉我查询的错误。。

编辑：这是我想要的结果

{ 
"_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
}

您可以将项目作为分组和$lookup之后的最后一步你也可以在小组中处理所有这些，您将在$group中包含的内容将仅在查询中对您可用，但假设您需要收集交换机中一个阵列中每个交换机使用的CCD_ 9文档，但你不需要power_usages_month的所有信息，你只需要current和time_minutes，例如

你可以做一些类似的事情

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,
}
}
]
)

这将返回一组开关及其总电流和开关信息(如果你不需要所有开关信息，你也可以在这里进行一些投影(，以及与该开关相关的用电文档(投影后(

结果将类似于

{
"_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
}
]
}

更新
如果您只是需要有关交换机的信息，你还需要得到瓦特，它=总电流*每个开关的电压，我们可以在$project中做到这一点，我们可以将$multiply运算符添加到$project管道中，它将开关的电压乘以我们刚刚在$group管道中计算的总电流

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" ] }
}
}
]
)

这将产生一个形式的数组

{
"_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
}

相关内容

最新更新

热门标签：