mongoDb groupby与数组对象字段

Question

我已经搜索了很多基于数组字段值使用groupby，但我没有得到适当的结果在谷歌，所以我在这里张贴。

我已经尽了最大的努力，它的工作50%需要纠正我的查询，谁能帮助我这个

我有一个像 这样的数据库值

{"_id": "62b0bec8922dc767f8b933b4",
"seatSeletion": [{
"rowNo":  0,
"columnNo": 0,
"seatNo":  3
}, {
"rowNo": 0,
"columnNo": 1,
"seatNo": 4
}],
"movieId": "62af1ff6cb38656a4ffe36aa",
"movieDate": "2022-06-20T18:14:38.133+00:00",
"movieTiming": "1:30 p.m",
},
{"_id": "62b0b91560f57e0cb220db02","seatSeletion": [{
"rowNo":  0,
"columnNo": 0,
"seatNo":  1
}, {
"rowNo": 0,
"columnNo": 1,
"seatNo": 2
}],
"movieId": "62af1ff6cb38656a4ffe36aa",
"movieDate": "2022-06-20T18:14:38.133+00:00",
"movieTiming": "1:30 p.m",
}

预期输出

{
"seatSeletion": [
{
"rowNo": 0,
"columnNo": 0,
"seatNo": 1
},
{
"rowNo": 0,
"columnNo": 1,
"seatNo": 2
},
{
"_id": "62b0b90e60f57e0cb220db00",
"rowNo": 0,
"columnNo": 0,
"seatNo": 3
},
{
"_id": "62b0b90e60f57e0cb220db01",
"rowNo": 0,
"columnNo": 1,
"seatNo": 4
}
],
"movieTiming": "1:30 p.m",
"movieId": "62af1ff6cb38656a4ffe36aa",
"movieDate": "2022-06-20T11:03:37.000Z"
},

这是我在我的查询

Bookings.aggregate([
{
$match: {
$and: [{ movieId: ObjectId(bookingParam.movieId) },
{ movieTiming: bookingParam.movieTiming },
{ movieDate: dateQuery },
]
}
},
{
$group: {
_id: {
seatSeletion: '$seatSeletion', movieTiming: '$movieTiming',
movieId: '$movieId', movieDate: '$movieDate', createdBy: "$createdBy", updatedBy: "$updatedBy", movies: "$movies"
}
}
},
{
$project: {
seatSeletion: '$_id.seatSeletion', movieTiming: '$_id.movieTiming',
movieId: '$_id.movieId', movieDate: '$_id.movieDate', movies: "$_id.movies",
_id: 0
}
}
])

但我得到了这样的

{
"seatSeletion": [
{
"_id": "62b0b91560f57e0cb220db03",
"rowNo": 0,
"columnNo": 0,
"seatNo": 1
},
{
"_id": "62b0b91560f57e0cb220db04",
"rowNo": 0,
"columnNo": 1,
"seatNo": 2
}
],
"movieTiming": "1:30 p.m",
"movieId": "62af1ff6cb38656a4ffe36aa",
"movieDate": "2022-06-20T11:03:37.000Z"
},
{
"seatSeletion": [
{
"_id": "62b0b90e60f57e0cb220db00",
"rowNo": 0,
"columnNo": 0,
"seatNo": 3
},
{
"_id": "62b0b90e60f57e0cb220db01",
"rowNo": 0,
"columnNo": 1,
"seatNo": 4
}
],
"movieTiming": "1:30 p.m",
"movieId": "62af1ff6cb38656a4ffe36aa",
"movieDate": "2022-06-20T11:03:37.000Z"
}
can anyone help me to fix this issue.

Answer 1

一种选择是在$group之后使用$reduce。重要的是不要按seatSeletion分组，因为该字段的值在这些电影中不常见:

db.collection.aggregate([
{
$match: {
$and: [
{movieId: "62af1ff6cb38656a4ffe36aa"},
{movieTiming: "1:30 p.m"},
{movieDate: "2022-06-20T18:14:38.133+00:00"},

]
}
},
{
$group: {
_id: {movieTiming: "$movieTiming", movieId: "$movieId", movieDate: "$movieDate"},
seatSeletion: {$push: "$seatSeletion"}
}
},
{
$project: {
seatSeletion: {
$reduce: {
input: "$seatSeletion",
initialValue: [],
in: {$concatArrays: ["$$value", "$$this"]}
}
},
movieTiming: "$_id.movieTiming",
movieId: "$_id.movieId",
movieDate: "$_id.movieDate",
_id: 0
}
}
])

看看它在操场的例子中是如何工作的

另一个选择是使用$unwind而不是$reduce，但通常被认为较慢:

db.collection.aggregate([
{
$match: {
$and: [
{movieId: "62af1ff6cb38656a4ffe36aa"},
{movieTiming: "1:30 p.m"},
{movieDate: "2022-06-20T18:14:38.133+00:00"},

]
}
},
{$unwind: "$seatSeletion"},
{
$group: {
_id: {movieTiming: "$movieTiming", movieId: "$movieId", movieDate: "$movieDate"},
seatSeletion: {$push: "$seatSeletion"}
}
},
{
$project: {
seatSeletion: 1,
movieTiming: "$_id.movieTiming",
movieId: "$_id.movieId",
movieDate: "$_id.movieDate",
_id: 0
}
}
])

查看它在playground示例中的工作方式- unwind

Answer 2

更多输出

{
"_id": {
"movieId": "62af1ff6cb38656a4ffe36aa",
"movieDate": "2022-06-20T18:14:38.133+00:00",
"movieTiming": "1:30 p.m"
},
"seatSeletion": [
{ "rowNo": 0,"columnNo": 0,"seatNo": 3
},
{ "rowNo": 0,"columnNo": 1,"seatNo": 4
},
{ "rowNo": 0,"columnNo": 0,"seatNo": 1
},
{ "rowNo": 0,"columnNo": 1,"seatNo": 2
}
]
}

查询

db.collection.aggregate(
{
$match: {}
},
{
$unwind: {
path: '$seatSeletion'
}
},
{
$group: {
_id:
{
movieId: '$movieId',
movieDate: '$movieDate',
movieTiming: '$movieTiming'
},
seatSeletion:
{ $push: '$seatSeletion' }
}
}
)