如何将多个 $group 合并到 $project 中的 MongoDB 聚合中?

huangapple go评论64阅读模式
英文:

How to merge multiple $group into $project in aggregate mongodb?

问题

我正在使用聚合操作查询MongoDB中的数据,但在$group部分遇到了问题,我想将多个$group阶段合并为最终的$project,但似乎不按照我的期望工作。在下面的代码中,当我执行它时,除了_id之外,我没有得到任何结果。我该如何解决这个问题?

以下是我的聚合管道:

const user = userCollection.aggregate([
    {
      $match: {
         _id: ObjectId(id)
      }
    },

    // 查找图像列表
    {
       $lookup: {...}
    },
    {
        $unwind: {
            path: '$images',
            preserveNullAndEmptyArrays: true,
        },
    },
    { $sort: { 'images.createdAt': -1 } },
    {
        $group: {
            _id: '$_id',            
            images: {
                $push: '$images',
            },
        },
    },

    // 查找阻止列表
    {
       $lookup: {...}
    },
    {
        $unwind: {
            path: '$blocked',
            preserveNullAndEmptyArrays: true,
        },
    },
    { $sort: { 'blocked.createdAt': -1 } },
    {
        $group: {
            _id: '$_id',            
            blocked: {
                $push: '$blocked',
            },
        },
    },

    // 查找粉丝列表
    {
       $lookup: {...}
    },
    {
        $unwind: {
            path: '$followers',
            preserveNullAndEmptyArrays: true,
        },
    },
    { $sort: { 'followers.createdAt': -1 } },
    {
        $group: {
            _id: '$_id',            
            followers: {
                $push: '$followers',
            },
        },
    },


    {
       $project: {
          _id: 1,
          name: 1,
          age: 1,
          bio: 1,
          images: 1,
          blocked: 1,
          followers: 1,
       }
    }

]);

console.log(user);
// 结果: [{ _id: '...' }, { _id: '...' }, { _id: '...' }, { _id: '...' }]

希望这可以帮助你解决问题。

英文:

I am using aggregate to query data in MongoDB but I have an issue with $group, I want to merge multiple $group stage to one $project at final but look like it doesn't seem to works as my expected. In the code below when I am excute it I don't get any results except _id. How can I resolve this issue?

Here is my aggregate pipelines:

const user = userCollection.aggregate([
{
$match: {
_id: ObjectId(id)
}
},
// lookup images list
{
$lookup: {...}
},
{
$unwind: {
path: '$images',
preserveNullAndEmptyArrays: true,
},
},
{ $sort: { 'images.createdAt': -1 } },
{
$group: {
_id: '$_id',            
images: {
$push: '$images',
},
},
},
// lookup blocked list
{
$lookup: {...}
},
{
$unwind: {
path: '$blocked',
preserveNullAndEmptyArrays: true,
},
},
{ $sort: { 'blocked.createdAt': -1 } },
{
$group: {
_id: '$_id',            
blocked: {
$push: '$blocked',
},
},
},
// lookup followers list
{
$lookup: {...}
},
{
$unwind: {
path: '$followers',
preserveNullAndEmptyArrays: true,
},
},
{ $sort: { 'followers.createdAt': -1 } },
{
$group: {
_id: '$_id',            
followers: {
$push: '$followers',
},
},
},
{
$project: {
_id: 1,
name: 1,
age: 1,
bio: 1,
images: 1,
blocked: 1,
followers: 1,
}
}
]);
console.log(user);
// Results: [{ _id: '...' }, { _id: '...' }, { _id: '...' }, { _id: '...' }]

答案1

得分: 2

通用的指导原则是要记住以下处理数组的序列是一种反模式:

  1. $unwind
  2. 处理展开的数组,例如使用 $match$sort 进行过滤或排序
  3. 使用 _id: '$_id' 进行 $group 将数据重新组合

每当你看到这种情况时,应该尝试寻找其他可以在不需要拆解和重构文档的情况下内联处理数组的操作符。

在你的情况下,我会尝试类似以下的操作:

[
  { $match: { ... } },
  { $lookup: { ... as: 'images' } },
  { $lookup: { ... as: 'blocked' } },
  { $lookup: { ... as: 'followers' } },
  {
    $project: {
      _id: 1,
      name: 1,
      age: 1,
      bio: 1,
      images: {
        $sortArray: { input: "$images", sortBy: { createdAt: -1 } }
      },
      blocked: {
        $sortArray: { input: "$blocked", sortBy: { createdAt: -1 } }
      },
      followers: {
        $sortArray: { input: "$followers", sortBy: { createdAt: -1 } }
      },
    }
  }
]

具体地,我们在最后的 $project 阶段使用了 $sortArray 操作符 来进行提到的数组处理。

你可以在 这个示例 playground 中找到 $sortArray 的简单演示。

或者,如果你使用的是早于该操作符可用版本,或者需要额外的逻辑,你可以考虑使用 这个 $lookup 语法

[
  ...
  { $lookup: 
    {
      from: 'images',
      localField: 'imageField',
      foreignField: 'imageField',
      pipeline: [
        { $sort: { createdAt: -1 } }
      ],
      as: 'images'
    } 
  },
  ...
]
英文:

A general guideline to keep in mind is that the the following sequence to process arrays is an anti-pattern:

  1. $unwind
  2. Processing the unwound array, eg filtering it with $match or $sorting it
  3. $grouping the data back together using `_id: '$_id'

Anytime you see that you should try to look around for other operators that can process the arrays inline without having to deconstruct and then reconstruct the documents.

In your situation, I'd look to do something similar to the following:

[
{ $match: { ... } },
{ $lookup: { ... as: 'images' } },
{ $lookup: { ... as: 'blocked' } },
{ $lookup: { ... as: 'followers' } },
{
$project: {
_id: 1,
name: 1,
age: 1,
bio: 1,
images: {
$sortArray: { input: "$images", sortBy: { createdAt: -1 } }
},
blocked: {
$sortArray: { input: "$blocked", sortBy: { createdAt: -1 } }
},
followers: {
$sortArray: { input: "$followers", sortBy: { createdAt: -1 } }
},
}
}
]

Specifically we are using the $sortArray operator in the final $project stage to do the array processing that was mentioned.

A simple demonstration of $sortArray can be found in this playground example.

Alternatively, either if on a version prior to that operator becoming available or if additional logic is required, you could consider using this $lookup syntax:

[
...
{ $lookup: 
{
from: 'images',
localField: 'imageField',
foreignField: 'imageField',
pipeline: [
{ $sort: { createdAt: -1 } }
]
as: 'images'
} 
},
...
]

huangapple
  • 本文由 发表于 2023年6月22日 07:27:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76527734.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定