我有一個收藏品，用于存盤特定日期分配的水。現在有幾天沒有操作設備，資料沒有存盤在資料庫中，我不會在集合中獲取資料。例如，我正在查詢過去 7 天分配的水，而該設備僅運行了兩天，結果如下：
[{
"uID" : "12345678",
"midNightTimeStamp" : NumberInt(1645381800),
"waterDispensed" : NumberInt(53)
},
{
"uID" : "12345678",
"midNightTimeStamp" : NumberInt(1645641000),
"waterDispensed" : NumberInt(30)
}]
轉換上述兩個時間戳可以得到 2 月 21 日星期一和 2 月 24 日星期四的資料。現在，如果我在 2 月 21 日到 2 月 27 日運行類似這樣的查詢，
db.getCollection("analytics").find({ uID: "12345678", midNightTimeStamp: {"$in": [1645381800, 1645468200, 1645554600, 1645641000, 1645727400, 1645813800, 1645900200]}})
這僅回傳我上面的兩個檔案，如何填充 midNightTimeStamp 的缺失值以獲取這樣的檔案串列，該串列不存在：
[{
"uID" : "12345678",
"midNightTimeStamp" : 1645381800,
"waterDispensed" : 53
},
{
"uID" : "12345678",
"midNightTimeStamp" : 1645468200,
"waterDispensed" : 0
},
{
"uID" : "12345678",
"midNightTimeStamp" : 1645554600,
"waterDispensed" : 0
},
{
"uID" : "12345678",
"midNightTimeStamp" : 1645641000,
"waterDispensed" : 30
},
{
"uID" : "12345678",
"midNightTimeStamp" : 1645727400,
"waterDispensed" : 0
},
{
"uID" : "12345678",
"midNightTimeStamp" : 1645813800,
"waterDispensed" : 0
},
{
"uID" : "12345678",
"midNightTimeStamp" : 1645900200,
"waterDispensed" : 0
}
uj5u.com熱心網友回復：
也許是這樣的：
db.collection.aggregate([
{
$group: {
_id: null,
ar: {
$push: "$$ROOT"
},
mind: {
"$min": "$midNightTimeStamp"
},
maxd: {
"$max": "$midNightTimeStamp"
}
}
},
{
$project: {
ar: {
$map: {
input: {
$range: [
"$mind",
{
"$sum": [
"$maxd",
86400
]
},
86400
]
},
as: "dateInRange",
in: {
$let: {
vars: {
dateIndex: {
"$indexOfArray": [
"$ar.midNightTimeStamp",
"$$dateInRange"
]
}
},
in: {
$cond: {
if: {
$ne: [
"$$dateIndex",
-1
]
},
then: {
$arrayElemAt: [
"$ar",
"$$dateIndex"
]
},
else: {
midNightTimeStamp: "$$dateInRange",
"waterDispensed": NumberInt(0)
}
}
}
}
}
}
}
}
},
{
$unwind: "$ar"
},
{
$project: {
_id: 0,
"waterDispensed": "$ar.waterDispensed",
midNightTimeStamp: "$ar.midNightTimeStamp",
"Date": {
$toDate: {
"$multiply": [
"$ar.midNightTimeStamp",
1000
]
}
}
}
}
])
解釋：
- $group 檔案以查找時間戳的最大值和最小值，并 $push 名為“ar”的臨時陣列中的所有元素
- $project 陣列 $mapping 生成的日期在 max 和 min 之間，1x 天步長 ( 86400 ) ，用 waterDispanced:0 填充空元素
- $展開陣列 $ar
- $project 僅在最終輸出中我們需要的欄位。
操場
uj5u.com熱心網友回復：
這與其他答案略有不同，它只需要抓住"uID"想要的。MQL 中的注釋解釋了該程序。
db.collection.aggregate([
{ // The uID we want
"$match": { "uID": "12345678" }
},
{ // grab all the uID docs as "water"
// keep uID
"$group": {
"_id": null,
"water": { "$push": "$$CURRENT" },
"uID": { "$first": "$uID" }
}
},
{ // create outArray
"$set": {
"outArray": {
// by mapping time vals
"$map": {
"input": {
"$range": [ NumberInt(1645381800), NumberInt(1645900200), 86400 ]
},
"in": {
"$cond": [
{ // already have doc?
"$in": [ "$$this", "$water.midNightTimeStamp" ]
},
{ // yes! Get it!
"$arrayElemAt": [
"$water",
{ "$indexOfArray": [ "$water.midNightTimeStamp", "$$this" ] }
]
},
{ // no, create it
"uID": "$uID",
"midNightTimeStamp": "$$this",
"waterDispensed": 0
}
]
}
}
}
}
},
{ // only need outArray now
"$project": {
"_id": 0,
"outArray": 1
}
},
{ // create docs
"$unwind": "$outArray"
},
{ // hoist them
"$replaceWith": "$outArray"
},
{ // don't need _id
"$unset": "_id"
}
])
在mongoplayground.net上試試。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/440595.html