1、原生sql脚本:
db.getCollection('value_stream_ci_source').aggregate(
{
"$match": {
"startTime": {
'$gte': ISODate("2022-04-11T00:00:00Z"),
'$lt': ISODate("2022-04-30T00:00:00Z")
},
"elementId": {
"$in": ["512578288305049600", "512578679881076736", "512584032177885184"]
},
}
},
{
"$group": {
"_id": {
"$subtract": [
{
"$subtract": ["$startTime", new Date("1970-01-01")]
},
{
"$mod": [
{
"$subtract": ["$startTime", new Date("1970-01-01")]
},
14400000 /*聚合时间段,30分钟*/
]
}
]
},
// "repstime": {'$avg': '$repstime'},
"timelist": {
'$push': '$startTime'
},
"bootInfos": {
'$push': '$$ROOT'
} /*查看聚合那些时间*/
}
},
{
"$addFields": {
bootInfos: {
$map: {
input: "$bootInfos",
as: "bootInfo",
in: {
"$mergeObjects": [
"$$bootInfo",
{
"duration": {
"$subtract" :[
"$$bootInfo.endTime","$$bootInfo.startTime"
]
}
}
]
}
}
}
}
},
{
"$project": {
"groupTimeInfo": {
"$add": [new Date(0), "$_id"]
},
"bootInfos": 1
}
},
{
"$sort": {
"groupTimeInfo": 1
}
}
)
2、观察Pipeline中的该部分内容
{
"$addFields": {
bootInfos: {
$map: {
input: "$bootInfos",
as: "bootInfo",
in: {
"$mergeObjects": [
"$$bootInfo",
{
"duration": {
"$subtract" :
["$$bootInfo.endTime","$$bootInfo.startTime"]
}
}
]
}
}
}
}
},
2、查询结果集:
// 查询结果集:
{
"_id": NumberLong("1651046400000"),
"bootInfos": [
{
"_id": ObjectId("627398ec9c52d69fe6eeb240"),
"id": "321083875004518400",
"elementId": "512578288305049600",
"orgId": "3216523602522214545",
"pipelineExecutiveId": "6e1857b1-ecc7-sdfsdf-9d47-e3c616ba0795",
"status": "RunFailed",
"phaseInfos": {
"pipeline": {
"pipelineName": "346-b5b2-sdfsdfds",
"stages": [
{
"taskInfos": [
{
"name": "checkout",
"requires": null,
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "checkout-6vv2c",
"type": "checkout",
"startTime": ISODate("2022-04-28T04:19:08.000Z"),
"endTime": ISODate("2022-04-28T04:19:09.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z")
},
{
"taskInfos": [
{
"name": "say-hello",
"requires": [
"checkout"
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "say-hello-hd7zr",
"type": "run",
"startTime": ISODate("2022-04-28T04:19:20.000Z"),
"endTime": ISODate("2022-04-28T04:19:20.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z")
}
]
}
},
"startTime": ISODate("2022-04-27T10:16:16.7Z"),
"endTime": ISODate("2022-04-27T10:18:16.7Z"),
"createTime": ISODate("2022-05-05T10:29:16.251Z"),
"updateTime": ISODate("2022-05-05T09:29:16.251Z"),
"del": NumberInt("0"),
"branchName": "dev2.0.0",
"duration": NumberLong("120000")
},
{
"_id": ObjectId("627398ca9c52d69fe6eeb23f"),
"id": "321083735082536960",
"elementId": "512578288305049600",
"orgId": "3216523602522214545",
"pipelineExecutiveId": "6e1857b1-ecc7-465c-9d47-e3c616ba0795",
"status": "RunFailed",
"branchName": "dev1.0.0",
"phaseInfos": {
"pipeline": {
"pipelineName": "b5b2-722e314815ff",
"stages": [
{
"taskInfos": [
{
"name": "checkout",
"requires": null,
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "checkout-6vv2c",
"type": "checkout",
"startTime": ISODate("2022-04-28T04:19:08.000Z"),
"endTime": ISODate("2022-04-28T04:19:09.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z")
},
{
"taskInfos": [
{
"name": "say-hello",
"requires": [
"checkout"
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "say-hello-hd7zr",
"type": "run",
"startTime": ISODate("2022-04-28T04:19:20.000Z"),
"endTime": ISODate("2022-04-28T04:19:20.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z")
}
]
}
},
"startTime": ISODate("2022-04-27T09:16:04.89Z"),
"endTime": ISODate("2022-04-27T09:18:04.89Z"),
"createTime": ISODate("2022-05-05T09:28:42.891Z"),
"updateTime": ISODate("2022-05-05T09:28:42.891Z"),
"del": NumberInt("0"),
"duration": NumberLong("120000")
}
],
"groupTimeInfo": ISODate("2022-04-27T08:00:00.000Z")
}
// 2
{
"_id": NumberLong("1651060800000"),
"bootInfos": [
{
"_id": ObjectId("627399219c52d69fe6eeb244"),
"id": "321084100247031808",
"elementId": "512584032177885184",
"orgId": "3216523602522214545",
"pipelineExecutiveId": "6e1857b1-ecc7-sdfsdf-9d47-e3c616ba0795",
"status": "RunSucceeded",
"phaseInfos": {
"pipeline": {
"pipelineName": "b214-4903-b5b2-sdfsdfds",
"stages": [
{
"taskInfos": [
{
"name": "checkout",
"requires": null,
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "checkout-6vv2c",
"type": "checkout",
"startTime": ISODate("2022-04-28T04:19:08.000Z"),
"endTime": ISODate("2022-04-28T04:19:09.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z")
},
{
"taskInfos": [
{
"name": "say-hello",
"requires": [
"checkout"
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "say-hello-hd7zr",
"type": "run",
"startTime": ISODate("2022-04-28T04:19:20.000Z"),
"endTime": ISODate("2022-04-28T04:19:20.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z")
}
]
}
},
"startTime": ISODate("2022-04-27T14:18:02.965Z"),
"endTime": ISODate("2022-04-27T14:20:02.965Z"),
"createTime": ISODate("2022-05-05T14:30:09.953Z"),
"updateTime": ISODate("2022-05-05T09:30:09.953Z"),
"del": NumberInt("0"),
"branchName": "dev6.0.0",
"duration": NumberLong("120000")
},
{
"_id": ObjectId("627399109c52d69fe6eeb243"),
"id": "321084025168990208",
"elementId": "512578679881076736",
"orgId": "3216523602522214545",
"pipelineExecutiveId": "6e1857b1-ecc7-sdfsdf-9d47-e3c616ba0795",
"status": "RunSucceeded",
"phaseInfos": {
"pipeline": {
"pipelineName": "b214-4903-b5b2-sdfsdfds",
"stages": [
{
"taskInfos": [
{
"name": "checkout",
"requires": null,
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "checkout-6vv2c",
"type": "checkout",
"startTime": ISODate("2022-04-28T04:19:08.000Z"),
"endTime": ISODate("2022-04-28T04:19:09.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:00.000Z"),
"endTime": ISODate("2022-04-28T04:19:10.000Z")
},
{
"taskInfos": [
{
"name": "say-hello",
"requires": [
"checkout"
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z"),
"status": "RunSucceeded",
"steps": [
{
"name": "say-hello-hd7zr",
"type": "run",
"startTime": ISODate("2022-04-28T04:19:20.000Z"),
"endTime": ISODate("2022-04-28T04:19:20.000Z"),
"status": "RunSucceeded"
}
]
}
],
"startTime": ISODate("2022-04-28T04:19:11.000Z"),
"endTime": ISODate("2022-04-28T04:19:21.000Z")
}
]
}
},
"startTime": ISODate("2022-04-27T13:18:18.523Z"),
"endTime": ISODate("2022-04-27T13:20:18.523Z"),
"createTime": ISODate("2022-05-05T13:29:52.053Z"),
"updateTime": ISODate("2022-05-05T09:29:52.053Z"),
"del": NumberInt("0"),
"branchName": "dev5.0.0",
"duration": NumberLong("120000")
}
],
"groupTimeInfo": ISODate("2022-04-27T12:00:00.000Z")
}
解释:
a d d F i e l d s 是 m o n g o 中 操 作 符 , addFields是mongo中操作符, addFields是mongo中操作符,map循环数组中的每一个元素,采用"$mergeObjects"为每一个数组中的对象添加一个新的属性!!