MongoDB的使用
- 主要内容有: MongoDB的简单查询操作, MongoDB更新操作, MongoDB聚合查询操作
- 用Java代码实现在最下面，可以做对照
简单查询
- 在Java中的使用
- Query：用于创建查询条件的对象, 使用时一般需要传入如"Criteria"构建的查询条件
- Criteria: 构建具体查询条件的对象
- MongoDB官方文档: https://www.mongodb.com/docs/manual/reference/operator/query/
- 对应的Java方法
mongodbFind()具体实现在最下面的Java代码里
1. 比较运算符
1. $eq匹配等于指定值的文档
2. $gt匹配大于指定值的文档
3. $gte匹配大于等于指定值的文档
4. $in匹配数组中的任一值
5. $lt匹配小于指定值的文档
6. $lte匹配小于等于指定值的文档
7. $ne匹配不等于指定值的文档
8. $nin 不匹配数组中的值
// 数据
{ item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
{ item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
{ item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
{ item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
{ item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
// 普通字段的in
db.inventory.find( { qty: { $in: [ 25, 50 ] } }, { _id: 0 } )
// 数组的多个元素in, 精确, 只能匹配出 {item: "notebook"} 顺序和数量都必须匹配
db.inventory.find( { tags: ["red", "blank"] } )
// 数组的多个元素的in, 包含 匹配出 {item: "notebook","journal","paper","planner"}
db.inventory.find( { tags: { $all: ["red", "blank"] } } )
// 数组的单个元素, 包含
db.inventory.find( { tags: "red" } )
// 比较操作, 有一个元素符合即返回
db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
// 数组中每一个元素都需符合 {item: "planner"}
db.inventory.find( { dim_cm: { $elemMatch: { $gt: 22, $lt: 30 } } } )
// 数组中第二个元素大于25的
db.inventory.find( { "dim_cm.1": { $gt: 25 } } )
// 内嵌数组查询
{ item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
{ item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
{ item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
{ item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
{ item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
// 内嵌数组, 顺序一致, 可以查询结果
db.inventory3.find( { "instock": { warehouse: "A", qty: 5 } } )
// 内嵌数组, 顺序不一致, 无法查询出来结果
db.inventory3.find( { "instock": { qty: 5, warehouse: "A"} } )
// 内嵌数组, 使用“.”查询
db.inventory.find( { 'instock.qty': { $lte: 20 } } )
// 内嵌数组, 遍历数组中的每一个对象, 可以查询出 paper和postcard
db.inventory3.find( { "instock": { $elemMatch: { qty: 15, warehouse: "B" } } } )
// 内嵌文档的查询
{ item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
{ item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
{ item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
{ item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
{ item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
// 内嵌文档的查询, 必须保证顺序
db.inventory2.find( { size: { h: 14, w: 21, uom: "cm" } } )
// 内嵌文档的查询, 顺序不对无法查询出来
db.inventory2.find( { size: { w: 21, h: 14, uom: "cm" } } )
// 使用”.“查询
db.inventory2.find( { "size.uom": "in" } )
// 使用比较$lt查询
db.inventory2.find( { "size.h": { $lt: 15 } } )
// 内嵌文档多字段查询
db.inventory2.find( { "size.h": { $lt: 15 }, "size.uom": "in"} )
2. 逻辑运算符
1. $and 与条件查询
db.zsyyIBD.find({
$and: [
{info_patientID:'000530204300'},
{ visit_id:'1669198463581NA'}
]
})
2. $or或条件查询
db.zsyyIBD.find({
$or: [
{info_patientID:'000530204300', visit_id:'1669198463581NA'},
{info_patientID:'ZY010001298384', visit_id:'1669198463126NA'}
]
})
3. 元素查询运算符
1. $exists查询存在指定字段的文档
2. $type查询类型为指定类型的文档, 此运算符第二个参数的值还有很多,
db.inventory.find({item:{$exists: true}})
db.inventory.find({item:{$type: "string"}}) //{item:123}的数据无法查出,
4. 评估查询运算符
1. $expr 使用聚合表达式查询数据, 参数是一个表达式
{ "_id" : 1, "category" : "food", "budget": 400, "spent": 450 }
{ "_id" : 2, "category" : "drinks", "budget": 100, "spent": 150 }
{ "_id" : 3, "category" : "clothes", "budget": 100, "spent": 50 }
{ "_id" : 4, "category" : "misc", "budget": 500, "spent": 300 }
{ "_id" : 5, "category" : "travel", "budget": 200, "spent": 650 }
// 输出spent比budget大的数据
db.monthlyBudget.find( { $expr: { $gt: [ "$spent" , "$budget" ] } } )
{ "_id" : 1, "item" : "binder", "qty" : NumberInt("100"), "price" : NumberDecimal("12") },
{ "_id" : 2, "item" : "notebook", "qty" : NumberInt("200"), "price" : NumberDecimal("8") },
{ "_id" : 3, "item" : "pencil", "qty" : NumberInt("50"), "price" : NumberDecimal("6") },
{ "_id" : 4, "item" : "eraser", "qty" : NumberInt("150"), "price" : NumberDecimal("3") },
{ "_id" : 5, "item" : "legal pad", "qty" : NumberInt("42"), "price" : NumberDecimal("10") }
// Aggregation expression to calculate discounted price
let discountedPrice = {
$cond: {
if: { $gte: ["$qty", 100] },
then: { $multiply: ["$price", NumberDecimal("0.50")] },
else: { $multiply: ["$price", NumberDecimal("0.75")] }
}
};
db.supplies.find( { $expr: { $lt:[ discountedPrice, NumberDecimal("5") ] } });
2. $mod
{ field: { $mod: [ divisor, remainder ] } }查找字段field的值除以divisor等于remainder的数据
db.inventory.insertMany( [
{ "_id" : 1, "item" : "abc123", "qty" : 0 },
{ "_id" : 2, "item" : "xyz123", "qty" : 5 },
{ "_id" : 3, "item" : "ijk123", "qty" : 12 }
] )
db.inventory.find( { qty: { $mod: [ 4, 0 ] } } )
// result
{ "_id" : 1, "item" : "abc123", "qty" : 0 }
{ "_id" : 3, "item" : "ijk123", "qty" : 12 }
3. $regex 正则查询
5. 数组查询运算符
1. $all
{ tags: { $all: [ "ssl" , "security" ] } }
等于
{ $and: [ { tags: "ssl" }, { tags: "security" } ] }
2. $elemMatch 如果数组字段中的元素与所有指定的$elemMatch条件匹配，则选择文档
3. $size 查询数组为指定大小的数据
db.collection.find( { field: { $size: 2 } } );
更新操作
- 参考代码
updateOperate() - 官方文档地址: https://www.mongodb.com/docs/manual/reference/operator/update/
1. 字段更新操作
1. $inc 将字段递增指定值
db.products.insertOne(
{
_id: 1,
sku: "abc123",
quantity: 10,
metrics: { orders: 2, ratings: 3.5 }
}
)
db.products.updateOne(
{ sku: "abc123" },
{ $inc: { quantity: -2, "metrics.orders": 1 } }
)
// result
{
_id: 1,
sku: 'abc123',
quantity: 8,
metrics: { orders: 3, ratings: 3.5 }
}
2. $min 如果更新的值小于当前字段的值则更新
db.scores.insertOne( { _id: 1, highScore: 800, lowScore: 200 } )
db.scores.updateOne( { _id: 1 }, { $min: { lowScore: 150 } } )
{ _id: 1, highScore: 800, lowScore: 150 } // result
db.scores.updateOne( { _id: 1 }, { $min: { lowScore: 250 } } )
{ _id: 1, highScore: 800, lowScore: 150 } // result
3. $max 如果更新的值大于当前字段的值则更新
4. $mul 字段乘以设置的值
db.products.insertOne(
{ "_id" : 1, "item" : "Hats", "price" : Decimal128("10.99"), "quantity" : 25 }
)
db.products.updateOne(
{ _id: 1 },
{ $mul:
{
price: Decimal128( "1.25" ), // price*1.25
quantity: 2 //quantity*2
}
}
)
// result
{ _id: 1, item: 'Hats', price: Decimal128("13.7375"), quantity: 50 }
// 一个不存在的字段乘以值后, 会新生成一个字段
db.products.insertOne( { _id: 2, item: "Unknown" } )
db.products.updateOne(
{ _id: 2 },
{ $mul: { price: Decimal128("100") } }
)
{ "_id" : 2, "item" : "Unknown", "price" : NumberLong(0) }
5. $rename 重命名字段
// 重命名普通字段
db.students.updateOne(
{ _id: 1 },
{ $rename: { 'nickname': 'alias', 'cell': 'mobile' } }
)
// 重命名嵌入文档的字段
db.students.updateOne( { _id: 1 }, { $rename: { "name.first": "name.fname" } } )
6. $set 更新字段的值, 如果字段不存在, 则新增这个字段
db.products.insertOne(
{
_id: 100,
quantity: 250,
instock: true,
reorder: false,
details: { model: "14QQ", make: "Clothes Corp" },
tags: [ "apparel", "clothing" ],
ratings: [ { by: "Customer007", rating: 4 } ]
}
)
db.products.updateOne(
{ _id: 100 },
{ $set:
{
quantity: 500,
details: { model: "2600", make: "Fashionaires" },
tags: [ "coats", "outerwear", "clothing" ]
}
}
)
// result
{
_id: 100,
quantity: 500,
instock: true,
reorder: false,
details: { model: '2600', make: 'Fashionaires' },
tags: [ 'coats', 'outerwear', 'clothing' ],
ratings: [ { by: 'Customer007', rating: 4 } ]
}
// 更新嵌入文档的字段的值
db.products.updateOne(
{ _id: 100 },
{ $set: { "details.make": "Kustom Kidz" } }
)
// result
{
_id: 100,
quantity: 500,
instock: true,
reorder: false,
details: { model: '2600', make: 'Kustom Kidz' },
tags: [ 'coats', 'outerwear', 'clothing' ],
ratings: [ { by: 'Customer007', rating: 4 } ]
}
// 更新数组的值, 使用下标
db.products.updateOne(
{ _id: 100 },
{ $set:
{
"tags.1": "rain gear",
"ratings.0.rating": 2
}
}
)
// result
{
_id: 100,
quantity: 500,
instock: true,
reorder: false,
details: { model: '2600', make: 'Kustom Kidz' },
tags: [ 'coats', 'rain gear', 'clothing' ],
ratings: [ { by: 'Customer007', rating: 2 } ]
}
7. $unset 删除一个字段
db.products.insertMany( [
{ "item": "chisel", "sku": "C001", "quantity": 4, "instock": true },
{ "item": "hammer", "sku": "unknown", "quantity": 3, "instock": true },
{ "item": "nails", "sku": "unknown", "quantity": 100, "instock": true }
] )
db.products.updateOne(//更新一条数据
{ sku: "unknown" },
{ $unset: { quantity: "", instock: "" } }
)
// result
{
item: 'chisel',
sku: 'C001',
quantity: 4,
instock: true
},
{
item: 'hammer',
sku: 'unknown'
},
{
item: 'nails',
sku: 'unknown',
quantity: 100,
instock: true
}
2. 数组更新操作
1. $addToSet 如果值不存在数组, 则往数组里面添加一个值
// colors不是数组时, 执行addToSet不起作用
db.pigments.insertOne( { _id: 1, colors: "blue, green, red" } )
db.pigments.updateOne(
{ _id: 1 },
{ $addToSet: { colors: "mauve" } }
)
// addToSet的值是一个数组, 则会把这个数组作为一个元素更新
db.alphabet.insertOne( { _id: 1, letters: ["a", "b"] } )
db.alphabet.updateOne(
{ _id: 1 },
{ $addToSet: { letters: [ "c", "d" ] } }
)
// result
{ _id: 1, letters: [ 'a', 'b', [ 'c', 'd' ] ] }
// 解决上面的问题, 使用$each, 会逐个判断,然后更新
{ _id: 2, item: "cable", tags: [ "electronics", "supplies" ] } // 数据
db.inventory.updateOne(
{ _id: 2 },
{ $addToSet: { tags: { $each: [ "camera", "electronics", "accessories" ] } } }
)
// result
{
_id: 2,
item: "cable",
tags: [ "electronics", "supplies", "camera", "accessories" ]
}
// addToSet的值不是数组
db.inventory.insertOne(
{ _id: 1, item: "polarizing_filter", tags: [ "electronics", "camera" ] }
)
db.inventory.updateOne( //更新一个不存在的
{ _id: 1 },
{ $addToSet: { tags: "accessories" } }
)
db.inventory.updateOne( //更新一个已存在的
{ _id: 1 },
{ $addToSet: { tags: "camera" } }
)
{ _id: 2, item: "cable", tags: [ "electronics", "supplies" ] } // result
2. $pop 字段值为-1则移除数组的第一个元素, 字段值为1则移除数组的最后一个元素
db.students.insertOne( { _id: 1, scores: [ 8, 9, 10 ] } )
db.students.updateOne( { _id: 1 }, { $pop: { scores: -1 } } ) //移除第一个元素
{ _id: 1, scores: [ 9, 10 ] } // result
db.students.updateOne( { _id: 11 }, { $pop: { scores: 1 } } ) //移除最后一个元素
{ _id: 10, scores: [ 9 ] } //result
3. $pull 该运算符从现有数组中删除与指定条件匹配的一个或多个值的所有实例
db.stores.insertMany( [
{
_id: 1,
fruits: [ "apples", "pears", "oranges", "grapes", "bananas" ],
vegetables: [ "carrots", "celery", "squash", "carrots" ]
},
{
_id: 2,
fruits: [ "plums", "kiwis", "oranges", "bananas", "apples" ],
vegetables: [ "broccoli", "zucchini", "carrots", "onions" ]
}
] )
db.stores.updateMany( //移除fruits里面的apples和oranges. vegetables中的carrots
{ },
{ $pull: { fruits: { $in: [ "apples", "oranges" ] }, vegetables: "carrots" } }
)
{ //result
_id: 1,
fruits: [ 'pears', 'grapes', 'bananas' ],
vegetables: [ 'celery', 'squash' ]
},
{
_id: 2,
fruits: [ 'plums', 'kiwis', 'bananas' ],
vegetables: [ 'broccoli', 'zucchini', 'onions' ]
}
// 移除数组中数据大于某个值的元素
db.profiles.insertOne( { _id: 1, votes: [ 3, 5, 6, 7, 7, 8 ] } )
db.profiles.updateOne( { _id: 1 }, { $pull: { votes: { $gte: 6 } } } )
{ _id: 1, votes: [ 3, 5 ] }//result
// 移除嵌套数组文档中的数据
db.survey.insertMany( [
{
_id: 1,
results: [
{
item: "A",
score: 5,
answers: [ { q: 1, a: 4 }, { q: 2, a: 6 } ]
},
{
item: "B",
score: 8,
answers: [ { q: 1, a: 8 }, { q: 2, a: 9 } ]
}
]
},
{
_id: 2,
results: [
{
item: "C",
score: 8,
answers: [ { q: 1, a: 8 }, { q: 2, a: 7 } ]
},
{
item: "B",
score: 4,
answers: [ { q: 1, a: 0 }, { q: 2, a: 8 } ]
}
]
}
] )
db.survey.updateMany(// 移除results.answers的q的值等于2的, a大于8的
{ },
{
$pull:
{
results:
{
answers: { $elemMatch: { q: 2, a: { $gte: 8 } } }
}
}
}
)
{//result 移除了一二条数据中的B
_id: 1,
results: [
{
item: 'A',
score: 5,
answers: [ { q: 1, a: 4 }, { q: 2, a: 6 } ]
}
]
},
{
_id: 2,
results: [
{
item: 'C',
score: 8,
answers: [ { q: 1, a: 8 }, { q: 2, a: 7 } ]
}
]
}
4. $pullAll 移除数组中的元素, 不同于$pull, 此操作没有查询条件
db.survey.insertOne( { _id: 1, scores: [ 0, 2, 5, 5, 1, 0 ] } )
db.survey.updateOne( { _id: 1 }, { $pullAll: { scores: [ 0, 5 ] } } )//移除0,5
// 对比$pull
db.survey.updateOne( { _id: 1 }, { $pull: { scores: {$in:[ 0, 5 ]} } } )
{ "_id" : 1, "scores" : [ 2, 1 ] } //result
5. $push 往数组中追加元素
db.students.insertOne( { _id: 1, scores: [ 44, 78, 38, 80 ] } )
db.students.updateOne(
{ _id: 1 },
{ $push: { scores: 89 } }
)
{ _id: 1, scores: [ 44, 78, 38, 80, 89 ] }
// 追加多个值 $each
db.students.updateOne(
{ name: "joe" },
{ $push: { scores: { $each: [ 90, 92, 85 ] } } }
)
//其他几个参数 $sort:排序 $slice:保留的数组中元素的个数
db.students.insertOne(
{
"_id" : 5,
"quizzes" : [
{ "wk": 1, "score" : 10 },
{ "wk": 2, "score" : 8 },
{ "wk": 3, "score" : 5 },
{ "wk": 4, "score" : 6 }
]
}
)
db.students.updateOne(
{ _id: 5 },
{
$push: {
quizzes: {
$each: [ { wk: 5, score: 8 }, { wk: 6, score: 7 }, { wk: 7, score: 6 } ],
$sort: { score: -1 },
$slice: 3
}
}
}
)
{ //result 追加数据后排序完保留了三个
"_id" : 5,
"quizzes" : [
{ "wk" : 1, "score" : 10 },
{ "wk" : 2, "score" : 8 },
{ "wk" : 5, "score" : 8 }
]
}
6. $each 每一个元素, 用在 $addToSet, $push等
-
db.students.insertOne( { "_id" : 1, "scores" : [ 100 ] } ) db.students.updateOne(// 在0索引处添加数据 { _id: 1 }, { $push: { scores: { $each: [ 50, 60, 70 ], $position: 0 //position=2时代表在中间插入 } } } ) { "_id" : 1, "scores" : [ 50, 60, 70, 100 ] }//result
聚合查询
1. 聚合查询
// 一个简单的聚合查询
db.artists.aggregate()
.match({}) //match操作
.project({}) //project操作
.sort({_id:-1}) //sort操作
.limit(100) //limit操作
// 有下面一组数据, 想要搜索 lab.crp_labsubitem等于超敏C反应蛋白 且 lab.crp_applytime大于2017-01-01的数据
{
"_id" : ObjectId("63a173c531f9a16de00eb7fd"),
"lab" : [
{
"crp_applytime" : "2016-10-08 13:12:50", //采集时间
"crp_specialtype" : "静脉血",
"crp_labsubitem" : "超敏C反应蛋白" //名称
},
{
"crp_applytime" : "2017-10-08 13:12:50",
"crp_specialtype" : "静脉血",
"crp_labsubitem" : "C反应蛋白(CRP)" //名称
}
],
"name" : "患者A"
}
// 简单搜索-搜出的结果不对
db.juhe.find({
'lab.crp_labsubitem': '超敏C反应蛋白',
'lab.crp_applytime': {
$gt: '2017-01-01'
}
})
// 简单搜索-可以搜索出结果
db.juhe.find({lab:{$elemMatch: {"crp_labsubitem" : "超敏C反应蛋白", "crp_applytime": {$gt: "2017-01-01"}}}})
// 聚合查询 project阶段判断
db.juhe.aggregate([
{
$project: {
lab2: {
$filter: {
input: "$lab",
as: "jiagong",
cond: {
$and: [
{
$eq: ["$$jiagong.crp_labsubitem", "超敏C反应蛋白"]
},
{
$gt: [
"$$jiagong.crp_applytime", '2017-01-01'
]
}
]
}
}
}
}
}
])
2. 聚合查询中的一些管道操作
$match: 用于过滤数据，只输出符合条件的文档$project: 修改输入文档的结构。可以用来重命名、增加或删除字段$limit: 用来限制MongoDB聚合管道返回的文档数。$skip: 在聚合管道中跳过指定数量的文档，并返回余下的文档。$unwind: 将文档中的某一个数组类型字段拆分成多条，每条包含数组中的一个值。$group: 将集合中的文档分组，可用于统计结果$sort: 将输入文档排序后输出$count: 用于统计文档数量- 文档: https://www.cnblogs.com/janson/p/6164775.html
3. 使用到的Java类
-
AggregationOperation：聚合管道的操作对象，比如$group/$lookup/$unwind/$sort…使用的时候，需要先构建对应的聚合操作，比如$group（需要构建具体操作Aggregation.group(“”)）， 可以创建多个，最后一并传入到Aggregation对象中，再交给MogoTemplate去执行管道聚合GroupOperation groupOperation = Aggregation.group("").max(AccumulatorOperators.Max.maxOf("")).as(""); ProjectionOperation projectionOperation = Aggregation.project("").and(ConditionalOperators.Cond.when("").thenValueOf("").otherwiseValueOf("")).as(""); MatchOperation matchOperation = Aggregation.match(Criteria.where("").is("")); SortOperation sortOperation = Aggregation.sort(Sort.Direction.ASC, ""); SkipOperation skipOperation = Aggregation.skip((long) 100); LimitOperation limitOperation = Aggregation.limit(10); -
Aggregation：上面AggregationOperation的集合，把上面的所有聚合操作存在一起，template调用aggregate方法的时候，传入该对象Aggregation aggregation = Aggregation.newAggregation(matchOperation, groupOperation, projectionOperation, limitOperation, skipOperation, limitOperation) -
AggregationExpression: AggregationExpression可以与聚合管道阶段（如project和group）中的字段表达式一起使用
-
ArithmeticOperators算术相关的操作
-
ArrayOperators数组相关的操作
-
StringOperators字符串相关的操作
-
DateOperators日期相关的操作
-
ConditionalOperators条件相关的操作
ProjectionOperation projectionOperation = Aggregation.project("").and(ConditionalOperators.Cond.when("").thenValueOf("").otherwiseValueOf("")).as("");
-
4. $project: 重新构造文档/数据的结构，比如删除或增加字段，每个输入文档对应一个输出文档, 参考方法project()
{"_id":8,"item":"abc","price":NumberDecimal("10"),"quantity":5,"date":ISODate("2016-02-07T04:20:13.000+08:00"),"fee":10}, {"_id":7,"item":"def","price":NumberDecimal("7.5"),"quantity":10,"date":ISODate("2015-09-10T16:43:00.000+08:00"),"fee":10}
db.sales.aggregate([
{
$project: {
_id:0,
item: 1,
total: { // 增加一个total字段
$multiply: [
"$price",
"$quantity"
]
}
}
}
])
5. $unwind : 将数组中的元素拆分成单条文档, 参考方法unwind()
db.unwind_clothing.aggregate([
{
$unwind: "$sizes"
}
])
-
注意:
- sizes字段必须存在
- sizes的值不能为null
- sizes不能是一个空数组, 否则这条数据则不会返回(该字段即使不是数组, 也能返回)
-
解决方案, 使用preserveNullAndEmptyArrays参数
db.unwind_clothing.aggregate([ { $unwind: { path: "$sizes", preserveNullAndEmptyArrays: true } } ]) -
-
返回拆出来的文档的数据下标索引
includeArrayIndexdb.unwind_clothing.aggregate([ { $unwind: { path: "$sizes", preserveNullAndEmptyArrays: true, includeArrayIndex: "arrayIndex" } } ])
-
6. count统计的几种方式, 参考方法count()
// 方法一
db.artists.countDocuments();
// 方法二
db.artists.count();
// 方法三
db.artists.aggregate([
{
$group: {
_id: null,
myCount: {
$sum: 1
}
}
},
{
$project: {
_id: 0
}
}
])
// 方法四 passing_scores是别名, 任意什么都可以
db.artists.aggregate(
[
{
$count: "passing_scores"
}
]
)
7. 分组$group, 参考方法group()
1. SELECT date,
Sum(( price * quantity )) AS totalSaleAmount,
Avg(quantity) AS averageQuantity,
max(quantity) AS maxQuantity,
Count(*) AS Count
FROM sales
GROUP BY Date(date)
HAVING totalSaleAmount >= 100
ORDER BY totalSaleAmount DESC
2.db.sales.aggregate(
[
// First Stage
{
$group :
{
_id : "$item",
totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
count:{$sum: 1}, // count的数量
maxQuantity:{$max: "$quantity"},
minQuantity:{$min:"$quantity"},
avgQuantity:{$avg: "$quantity"},
quantityArr:{$push: "$quantity"}
}
},
// Second Stage
{
$match: { "totalSaleAmount": { $gte: 100 } }
},
{
$sort: {totalSaleAmount:-1}
}
]
)
8. 表达式
1. 布尔表达式, 参考代码orAndOperate()
-
$and仅当其所有表达式计算结果为true时，才返回true。 接受任意数量的参数表达式db.people.find({ $and: [ { status: "A" } , { age: 50 } ] }) -
$or当其任何一表达式求值为true时返回true。 接受任意数量的参数表达式。db.people.find({ $or: [ { status: "A" } , { age: 50 } ] }) -
应用场景: 每条数据需要多个条件时, 批量查询出来这批数据( 已知道一批数据的患者ID和患者就诊次, 为了减少数据库交互, 一次性把数据查询出来 )
db.zsyyIBD.find({ $or: [ {info_patientID:'000530204300', visit_id:'1669198463581NA'}, {info_patientID:'ZY010001298384', visit_id:'1669198463126NA'} ] })
2. 比较表达式, 参考代码compare()
-
$eq如果值是相等，则返回true -
$gt如果第一个值大于第二个值，则返回true -
$gte如果第一个值大于或等于第二个值，则返回true。 -
$lt如果第一个值小于第二个值，则返回true。 -
$lte如果第一个值小于或等于第二个值，则返回true。 -
$ne如果值不相等，则返回true。db.sales.aggregate([ { $match: { quantity: { $lt: 10 } } }, { $project: { eqflag: { $eq: [ "$quantity", 5 ] }, ltflag: { $lt: [ "$quantity", 5 ] } } } ])
3. 算术表达式, 参考代码arithmeticOpratee()
$add添加数字以返回总和$divide返回将第一个数除以第二个数的结果。 接受两个参数表达式$mod返回第一个数除以第二个数的余数。 接受两个参数表达式。$multiply返回多个数相乘的结果。 接受任意数量的参数表达式$subtract返回从第一个值减去第二个值的结果。 如果两个值是数字，则返回差值。 如果两个值都是日期，则返回以毫秒为单位的差值
db.sales.aggregate([
{
$project: {
quantity: 1,
addValue: {
$add: [
"$quantity",
5
]
},
divideValue: {
$divide: [
"$quantity",
2
]
},
modValue: {
$mod: [
"$quantity",
2
]
},
multiplyValue: {
$multiply: [
"$quantity",
2
]
},
subtractValue: {
$subtract: [
"$quantity",
2
]
}
}
}
])
4. 字符串表达式, 参考代码strOperate()
-
$concat连接任意数量的字符串db.sales.aggregate([ { $project: { con: { $concat: [ "$item", "-", "$item", "-", "item" ] } } } ]) -
$indexOfCP搜索字符串以查找子串的出现，并返回第一个出现的字符串的索引。 找不到子串，返回-1db.sales.aggregate([ { $project: { item: 1, indexFlag: { $indexOfCP: [ "$item", "z" ] } } } ]) -
$split基于分隔符将字符串拆分为子字符串。 返回一个子串数组。 如果在字符串中未找到定界符，则返回包含原始字符串的数组{ $split: [ "June-15-2013", "-" ] } [ "June", "15", "2013" ] { $split: [ "banana split", "a" ] } [ "b", "n", "n", " split" ] { $split: [ "astronomical", "astro" ] } [ "", "nomical" ] { $split: [ "pea green boat", "owl" ] } [ "pea green boat" ] db.split_test.aggregate([ { $project: { urlArr: { $split: [ "$url", "/" ] } } } ]) -
$strcasecmp执行不区分大小写的字符串比较，并返回：如果两个字符串相等，则返回0，如果第一个字符串大于第二个，则返回1，如果第一个字符串小于第二个，则返回-1db.sales.aggregate([ { $project: { item: 1, compareFlag: { $strcasecmp: [ "$item", "ABc" ] } } } ]) -
$strLenCP返回字符串的长度db.sales.aggregate([ { $project: { item: 1, strLength: { $strLenCP: "$item" } } } ]) -
$substrCP返回字符串的子字符串。 索引（从零开始）开始{ $substrCP: [ "abcde", 1, 2 ] } "bc" { $substrCP: [ "Hello World!", 6, 5 ] } "World" db.sales.aggregate([ { $project: { item: 1, subStr: { $substrCP: [ "$item", 0, 1 ] } } } ]) -
$toLower字符串转小写, $toUpper字符串转大写db.sales.aggregate([ { $project: { item: 1, itemUpper: { $toUpper: "$item" }, itemLower: { $toLower: "$item" } } } ])
5. 数组表达式, 参考代码arrOperate()
-
$arrayElemAt返回指定数组索引处的元素db.arr_elementat.aggregate([ { $project: { favorites: 1, first: { $arrayElemAt: [ "$favorites",0 ] }, outArrayIndex: { $arrayElemAt: [ "$favorites",10 ] }, last: { $arrayElemAt: [ "$favorites",-1 ] } } } ]) -
$concatArrays连接数组并返回db.arr_concat.aggregate([ { $project: {instock:1, ordered:1, items: { $concatArrays: [ "$instock", "$ordered" ] } } } ]) -
$filter选择数组的子集以返回只包含与过滤条件匹配的元素的数组db.arr_filter.aggregate([ { $project: { items: { $filter: { input: "$items", as: "item", cond: { $and: [ { $eq: [ "$$item.item_id", 103 ] }, { $eq: [ "$$item.quantity", 4 ] } ] } } } } } ]) -
$indexOfArray搜索数组以获取指定值的出现，并返回第一个出现的数组索引。 找不到子串，返回-1db.arr_index.aggregate([ { $project: { flag: { $indexOfArray: [ "$items", 2 ] } } } ]) -
$isArray确定操作数是否为数组。 返回布尔值db.arr_index.aggregate([ { $project: { items: 1, flag: { $isArray: "$items" } } } ]) -
$reverseArray返回具有相反顺序的元素的数组db.arr_index.aggregate([ { $project: { items: 1, reverseArray: { $reverseArray: "$items" } } } ]) -
$size返回数组中元素的数量。 接受单个表达式作为参数db.arr_index.aggregate([ { $project: { items: 1, numberOfColors: { $cond: { if: { $isArray: "$items" }, then: { $size: "$items" }, else: "NA"} } } } ] ) -
$slice返回数组的子集db.arr_index.aggregate([ { $project: { items: 1, sliceItems:{$slice: ["$items", 1, 1]} } } ] ) -
$in返回一个布尔值，表示指定的值是否在数组中(数组字段需要存在)db.arr_index.aggregate([ { $project: { items: 1, inItems:{$cond: { if: { $isArray: "$items" }, then: {$in: ["one", "$items"]}, else: false}} } } ] )
6. 日期表达式, 参考代码dateOperate()
-
$year 返回日期作为数字的年份（例如2014）。
-
$dateToString 以格式化的字符串形式返回日期。
db.sales.aggregate( [ { $project: { year: { $year: "$date" }, month: { $month: "$date" }, day: { $dayOfMonth: "$date" }, hour: { $hour: "$date" }, minutes: { $minute: "$date" }, seconds: { $second: "$date" }, milliseconds: { $millisecond: "$date" }, dayOfYear: { $dayOfYear: "$date" }, dayOfWeek: { $dayOfWeek: "$date" }, week: { $week: "$date" }, yearMonthDayUTC: { $dateToString: { format: "%Y-%m-%d", date: "$date" } } } } ] )
7. 条件表达式, 参考代码conditionOperate()
-
$cond三元运算符计算一个表达式，并根据结果返回其他两个表达式之一的值db.sales.aggregate([ { $project: { price: 1, priceLteTen: { $cond: { if: { $lte: ["$price",10] }, then: { $concat: ["$item","lteTen"] }, else: { $concat: ["$item","GtTen"] } } } } } ]) -
$ifNull如果第一个字段不为空则返回第一个表达式的值, 否则判断第二个字段是否为空, 不为空返回第二个字段的值, 否则返回第三个参数db.sales.aggregate([ { $project: { "value": { $ifNull: ["$test", "$test2", "Unspecified"] } } } ]) -
$switch评估一系列的案例表达式。 当它找到一个计算结果为true的表达式时，$switch将执行一个指定的表达式并跳出控制流db.switch_case.aggregate( [ { $project: { "name" : 1, "summary" : { $switch: { branches: [ { case: { $gte : [ { $avg : "$scores" }, 90 ] }, then: "Doing great!" }, { case: { $and : [ { $gte : [ { $avg : "$scores" }, 80 ] }, { $lt : [ { $avg : "$scores" }, 90 ] } ] }, then: "Doing pretty well." }, { case: { $lt : [ { $avg : "$scores" }, 80 ] }, then: "Needs improvement." } ], default: "No scores found." } } } } ] )
9. 总结: 对应的Java方法
package com.hessianhealth.demo;
import com.hessianhealth.demo.entity.PatientInfo;
import org.bson.Document;
import org.bson.types.ObjectId;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Sort;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.*;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.query.Update;
import org.springframework.data.mongodb.core.schema.JsonSchemaObject;
import java.util.*;
import java.util.regex.Pattern;
/**
* @ClassName TestMongoServiceImpl
* @Description
* @Author sunchuiyou
* @Date 2022/12/17 21:08
* @Version 1.0
**/
@SpringBootTest
public class TestMongoServiceImpl {
@Autowired
private MongoTemplate mongoTemplate;
/**
* @Author sunchuiyou
* @Description mongodb ddl
* @Date 19:58 2022/12/19
* @Param *
* @Return void
**/
@Test
public void mongodbDDL() {
// 创建集合
HashMap<String, String> map = new HashMap<>();
map.put("userName", "scy");
mongoTemplate.insert(map, "user");
// 添加字段
Update update = new Update();
update.set("date", new Date());
mongoTemplate.updateMulti(new Query(), update, "user");
// 删除字段
Update update2 = new Update();
update2.unset("date");
mongoTemplate.updateMulti(new Query(), update2, "user");
// 删除集合
mongoTemplate.dropCollection("user");
}
/**
* @Author sunchuiyou
* @Description mongodb普通查询
* @Date 19:58 2022/12/19
* @Param *
* @Return void
**/
@Test
public void mongodbFind() {
Query query = new Query();
query.addCriteria(Criteria.where("_id").gt(1));
query.fields().include("first_name", "last_name");
query.with(Sort.by(Sort.Order.desc("_id")));
query.skip(0).limit(100);
List<Map> list = mongoTemplate.find(query, Map.class, "artists");
System.out.println(list);
// 比较运算符
Criteria.where("_id").lt(3);
// 逻辑运算符
new Criteria().andOperator(Criteria.where("_id").gte(3), Criteria.where("first_name").is("Edvard"));
// 元素查询运算符
Criteria.where("_id").exists(true);
Criteria.where("_id").type(JsonSchemaObject.Type.NUMBER);
// 评估运算符 $expr
MatchOperation match = Aggregation.match(EvaluationOperators.EvaluationOperatorFactory.Expr.valueOf(
ComparisonOperators.Gt.valueOf("spent").greaterThan("budget")));
// 评估运算符 $mod
MatchOperation match1 = Aggregation.match(Criteria.where("_id").mod(2, 0));
Aggregation aggregation = Aggregation.newAggregation(match, match1);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "monthlyBudget", Map.class);
System.out.println(aggregate.getMappedResults());
// 模糊查询
Criteria criteria = new Criteria();
Pattern pattern = Pattern.compile(".*?" + "value" + ".*", Pattern.CASE_INSENSITIVE);
criteria.and("").regex(pattern);
// 数组查询 $all 不用考虑顺序
Query query1 = new Query(Criteria.where("favorites").all("pie", "apples"));
List<Map> list1 = mongoTemplate.find(query1, Map.class, "arr_elementat");
System.out.println(list1);
// 数组查询 $elemMatch
Query query2 = new Query(Criteria.where("dim_cm").elemMatch(new Criteria().gt(22).lt(30)));
List<Map> list2 = mongoTemplate.find(query2, Map.class, "inventory");
System.out.println(list2);
// 数组查询 $size
Criteria.where("dim_cm").size(2);
mongoTemplate.remove(new Query(Criteria.where("_id").in()), "");
}
/**
* @Author sunchuiyou
* @Description 更新操作
* @Date 14:56 2022/12/22
* @Param *
* @Return void
**/
@Test
public void updateOperate() {
// $inc
Update update = new Update().inc("quantity", 2);
Query query = new Query(Criteria.where("sku").is("abc123"));
mongoTemplate.updateMulti(query, update, "products");
// $min
new Update().min("lowScore", 150);
// $max
new Update().max("lowScore", 150);
// $mul
new Update().multiply("lowScore", 100);
// $rename
new Update().rename("lowScore", "lowScoreNew");
// $set
new Update().set("lowScore2", 90);
// $unset
new Update().unset("lowScore2");
// $addToSet
new Update().addToSet("tags", 1);
// $pop
new Update().pop("tags", Update.Position.FIRST);
// $pull
Update update1 = new Update().pull("vegetables", "carrots");
mongoTemplate.updateMulti(new Query(), update1, "stores");
// $pull $gt
Update update2 = new Update().pull("votes", Query.query(new Criteria().gt(6)));
mongoTemplate.updateMulti(new Query(), update2, "profiles");
// $pull 删除内嵌文档
Update update3 = new Update().pull("results", Query.query(Criteria.where("answers").elemMatch(new Criteria().andOperator(Criteria.where("q").is(2), Criteria.where("a").gte(8)))));
mongoTemplate.updateMulti(new Query(), update3, "survey2");
}
/**
* @Author sunchuiyou
* @Description 查询数组中即包含A又包含B的数据
* @Date 21:20 2022/12/17
* @Param *
* @Return void
**/
@Test
public void testArrayFind() {
Query query = new Query();
ArrayList<String> strings = new ArrayList<>();
strings.add("游泳");
strings.add("唱歌");
query.addCriteria(Criteria.where("hobby").all(strings));
List<Map> list = mongoTemplate.find(query, Map.class, "test");
System.out.println(list);
}
/**
* @Author sunchuiyou
* @Description 添加一个字段
* @Date 21:56 2022/12/17
* @Param *
* @Return void
**/
@Test
public void addField() {
Update update = new Update();
update.set("date", new Date());
mongoTemplate.updateMulti(new Query(), update, "test");
}
/**
* @Author sunchuiyou
* @Description 删除一个字段
* @Date 21:56 2022/12/17
* @Param *
* @Return void
**/
@Test
public void dropField() {
Update update = new Update();
update.unset("date");
mongoTemplate.updateMulti(new Query(), update, "test");
}
/**
* @Author sunchuiyou
* @Description $or和$and的使用
* @Date 22:23 2022/12/17
* @Param *
* @Return void
**/
@Test
public void orAndOperate() {
// 模拟一批数据
List<PatientInfo> list = new ArrayList<>();
PatientInfo patientInfo = new PatientInfo("000530204300", "1669198463581NA");
PatientInfo patientInfo2 = new PatientInfo("ZY010001298384", "1669198463126NA");
list.add(patientInfo);
list.add(patientInfo2);
// 构造$or数组
List<Criteria> orCriteriaList = new ArrayList<>();
list.forEach(obj -> {
Criteria criteria = new Criteria();
criteria.andOperator(Criteria.where("info_patientID").is(obj.getPatientId()),
Criteria.where("visit_id").is(obj.getVisitId()));
orCriteriaList.add(criteria);
});
// 将$or数组拼接
Criteria criteria = new Criteria();
Criteria criteria1 = criteria.orOperator(orCriteriaList);
List<Map> list1 = mongoTemplate.find(new Query(criteria1), Map.class, "zsyyIBD");
System.out.println(list1);
}
@Test
public void match() {
MatchOperation matchOperation = Aggregation.match(Criteria.where("price").lt(10));
Aggregation aggregation = Aggregation.newAggregation(matchOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description $project操作
* @Date 14:56 2022/12/22
* @Param *
* @Return void
**/
@Test
public void project() {
// 将expression中的数组符号[]全部替换成{}
ProjectionOperation operation = Aggregation.project("item").
// 方式一
andExpression("{$multiply:{\"$price\",\"$quantity\"}}").as("total")
// 方式二
.and("price").multiply("quantity").as("total2");
// 方式三
Aggregation.project("item").and(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("total");
Aggregation aggregation = Aggregation.newAggregation(operation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description unwind操作
* @Date 14:56 2022/12/22
* @Param *
* @Return void
**/
@Test
public void unwind() {
UnwindOperation unwindOperation = Aggregation.unwind("sizes");
Aggregation aggregation = Aggregation.newAggregation(unwindOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "unwind_clothing", Map.class);
System.out.println(aggregate.getMappedResults());
// 将sizes不存在的或者为空为null的也返回
UnwindOperation unwindOperation1 = Aggregation.unwind("sizes", true);
Aggregation aggregation1 = Aggregation.newAggregation(unwindOperation1);
AggregationResults<Map> aggregate1 = mongoTemplate.aggregate(aggregation1, "unwind_clothing", Map.class);
System.out.println(aggregate1.getMappedResults());
// 返回数组下标索引
UnwindOperation unwindOperation2 = Aggregation.unwind("sizes", "arrayIndex", true);
Aggregation aggregation2 = Aggregation.newAggregation(unwindOperation2);
AggregationResults<Map> aggregate2 = mongoTemplate.aggregate(aggregation2, "unwind_clothing", Map.class);
System.out.println(aggregate2.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description count操作
* @Date 14:56 2022/12/22
* @Param *
* @Return void
**/
@Test
public void count() {
Query query = new Query();
long count = mongoTemplate.count(query, "zsyyIBD");
System.out.println(count);
// {$sum:1} = count()
GroupOperation myCount = Aggregation.group().count().as("myCount");
ProjectionOperation project = Aggregation.project("myCount");
Aggregation aggregation1 = Aggregation.newAggregation(myCount, project);
AggregationResults<Map> aggregate1 = mongoTemplate.aggregate(aggregation1, "zsyyIBD", Map.class);
System.out.println(aggregate1.getMappedResults());
Aggregation aggregation = Aggregation.newAggregation(Aggregation.count().as("count"));
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "zsyyIBD", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description group操作
* @Date 14:57 2022/12/22
* @Param *
* @Return void
**/
@Test
public void group() {
GroupOperation groupOperation = Aggregation.group("item").
// ArithmeticOperators
sum(ArithmeticOperators.Multiply.valueOf("price").multiplyBy("quantity")).as("totalSaleAmount")
.max("quantity").as("maxQuantity")
.push("quantity").as("quantityArr");
MatchOperation matchOperation = Aggregation.match(Criteria.where("totalSaleAmount").gte(100));
SortOperation sortOperation = Aggregation.sort(Sort.Direction.DESC, "totalSaleAmount");
Aggregation aggregation = Aggregation.newAggregation(groupOperation, matchOperation, sortOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description 比较
* @Date 14:57 2022/12/22
* @Param *
* @Return void
**/
@Test
public void compare() {
MatchOperation matchOperation = Aggregation.match(Criteria.where("quantity").lt(10));
// 方式一
ProjectionOperation projectionOperation = Aggregation.project().andExpression("{$eq:{\"$quantity\",5}}").as("eqFlag").andExpression("{$lt:{\"$quantity\",5}}").as("leFlag");
// 方式二
ProjectionOperation projectionOperation1 = Aggregation.project("quantity").and(ComparisonOperators.Eq.valueOf("quantity").equalToValue(5)).as("eqFlag")
.and(ComparisonOperators.Lt.valueOf("quantity").lessThanValue(5)).as("leFlag");
Aggregation aggregation = Aggregation.newAggregation(matchOperation, projectionOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description 算术操作
* @Date 14:57 2022/12/22
* @Param *
* @Return void
**/
@Test
public void arithmeticOprate() {
ProjectionOperation projectionOperation = Aggregation.project("quantity").and(ArithmeticOperators.Add.valueOf("quantity").add(5)).as("addValue")
.and(ArithmeticOperators.Divide.valueOf("quantity").divideBy(2)).as("divideValue")
.and(ArithmeticOperators.Mod.valueOf("quantity").mod(2)).as("modValue")
.and(ArithmeticOperators.Multiply.valueOf("quantity").multiplyBy(2)).as("multiplyValue")
.and(ArithmeticOperators.Subtract.valueOf("quantity").subtract(2)).as("subtractValue");
Aggregation aggregation = Aggregation.newAggregation(projectionOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description 字符串操作相关
* @Date 22:35 2022/12/18
* @Param *
* @Return void
**/
@Test
public void strOperate() {
// $concat 连接任意数量的字符串
ProjectionOperation projectionOperation = Aggregation.project("item").and("item").concat("-", "$item", "-", "item").as("con")
// $indexOfCP 搜索字符串以查找子串的出现，并返回第一个出现的字符串的索引。 找不到子串，返回-1
.and(StringOperators.IndexOfCP.valueOf("item").indexOf("z")).as("indexFlag0")
.andExpression("{$indexOfCP:{\"$item\",\"z\"}}").as("indexFlag")
// $strcasecmp 执行不区分大小写的字符串比较，并返回：如果两个字符串相等，则返回0，如果第一个字符串大于第二个，则返回1，如果第一个字符串小于第二个，则返回-1
.andExpression("{$strcasecmp:{\"$item\",\"ABc\"}}").as("compareFlag")
.and("item").strCaseCmp("AbC").as("compareFlag2")
// $strLenCP 返回字符串的长度
.andExpression("{$strLenCP: \"$item\"}").as("strLength")
// $substrCP 返回字符串的子字符串。 索引（从零开始）开始
.and("item").substring(0, 1).as("subStr")
// $toLower字符串转小写, $toUpper字符串转大写
.and("item").toUpper().as("itemUpper")
.and(StringOperators.ToUpper.upper("$item")).as("itemUpper2");
Aggregation aggregation = Aggregation.newAggregation(projectionOperation, projectionOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
// $indexOfCP 搜索字符串以查找子串的出现，并返回第一个出现的字符串的索引。 找不到子串，返回-1
}
/**
* @Author sunchuiyou
* @Description 数组操作
* @Date 14:57 2022/12/22
* @Param *
* @Return void
**/
@Test
public void arrOperate() {
// https://docs.spring.io/spring-data/data-mongodb/docs/current/api/org/springframework/data/mongodb/core/aggregation/package-use.html
// $arrayElemAt
ProjectionOperation projectionOperation2 = Aggregation.project("favorites")
.and(ArrayOperators.ArrayElemAt.arrayOf("favorites").elementAt(0)).as("first")
.and(ArrayOperators.ArrayElemAt.arrayOf("favorites").elementAt(10)).as("outArrayIndex")
.and(ArrayOperators.ArrayElemAt.arrayOf("favorites").elementAt(-1)).as("last");
Aggregation aggregation2 = Aggregation.newAggregation(projectionOperation2);
AggregationResults<Map> aggregate2 = mongoTemplate.aggregate(aggregation2, "arr_elementat", Map.class);
System.out.println(aggregate2.getMappedResults());
// $concat
ProjectionOperation projectionOperation3 = Aggregation.project("instock", "ordered").and(ArrayOperators.ConcatArrays.arrayOf("ordered").concat("instock")).as("items");
Aggregation aggregation3 = Aggregation.newAggregation(projectionOperation3);
AggregationResults<Map> aggregate3 = mongoTemplate.aggregate(aggregation3, "arr_concat", Map.class);
System.out.println(aggregate3.getMappedResults());
// $filter方式一
ProjectionOperation projectionOperation = Aggregation.project("items").and(context -> Document.parse("{\"$filter\": {\"input\": \"$items\",\"as\": \"item\",\"cond\": { $and: [ {$eq: [\"$$item.item_id\", 103]}, {$eq: [\"$$item.quantity\", 4]}] }}}")).as("items");
// $filter方式二
ProjectionOperation projectionOperation1 = Aggregation.project("items").and(
ArrayOperators.Filter.filter("items").as("item").
by(BooleanOperators.And.and(ComparisonOperators.Eq.valueOf("$$item.item_id").equalToValue(103),
ComparisonOperators.Eq.valueOf("$$item.quantity").equalToValue(4)))).as("items");
Aggregation aggregation = Aggregation.newAggregation(projectionOperation1);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "arr_filter", Map.class);
System.out.println(aggregate.getMappedResults());
// $indexOfArray $isArray $reverseArray $size $slice $in
ProjectionOperation projectionOperation4 = Aggregation.project("items").
// $indexOfArray
and(ArrayOperators.IndexOfArray.arrayOf("items").indexOf(2)).as("flag")
// $isArray
.and(ArrayOperators.IsArray.isArray("items")).as("itemsIsArray")
// $reverseArray
.and(ArrayOperators.ReverseArray.reverseArrayOf("items")).as("reverseArray")
// $size
.and(ConditionalOperators.Cond.when(ArrayOperators.IsArray.isArray("items")).thenValueOf(ArrayOperators.Size.lengthOfArray("items")).otherwise("NA")).as("numberOfItems")
// $slice
.and(ArrayOperators.Slice.sliceArrayOf("items").offset(1).itemCount(1)).as("sliceItems")
// $in
.and(ConditionalOperators.Cond.when(ArrayOperators.IsArray.isArray("items")).thenValueOf(ArrayOperators.In.arrayOf("items").containsValue("one")).otherwise(false)).as("inItems")
;
Aggregation aggregation4 = Aggregation.newAggregation(projectionOperation4);
AggregationResults<Map> aggregate4 = mongoTemplate.aggregate(aggregation4, "arr_index", Map.class);
System.out.println(aggregate4.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description 日期操作符
* @Date 14:57 2022/12/22
* @Param *
* @Return void
**/
@Test
public void dateOperate() {
ProjectionOperation projectionOperation = Aggregation.project("date").and(DateOperators.Year.yearOf("date")).as("year")
.and(DateOperators.Month.monthOf("date")).as("month")
.and(DateOperators.DayOfMonth.dayOfMonth("date")).as("day")
.and(DateOperators.Minute.minuteOf("date")).as("minutes")
.and(DateOperators.Second.secondOf("date")).as("seconds")
.and(DateOperators.Millisecond.millisecondOf("date")).as("milliseconds")
.and(DateOperators.DayOfYear.dayOfYear("date")).as("dayOfYear")
.and(DateOperators.DayOfWeek.dayOfWeek("date")).as("dayOfWeek")
.and(DateOperators.Week.weekOf("date")).as("week")
.and(DateOperators.DateToString.dateOf("date").toString("%Y-%m-%d")).as("yearMonthDayUTC");
Aggregation aggregation = Aggregation.newAggregation(projectionOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
}
/**
* @Author sunchuiyou
* @Description 条件操作符
* @Date 14:57 2022/12/22
* @Param *
* @Return void
**/
@Test
public void conditionOperate() {
// $cond
ProjectionOperation projectionOperation = Aggregation.project("price").and(ConditionalOperators.Cond.when(ComparisonOperators.Lte.valueOf("price").lessThanEqualToValue(10))
.thenValueOf(StringOperators.Concat.valueOf("item").concat("lteTen")).otherwiseValueOf(StringOperators.Concat.valueOf("item").concat("gtTen"))).as("priceLteTen")
// $ifNull
.and(ConditionalOperators.IfNull.ifNull("test").then("Unspecified")).as("value");
;
Aggregation aggregation = Aggregation.newAggregation(projectionOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "sales", Map.class);
System.out.println(aggregate.getMappedResults());
AccumulatorOperators.Avg avgScores = AccumulatorOperators.Avg.avgOf("scores");
ProjectionOperation projectionOperation1 = Aggregation.project("scores", "name")
.and(ConditionalOperators.Switch.switchCases(ConditionalOperators.Switch.CaseOperator.when(ComparisonOperators.Gt.valueOf(avgScores).greaterThanValue(90)).then("Doing great!"),
ConditionalOperators.Switch.CaseOperator.when(BooleanOperators.And.and(ComparisonOperators.Gte.valueOf(avgScores).greaterThanEqualToValue(80), ComparisonOperators.Lt.valueOf(avgScores).lessThanValue(90))).then("Doing pretty well."),
ConditionalOperators.Switch.CaseOperator.when(ComparisonOperators.Lt.valueOf(avgScores).lessThanValue(80)).then("Needs improvement.")).defaultTo("No scores found.")).as("summary");
Aggregation aggregation1 = Aggregation.newAggregation(projectionOperation1);
AggregationResults<Map> aggregate1 = mongoTemplate.aggregate(aggregation1, "switch_case", Map.class);
System.out.println(aggregate1.getMappedResults());
}
@Test
public void bucket() {
GroupOperation groupOperation = Aggregation.group("").max(AccumulatorOperators.Max.maxOf("")).as("");
ProjectionOperation projectionOperation = Aggregation.project("").and(ConditionalOperators.Cond.when("").thenValueOf("").otherwiseValueOf("")).as("");
MatchOperation matchOperation = Aggregation.match(Criteria.where("").is(""));
SortOperation sortOperation = Aggregation.sort(Sort.Direction.ASC, "");
SkipOperation skipOperation = Aggregation.skip((long) 100);
LimitOperation limitOperation = Aggregation.limit(10);
Aggregation aggregation = Aggregation.newAggregation(matchOperation, groupOperation, projectionOperation, limitOperation, skipOperation, limitOperation);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation, "artists", Map.class);
System.out.println(aggregate.getMappedResults());
}
@Test
public void delete() {
Query query = new Query();
List<Map> list = mongoTemplate.find(query, Map.class, "testdelete");
ArrayList<ObjectId> objectIds = new ArrayList<>();
list.stream().forEach(map -> {
objectIds.add((ObjectId)map.get("_id"));
});
query.addCriteria(Criteria.where("_id").in(objectIds));
mongoTemplate.remove(query, "testdelete");
}
}
3455
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
