MongoDB ID 数组用于比较和提取值

Question

我对在堆栈中使用MongoDB和NoSQL有点陌生。最后，由于JSON解析数据的事实，我已经切换到了NoSQL。我可以在MySQL中完成我想要的东西，但我也在尝试教会自己一些新的东西。

目前，我在连接和设置NodeJS的Schemas方面没有问题。我在MongoDB中有一份文件，它根据国家平均值返回我的客户支付的燃油费用——我们的客户为我们提供了范围，以便我们能够找到他们支付的具体金额。

我的MongoDB文档如下所示：

main_database
|- customerFSC (name of document)
|-- 
{
"_id":{"$oid":"5e5ecc04e8da861114079ab2"},
"custID":"01",
"custName":"Customer ABC",
"avgLow":["1.19","1.24","1.29","1.34","1.39","1.44","1.49","1.54","1.59","1.64","1.69","1.74","1.79","1.84","1.89","1.94","1.99"],
"avgHigh":["1.239","1.289","1.339","1.389","1.439","1.489","1.539","1.589","1.639","1.689","1.739","1.789","1.839","1.889","1.939","1.989","2.039"],
"custFscPM":["0.01","0.02","0.03","0.04","0.05","0.06","0.07","0.08","0.09","0.10","0.11","0.12","0.13","0.14","0.15","0.16","0.17"]
}

如果本周的燃料平均值为1.215，则其范围在1.19的avgLow和1.239的avgHigh之间，但在客户FscPM 中返回0.01的实际支付

我的MongoDB Mongoose节点代码如下

const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const fscCustSchema = new Schema({
custID: String,
custName: String,
avgLow: Array,
avgHigh: Array,
custFscPM: Array
},
{ collection: 'customerFSC' }
);
module.exports = mongoose.model('customerFSC', fscCustSchema);


fscCustModel.find( { $and: [ 
{ $gte: [ { avgLow: USAFscAVG } ] },
{ $lte: [ { avgHigh: USAFscAVG } ] }
]},function(err2,resp2) {
if (err2) console.log("Error Thrown Looking up Fuel:" + err2);
console.log(resp);
});

Answer 1

您可以使用聚合，这是比使用.find()更强大的查询MongoDB的方法，请尝试以下查询：

db.collection.aggregate([
{
$project: {
custFscPM: {
$arrayElemAt: [
"$custFscPM",
{
$indexOfArray: [
"$avgLow",
{
$arrayElemAt: [
{
$filter: {
input: "$avgLow",
cond: {
$gte: [
{
$toDouble: "$$this"
},
1.94 // Your input
]
}
}
},
0
]
}
]
}
]
}
}
}
])

查看您的数据，在这个查询中，我们在avgLow数组中找到大于或等于传入值&获取它的索引&在同一索引中查找custFscPM中的元素将解决您的需求。

注意：由于avgLow中的值是字符串，我们需要将这些值转换为两倍才能进行比较。

测试：MongoDB Playground