mongo
Query failed with error code 2 and error message 'Field 'locale' is invalid in
错误写法 ： @Document(collection = "bookEntity")
正确写法一 ： @Document("bookEntity")
正确写法二 ：
-
- 将时间进行转换
-
- 分组求和
-
- 字段显示
db.mg_order.aggregate({
$group: {
_id: {
date: {
"$dateToString": {
format: "%Y-%m-%d", date: "$paytime", timezone: "+08:00", onNull: "unspecified",
}
}
, lotSn: "$parkingLotSn"
},
money:{$sum: "$money"}
,
count: { $sum: 1 }
}
},{
$project: {date:"$_id_date",count:"$count",money:"$money"}
})
两次分组
db.mg_order.aggregate([
{$group: {"_id": { "lotSn": "$parkingLotSn", "car": "$carPlate" }}},
{$group: {"_id": { "car":"$_id.car" },count: { $sum: 1 }}
{$sort: {count:-1}}
])
常用分组案例
db.mg_order.aggregate([
{
$group: {
_id: {
date: {
"$dateToString": {
format: "%Y-%m-%d", date: "$paytime", timezone: "+08:00", onNull: "unspecified",
}
}
, lotSn: "$parkingLotSn"
},
money: { $sum: "$money" },
count: { $sum: 1 }
}
},
{
$project: { date: "$_id.date", lotSn: "$_id.lotSn", money: "$money", count: "$count", _id: 0 }
},
{
$group: { _id: "$date", lots_ids:{$addToSet: {date:"$date",lotSn:"$lotSn",money:"$money",count:"$count"}}}
},
{
$project: {date:"$_id.date", lots_ids:"$lots_ids"}
}
])
Criteria criteria = Criteria.where("lotSn").in(lotSns).and("statisticTime").gte(start).lte(end);
MatchOperation match = Aggregation.match(criteria);
GroupOperation group = Aggregation.group("statisticDate").sum("totalTransactions").as("count")
.sum("totalRealIncome").as("money").first("statisticTime").as("time");
SortOperation sort = Aggregation.sort(Sort.Direction.ASC, "time");
ProjectionOperation project = Aggregation.project("statisticDate", "count", "money", "time");
Aggregation aggregation = Aggregation.newAggregation(match, group, project, sort);
andExpression
ProjectionOperation subPayTime = Aggregation.project("parkingLotSn", "money")
.andExpression("{$dateToString:{ format:'%Y-%m-%d',date: '$paytime'}}").as("subPayTime");
GroupOperation group = Aggregation.group("subPayTime", "parkingLotSn").count().as("count").sum("money")
.as("money");
Aggregation aggregation = Aggregation.newAggregation(match, subPayTime, group);
聚合操作之管道操作与MapReduce
数据准备
db.orders.insertMany([
{ _id: 1, cust_id: "Ant O. Knee", ord_date: new Date("2020-03-01"), price: 25, items: [ { sku: "oranges", qty: 5, price: 2.5 }, { sku: "apples", qty: 5, price: 2.5 } ], status: "A" },
{ _id: 2, cust_id: "Ant O. Knee", ord_date: new Date("2020-03-08"), price: 70, items: [ { sku: "oranges", qty: 8, price: 2.5 }, { sku: "chocolates", qty: 5, price: 10 } ], status: "A" },
{ _id: 3, cust_id: "Busby Bee", ord_date: new Date("2020-03-08"), price: 50, items: [ { sku: "oranges", qty: 10, price: 2.5 }, { sku: "pears", qty: 10, price: 2.5 } ], status: "A" },
{ _id: 4, cust_id: "Busby Bee", ord_date: new Date("2020-03-18"), price: 25, items: [ { sku: "oranges", qty: 10, price: 2.5 } ], status: "A" },
{ _id: 5, cust_id: "Busby Bee", ord_date: new Date("2020-03-19"), price: 50, items: [ { sku: "chocolates", qty: 5, price: 10 } ], status: "A"},
{ _id: 6, cust_id: "Cam Elot", ord_date: new Date("2020-03-19"), price: 35, items: [ { sku: "carrots", qty: 10, price: 1.0 }, { sku: "apples", qty: 10, price: 2.5 } ], status: "A" },
{ _id: 7, cust_id: "Cam Elot", ord_date: new Date("2020-03-20"), price: 25, items: [ { sku: "oranges", qty: 10, price: 2.5 } ], status: "A" },
{ _id: 8, cust_id: "Don Quis", ord_date: new Date("2020-03-20"), price: 75, items: [ { sku: "chocolates", qty: 5, price: 10 }, { sku: "apples", qty: 10, price: 2.5 } ], status: "A" },
{ _id: 9, cust_id: "Don Quis", ord_date: new Date("2020-03-20"), price: 55, items: [ { sku: "carrots", qty: 5, price: 1.0 }, { sku: "apples", qty: 10, price: 2.5 }, { sku: "oranges", qty: 10, price: 2.5 } ], status: "A" },
{ _id: 10, cust_id: "Don Quis", ord_date: new Date("2020-03-23"), price: 25, items: [ { sku: "oranges", qty: 10, price: 2.5 } ], status: "A" }
])
使用mapReduce
var mapFunction1 = function() {
emit(this.cust_id, this.price);
};
var reduceFunction1 = function(keyCustId, valuesPrices) {
return Array.sum(valuesPrices);
};
db.orders.mapReduce(
mapFunction1,
reduceFunction1,
{ out: "map_reduce_example" }
)
使用管道操作
// 根据分组将结果输出
db.orders.aggregate([
{ $group: { _id: "$cust_id", value: { $sum: "$price" } } },
{ $out: "agg_alternative_1" }
])
使用mapReduce
var mapFunction2 = function() {
for (var idx = 0; idx < this.items.length; idx++) {
var key = this.items[idx].sku;
var value = { count: 1, qty: this.items[idx].qty };
emit(key, value);
}
};
var reduceFunction2 = function(keySKU, countObjVals) {
var reducedVal = { count: 0, qty: 0 };
for (var idx = 0; idx < countObjVals.length; idx++) {
reducedVal.count += countObjVals[idx].count;
reducedVal.qty += countObjVals[idx].qty;
}
return reducedVal;
};
var finalizeFunction2 = function (key, reducedVal) {
reducedVal.avg = reducedVal.qty/reducedVal.count;
return reducedVal;
};
db.orders.mapReduce(
mapFunction2,
reduceFunction2,
{
out: { merge: "map_reduce_example2" },
query: { ord_date: { $gte: new Date("2020-03-01") } },
finalize: finalizeFunction2
}
);
使用管道
db.orders.aggregate( [
{ $match: { ord_date: { $gte: new Date("2020-03-01") } } }, // 根据条件进行过滤
{ $unwind: "$items" }, // 将items中的数据进行展开
{ $group: { _id: "$items.sku", qty: { $sum: "$items.qty" }, orders_ids: { $addToSet: "$_id" } } }, // 根据sku进行分组,计算qty总和,将sku放入集合
// 数据投影: count,qty,avg=devide/count
{ $project: { value: { count: { $size: "$orders_ids" }, qty: "$qty", avg: { $divide: [ "$qty", { $size: "$orders_ids" } ] } } } },
// 将上一步的结果存入collection中,如果匹配到进行更新，否则进行插入
{ $merge: { into: "agg_alternative_3", on: "_id", whenMatched: "replace", whenNotMatched: "insert" } }
] )
// $addToSet 将数据放入到set集合中； $size 统计集合中数据个数
分区间进行统计
db.mg_parkin.aggregate([
{$match: {ioType:1}},
{$addFields: {
timePart:{$function: {
body: function(stayTime) {
if(stayTime != null){
if(stayTime == 1){
return "0-0.5h";
}else if(stayTime / 2 <= 2){
return "1-2h";
}else if(stayTime / 2 <= 6){
return "2-6h";
}else {
return "6h以上"
}
}
return "未知"
},
args: [ "$stayTime"],
lang: "js"
}}
}},
{$group: { _id: "$timePart", count:{$sum: 1}}}
])
addFields
一次使用
AggregationOperation addFields = new AggregationOperation() {
@Override
public Document toDocument(AggregationOperationContext context) {
return new Document("$addFields", new Document("name", "$name.am"));
}
};
泛型
public AggregationOperation aggregateAddFields(final String field, final String valueExpresion) {
AggregationOperation addFields = new AggregationOperation() {
@Override
public Document toDocument(AggregationOperationContext context) {
return new Document("$addFields", new Document(field, valueExpresion));
}
};
return addFields;
}
...
AggregationOperation addFields = aggregateAddFields("name", "$name.am");
导入BSON文件
mongorestore --host localhost --port 27017 --db dbname --collection collection_name 文件路径
地理位置使用geojson
mongodb建立地理位置索引出现错误longitude/latitude is out of bound
解决方法：
在mongodb中存储GeoJSON的格式是先存经度后存纬度，经度它的范围在±180以内，而维度在±90以内，所以出现这种问题直接找是不是经纬度顺序存储反了即可。（上面的存储顺序是正确的大家不必纠结）
