mongodb - 按日期对来自多个集合的文档进行分组
问题描述
我有 3 个具有基本结构的模式
meal: {
user: 'objectID',
createdAt: 'date
}
activity: {
user: 'objectID',
createdAt: 'date'
}
role: {
user: 'objectID',
createdAt: 'date'
}
我想从属于用户的每个模式中获取所有文档,并按日期对它们进行分组。例如,一个响应
history: [
{
date: 01-11-2021,
meal: [
...array of meal documents on 01-11-2021
],
activity: [
...array of meal documents on 01-11-2021
],
role: [
...array of meal documents on 01-11-2021
],
},
...next date
]
解决方案
数据
db={
"user": [
{
"_id": 1,
"name": "Sam"
}
],
"meal": [
{
"user": 1,
"content": "apple",
"createdAt": ISODate("2021-09-01T11:23:25.184Z")
},
{
"user": 1,
"content": "orange",
"createdAt": ISODate("2021-09-01T11:23:25.184Z")
},
{
"user": 1,
"content": "pie",
"createdAt": ISODate("2021-09-02T11:23:25.184Z")
}
],
"activity": [
{
"user": 1,
"content": "baseball",
"createdAt": ISODate("2021-09-01T11:23:25.184Z")
}
],
"role": [
{
"user": 1,
"content": "admin",
"createdAt": ISODate("2021-09-01T11:23:25.184Z")
}
]
}
骨料
db.user.aggregate([
{
"$match": {
_id: 1
}
},
{
"$lookup": {
"from": "meal",
"localField": "_id",
"foreignField": "user",
"pipeline": [
{
"$set": {
"from": "meal"
}
}
],
"as": "meal_docs"
}
},
{
"$lookup": {
"from": "activity",
"localField": "_id",
"foreignField": "user",
"pipeline": [
{
"$set": {
"from": "activity"
}
}
],
"as": "activity_docs"
}
},
{
"$lookup": {
"from": "role",
"localField": "_id",
"foreignField": "user",
"pipeline": [
{
"$set": {
"from": "role"
}
}
],
"as": "role_docs"
}
},
{
$project: {
user: "$name",
items: {
$concatArrays: [
"$activity_docs",
"$meal_docs",
"$role_docs"
]
}
}
},
{
"$unwind": "$items"
},
{
$project: {
createdAt: {
$dateTrunc: {
"date": "$items.createdAt",
"unit": "day"
}
},
content: "$items.content",
from: "$items.from"
}
},
{
"$group": {
"_id": {
"createdAt": "$createdAt",
"from": "$from"
},
"list": {
"$push": "$$ROOT.content"
}
}
},
{
"$group": {
"_id": "$_id.createdAt",
"documents": {
"$push": {
k: "$$ROOT._id.from",
v: "$$ROOT.list"
}
}
}
},
{
"$project": {
documents: {
$arrayToObject: "$documents"
}
}
},
{
"$group": {
"_id": 1,
"history": {
"$push": {
date: "$$ROOT._id",
activity: "$$ROOT.documents.activity",
meal: "$$ROOT.documents.meal",
role: "$$ROOT.documents.role"
}
}
}
}
])
推荐阅读
- java - TransactionRequiredException:执行和更新/删除查询
- azure-ad-b2c - 摆脱“请求中提供的用户名或密码无效”。
- python-3.x - RuntimeError: 预期步幅为单个整数值或 1 个值的列表以匹配卷积维度,但得到步幅 = [1, 1]
- android - my dashboard page lisview is overlap when i click on list items in android
- vmware - OWASP BWA 无法通过 NAT 连接到 IP
- excel - Excel VBA 高级过滤器
- node.js - Nodemailer错误,即使密码和emailid正确
- java - 如何提高 JmsListener 的性能?CPU 利用率低
- twilio - 如何使用 twilio url 将图像保存到 laravel 中的文件夹?我使用了 file_put_content,但它对我不起作用,任何机构有任何解决方案吗?
- git - 如何使 HEAD 指向原点/主控