如何进行查询包括计数和收集的数据

Question

数据示例

[
{ owner: '0x123124124', createdAt: '13 May 2022 at 04:20:21 UTC'},
{ owner: '0x123124124', createdAt: '13 May 2022 at 06:20:21 UTC'},
{ owner: '0x123124124', createdAt: '13 May 2022 at 03:20:21 UTC'},
{ owner: '0x123124124', createdAt: '12 May 2022 at 07:39:34 UTC'},
{ owner: '0x123124124', createdAt: '12 May 2022 at 07:39:34 UTC'},
{ owner: '0x454545627', createdAt: '11 May 2022 at 04:13:48 UTC'}
]

我想查询这样的返回数据

[
{ owner: '0x123124124', createdAt: '13 May 2022', count: 3},
{ owner: '0x123124124', createdAt: '13 May 2022', count: 2},
{ owner: '0x454545627', createdAt: '11 May 2022', count: 1},
]

我用这个四舍五入了几个小时。谢谢你的帮助。

Answer 1

1. $set- SetcreatedAtwith

   1.1。$trim-1.1.1.

   1.1.1。$arrayElemAt-从1.1.1.1获取第一个值。

   1.1.1.1。$split-分割createdAt字符串与单词"at"

2. $group-按owner和createdAt分组并计数。

3. $project-修饰输出文档。

db.collection.aggregate([
{
$set: {
createdAt: {
$trim: {
input: {
"$arrayElemAt": [
{
$split: [
"$createdAt",
"at"
]
},
0
]
}
}
}
}
},
{
$group: {
_id: {
owner: "$owner",
createdAt: "$createdAt"
},
count: {
$sum: 1
}
}
},
{
$project: {
_id: 0,
owner: "$_id.owner",
createdAt: "$_id.createdAt",
count: 1
}
}
])

Mongo Playground示例