使用两个子对象数组MongoDB进行聚合
使用两个子对象数组MongoDB进行聚合
提问于 2019-09-26 07:55:18
在不同的集合对象中获得两个数组。我希望从一个对象中的两个数组中获取所有字段，并指定常见的uuid。
这是来自两个不同对象的两个数组：
{
"_id" : ObjectId("5a1ea4a4a1a13eaecf571267"),
"storage" : "events",
"list" : [
{
"uuid" : "5a03c1e0e31a11e7b8c2e398bcd9f882",
"type" : "TYPE",
"desc" : "DESC",
"when" : 1513934100000,
"loc" : "LOC",
"schedule" : [ ]
},
{
"uuid" : "1b45a340e70911e7b8c2e398bcd9f882",
"type" : "TYPE1",
"desc" : "DESC1",
"when" : 1514624400000,
"loc" : "LOC1",
"schedule" : []
}
}
}
{
"_id" : ObjectId("5a297001840b2aba87a5b1eb"),
"storage" : "control",
"list" : [
{
"uuid" : "5a03c1e0e31a11e7b8c2e398bcd9f882",
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
],
"when" : 1513934100000
},
{
"uuid" : "1b45a340e70911e7b8c2e398bcd9f882",
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
],
"when" : 1514624400000
},
}
}期望的结果是：(因为给定uuid为5a03c1e0e31a11e7b8c2e398bcd9f882)
{
"list" : [
{
"uuid" : "5a03c1e0e31a11e7b8c2e398bcd9f882",
"type" : "TYPE",
"desc" : "DESC",
"when" : 1513934100000,
"loc" : "LOC",
"schedule" : [ ],
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
]
}
}问题是，如果没有两个uuid匹配，则结果必须为null。我有多个$list，因此展开时必须有两个具有相同uuid的对象，如果不是结果，则必须为null。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-09-26 16:01:45
`db.a9e34aa77f3474fbab5e9827942fbdd.aggregate([
{
$project: {
'list': {
$filter: {
input: '$list',
as: 'item',
cond: {
$eq: ['$$item.uuid', '15d055a0d5d711e8bd45bff08b1fb980']
}
}
}
}
},
{
$unwind:"$list"
},
{
$group:{
"_id":"$list.uuid",
"list":{
$push:"$list"
}
}
},
{
$project:{
"merged":{
$reduce:{
"input":"$list",
"initialValue":{},
"in":{
$mergeObjects:["$$this","$$value"]
}
}
}
}
},
{
$group:{
"_id":null,
"list":{
$push: "$merged"
}
}
},
{
$project:{
"_id":0,
"list":1
}
}
]).pretty()`Stack Overflow用户
发布于 2019-09-26 12:04:38
以下查询可以获得预期的输出：
db.collection.aggregate([
{
$unwind:"$list"
},
{
$group:{
"_id":"$list.uuid",
"list":{
$push:"$list"
}
}
},
{
$project:{
"merged":{
$reduce:{
"input":"$list",
"initialValue":{},
"in":{
$mergeObjects:["$$this","$$value"]
}
}
}
}
},
{
$group:{
"_id":null,
"list":{
$push: "$merged"
}
}
},
{
$project:{
"_id":0,
"storage" : "result",
"list":1
}
}
]).pretty()数据集：
{
"_id" : ObjectId("5a1ea4a4a1a13eaecf571267"),
"storage" : "events",
"list" : [
{
"uuid" : "5a03c1e0e31a11e7b8c2e398bcd9f882",
"type" : "TYPE",
"desc" : "DESC",
"when" : 1513934100000,
"loc" : "LOC",
"schedule" : [ ]
},
{
"uuid" : "1b45a340e70911e7b8c2e398bcd9f882",
"type" : "TYPE1",
"desc" : "DESC1",
"when" : 1514624400000,
"loc" : "LOC1",
"schedule" : [ ]
}
]
}
{
"_id" : ObjectId("5a297001840b2aba87a5b1eb"),
"storage" : "control",
"list" : [
{
"uuid" : "5a03c1e0e31a11e7b8c2e398bcd9f882",
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
],
"when" : 1513934100000
},
{
"uuid" : "1b45a340e70911e7b8c2e398bcd9f882",
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
],
"when" : 1514624400000
}
]
}输出：
{
"list" : [
{
"uuid" : "1b45a340e70911e7b8c2e398bcd9f882",
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
],
"when" : 1514624400000,
"type" : "TYPE1",
"desc" : "DESC1",
"loc" : "LOC1",
"schedule" : [ ]
},
{
"uuid" : "5a03c1e0e31a11e7b8c2e398bcd9f882",
"missing" : [
{
"user" : "user",
"instrument" : "inst"
},
{
"user" : "user",
"instrument" : "inst"
}
],
"when" : 1513934100000,
"type" : "TYPE",
"desc" : "DESC",
"loc" : "LOC",
"schedule" : [ ]
}
],
"storage" : "result"
}查询分析：我们首先展开list数组，然后根据list.uuid进行分组。每个组都持有具有相同UUID的所有list元素的集合。稍后，将列表元素合并为一个元素。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58112067
复制相关文章
相似问题
腾讯云开发者
