系列文章目录
提示:
例如:第一章 mongosh查询
提示:写完文章后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
提示:这里可以添加本文要记录的大概内容:
例如:
提示:增删改查的模板
- createOperation
db.collectionName.insertOne({ a document }) - readOperation
db.collectionName.find({query critera},{projection}) - updateOperation
db.collectionName.updateMany({update filter},{update action}) - deleteOperation
db.collectionName.deleteMany({delete filter})
1.查询
-
查询所有
-
指定相等条件
# {query critera} {<filed1>:<value1>,..}
-
使用QueryOperators指定条件
Query Operators# {query critera} {<filed1>:{<operator1>:<value1>},..}
import static com.mongodb.client.model.Filters.*; @Test public void find() { mongoTemplate.getCollection(Entity.TABLE_NAME).find(eq("name","张三")); }
-
指定And
# {query critera} 既满足field1又满足field2
{<filed1>:<value1>,<filed2>:{<operator1>:<value1>},..}
import static com.mongodb.client.model.Filters.*;
@Test
public void findAnd() {
mongoTemplate.getCollection(KGCSHeadEntity.TABLE_NAME).find(and(in("name",Arrays.asList("张三","李四")),eq("age",19)));
}
- 指定Or
# {query critera} field1或满足field2
{$or:[<field1>:<value1>,<field1>:<value2>]}
import static com.mongodb.client.model.Filters.*;
@Test
public void findAnd() {
mongoTemplate.getCollection(KGCSHeadEntity.TABLE_NAME).find(or(in("name",Arrays.asList("张三","李四")),eq("age",19)));
}
-
既有And又有Or
# {query critera} field1或满足field2 {{<field3>:<value3>}:,$or:[<field1>:<value1>,<field2>:<value2>]}
import static com.mongodb.client.model.Filters.*; @Test public void findAndOr() { mongoTemplate.getCollection(KGCSHeadEntity.TABLE_NAME).find( and(in("name",Arrays.asList("张三","李四")) ,or(eq("age",19),eq("gender","male")))); }
1.1 nested documents
-
匹配内嵌的文档
# 准确匹配,值相等且field顺序也要一致 {<field>:{<filed1>:<value1>,<filed2>:<value1>}}
@Test public void testMatchAnEmbeddedDocument() { Document bson = new Document().append("h", 14).append("w", 21).append("uom", "cm"); Document size = new Document().append("size",bson); List<Inventory> list = template.find(new BasicQuery(size), Inventory.class); System.out.println( JSONObject.toJSONString(list) ); }
-
查询内嵌文档的field 同上
field字段必须使用引号括起来,用.符号标识内嵌关系"field.nestedfield"{ <field1>: { <operator1>: <value1> }, ... }
- 相等
db.inventory.find( { "size.uom": "in" } )
@Test public void testQueryOnNestedField() { Query query = Query.query(new Criteria("size.uom").is("in")); List<Inventory> list = template.find(query, Inventory.class); System.out.println( JSONObject.toJSONString(list) ); }
- queryOperators指定条件
# { <field1>: { <operator1>: <value1> }, ... } db.inventory.find( { "size.h": { $lt: 15 } } )
@Test public void testMatchUserQueryOperator() { Query query = Query.query(new Criteria("size.h").lt(15)); List<Inventory> list = template.find(query, Inventory.class); System.out.println( JSONObject.toJSONString(list) ); }
- and
db.inventory.find( { "size.h": { $lt: 15 }, "size.uom": "in", status: "D" } )
import static org.springframework.data.mongodb.core.query.Criteria.*; @Test public void testSpecifyAndCondition() { Query query = Query.query(new Criteria().andOperator( where("size.h").lt(15), where("size.uom").is("in"), where("status").is("A") )); List<Inventory> list = template.find(query, Inventory.class); System.out.println( JSONObject.toJSONString(list) ); }
1.2 query an array
- 匹配array
# 匹配一个数组,只含两个元素value1,value2,且value顺序一致 db.collectionNames.find({<field1>:[<value1>,<value2>]}) # $all符号能够 校验array中是否含有value1和value2两个值,顺序无所谓 db.collectionNames.find({<field1>:{$all:[<value1>,<value2>]}})
@Test
public void matchAnArray(){
Document document = new Document().append(“tags”, Arrays.asList(“red”,“blank”));
List list1 = template.find(new BasicQuery(document), Inventory.class);
System.out.println(
JSONObject.toJSONString(list1)
);
System.out.println(“+++++++++++++++++++++”);
Query query = Query.query(new Criteria(“tags”).all(Arrays.asList(“red”,“blank”)));
List list2 = template.find(query, Inventory.class);
System.out.println(
JSONObject.toJSONString(list2)
);
}
2. 查询array包含至少包含一个指定值
```shell
# field1中的array包含value1这个元素
db.collectionNames.find({<field1>:<value1>})
# field1中的元素
db.collectionNames.find({<field1>:{<operator1>:<value1>}})
@Test
public void testQueryAnArrayForAnElement(){
Query query = Query.query(new Criteria("tags").is("red"));
List<Inventory> list1 = template.find(query, Inventory.class);
System.out.println(
String.format("数量:%d,结果:%s",list1.size(),JSONObject.toJSONString(list1))
);
System.out.println("+++++++++++++++++++++");
Query query2 = Query.query(new Criteria("dim_cm").gt(25));
List<Inventory> list2 = template.find(query2, Inventory.class);
System.out.println(
String.format("数量:%d,结果:%s",list2.size(),JSONObject.toJSONString(list2))
);
}
- 复杂条件
- 元素符合条件中的一个
# dim_cm中元素有的元素只符合大于15,有的元素只符合小于20,或者有的元素同时满足大于15小于20,也就是说存在部分元素的组合满足了查询条件 db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
@Test public void testQueryCompoundFilter(){ Query query = Query.query(new Criteria().andOperator( Criteria.where("dim_cm").gt(15), Criteria.where("dim_cm").lt(20) )); List<Inventory> list2 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list2.size(),JSONObject.toJSONString(list2)) ); }
- 元素符合所有条件
# 至少有一个元素,满足该条件 db.inventory.find( { dim_cm: {$elemMatch :{ $gt: 15, $lt: 20 } } })
@Test public void testQueryAnArrayElementThatMeetsMultipleCriteria(){ Query query = Query.query( Criteria.where("dim_cm").elemMatch( new Criteria().lt(30).gt(22) ) ); List<Inventory> list2 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list2.size(),JSONObject.toJSONString(list2)) ); }
- 根据数组下标查询
.符号,双引号括起来# 查询满足 数组第2个元素大于25的数据 db.inventory.find( { "dim_cm.1": { $gt: 25 } } )
@Test public void testQueryAnArrayByArrayIndexPosition(){ Query query = Query.query( Criteria.where("dim_cm.1").gt(25) ); List<Inventory> list2 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list2.size(),JSONObject.toJSONString(list2)) ); }
- 根据数组长度查询
# db.inventory.find( { "tags":{$size: 3} } )
@Test public void testQueryAnArrayByArrayLength(){ Query query = Query.query( Criteria.where("tags").size(3) ); List<Inventory> list2 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list2.size(),JSONObject.toJSONString(list2)) ); }
- 元素符合条件中的一个
1.3查询embedded document
- 准确查询 equality match
按整个document来匹配,包括field的顺序db.inventory.find( { "instock": { warehouse: "A", qty: 5 } } )
@Test public void testQueryForADocumentNestedInAnArray() { Query query = Query.query(Criteria.where("instock").is(new Instock("A",5))); List<Inventory> list = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list.size(),JSONObject.toJSONString(list)) ); }
- 查询数组中的document中的field
- 查询数组中的document中的某个field
db.inventory.find( { 'instock.qty': { $lte: 20 } } )
@Test public void testSpecifyAQueryConditionOnAFieldEmbeddedInAnArrayOfDocuments() { List<Inventory> list = template.find( Query.query( Criteria.where("instock.qty").lte(20) ) , Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list.size(),JSONObject.toJSONString(list)) ); }
- 使用数组下标查询嵌套document的field
# instock第0个元素的qty的值少于等于20 db.inventory.find( { 'instock.0.qty': { $lte: 20 } } )
- 多条件查询
-
数组中至少一个内嵌元素同时满足多个条件
# instock中至少有一个元素有qty和warehouse字段,且值为5和A db.inventory.find( { "instock": { $elemMatch: { qty: 5, warehouse: "A" } } } ) # instock中至少有一个元素有qty且大于10小于等于20 db.inventory.find( { "instock": { $elemMatch: { qty: { $gt: 10, $lte: 20 } } } } )
@Test public void testASingleNestedDocumentMeetsMultipleQueryConditionsOnNestedFields() { List<Inventory> list = template.find( Query.query( Criteria.where("instock").elemMatch( Criteria.where("qty").is(5).and("warehouse").is("A") ) ) , Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list.size(),JSONObject.toJSONString(list)) ); //错误的,不好使 List<Inventory> list2 = template.find( Query.query( Criteria.where("instock").elemMatch( Criteria.where("instock.qty").is(5).and("instock.warehouse").is("A") ) ) , Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list2.size(),JSONObject.toJSONString(list2)) ); List<Inventory> list3 = template.find( Query.query( Criteria.where("instock").elemMatch( Criteria.where("qty").gt(10).lt(20) ) ) , Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list3.size(),JSONObject.toJSONString(list3)) ); }
-
元素的组合满足多个条件
# instock中至少有一个元素的qty是5,至少有一个元素的warehouse是A db.inventory.find( { "instock.qty": 5, "instock.warehouse": "A" } )
@Test public void testCombinationOfElementsSatisfiesTheCriteria(){ List<Inventory> list1 = template.find( Query.query( Criteria.where("instock.qty").gt(10).lte(20) ) , Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(),JSONObject.toJSONString(list1)) ); List<Inventory> list2 = template.find( Query.query( Criteria.where("instock.qty").is(5).and("instock.warehouse").is("A") ) , Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list2.size(),JSONObject.toJSONString(list2)) ); }
-
1.4 控制返回的字段
db.collectionName.find({query critera},{projection})
- 控制返回的字段
# 只返回item和status和id db.inventory.find( { status: "A" }, { item: 1, status: 1 } )
@Test public void testReturnSpecifiedFieldsAndThe_idFieldOnly(){ Query query = Query.query( Criteria.where("status").is("A") ); query.fields().include("item","status"); List<Inventory> list1 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- 不返回id字段
# 不返回id,只返回item和status, db.inventory.find( { status: "A" }, { item: 1, status: 1, _id: 0 } )
@Test public void testSuppress_idField(){ Query query = Query.query( Criteria.where("status").is("A") ); query.fields().include("item","status").exclude(Fields.UNDERSCORE_ID); List<Inventory> list1 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- 返回除了个别字段的所有字段
# 不返回status和instock db.inventory.find( { status: "A" }, { status: 0, instock: 0 } )
@Test public void testReturnAllButTheExcludedFields(){ Query query = Query.query( Criteria.where("status").is("A") ); query.fields().exclude("status","instock"); List<Inventory> list1 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- 控制嵌入文档的返回字段
db.inventory.find({ status: "A" },{ item: 1, status: 1, "size.uom": 1 }) { item: 1, status: 1, size: { uom: 1 } }.
@Test public void testReturnSpecificFieldsInEmbeddedDocument(){ Query query = Query.query( Criteria.where("status").is("A") ); query.fields().include("status","instock","size.uom"); List<Inventory> list1 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- 控制嵌入文档的返回字段除了XXX
db.inventory.find({ status: "A" },{ "size.uom": 0 })
@Test public void testProjectionOnEmbeddedDocumensInAnArray(){ Query query = Query.query( Criteria.where("status").is("A") ); query.fields().exclude("size.uom"); List<Inventory> list1 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- 在列表中的嵌入式文档的字段
db.inventory.find( { status: "A" }, { item: 1, status: 1, "instock.qty": 1 } )
@Test public void testProjectionOnEmbeddedDocumensInAnArray(){ Query query = Query.query( Criteria.where("status").is("A") ); query.fields().include("item","status","instock.qty"); List<Inventory> list1 = template.find(query, Inventory.class); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- 返回列表中的特定元素
project operator $elemMatch $slice $db.inventory.find( { status: "A" }, { item: 1, status: 1, instock: { $slice: -1 } } )
@Test
public void testProjectSpecificArrayElementsInTheReturnedArray(){
Query query = Query.query(
Criteria.where(“status”).is(“A”)
);
query.fields().include(“item”,“status”).slice(“instock”,-1);
List list1 = template.find(query, Inventory.class);
System.out.println(
String.format(“数量:%d\n,结果:%s”,list1.size(), JSONObject.toJSONString(list1))
);
}
### 1.5 查询值为null的字段
1. equality状态
```shell
# 查询item值是null 或item字段不存在的数据
db.inventory.find( { item: null } )
@Test
public void testEqualityFilter(){
List<Inventory> list1 = template.find(
Query.query(Criteria.where("item").is(null))
,Inventory.class
);
System.out.println(
String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1))
);
}
- type check
#这个10是BSON value中null的代表,或者{$type:"null"}也可以 # https://www.mongodb.com/docs/v5.0/reference/bson-types/ db.inventory.find( { item : { $type: 10 } } )
@Test public void testTypeCheck(){ List<Inventory> list1 = template.find( Query.query(Criteria.where("item").type(JsonSchemaObject.Type.NULL)) ,Inventory.class ); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }
- existence check
# 查询item字段不存在的数据 db.inventory.find( { item : { $exists: false } } )
@Test public void existenceCheck(){ List<Inventory> list1 = template.find( Query.query(Criteria.where("item").exists(false)) ,Inventory.class ); System.out.println( String.format("数量:%d\n,结果:%s",list1.size(), JSONObject.toJSONString(list1)) ); }