订单示例数据:
{
"total_goods_num" : 3,
"goods" : [
{
"amount" : 1,
"goods_code" : "100016079918",
"price" : { "$numberDecimal" : "5899.59" },
"wait_num" : 1,
"goods_id" : "1006"
},
{
"amount" : 2,
"goods_code" : "100009464821",
"price" : { "$numberDecimal" : "4798.37" },
"wait_num" : 2,
"goods_id" : "1005"
}
],
"created_at" : "2021-07-12 13:24:19",
"bill_no" : "D20210712174",
"userID" : "17333185253",
"updated_at" : "2021-07-12 14:43:58",
"address" : {
"province" : "安徽省",
"city" : "",
"area" : "",
"detail" : ""
},
"bill_status" : "1",
"pay_time" : "2021-07-12 13:23:48",
"total_money" : { "$numberDecimal" : "15496.33000" },
}
想要的查询SQL:
1】查询指定商品编码的数据:
db.bill.aggregate([
{
"$match": {
"bill_no": "D20210623173",
"goods": {
"$elemMatch": {
"goods_code": "100009464821"
}
}
}
},
{
"$sort": {
"bill_no": -1
}
},
{
"$skip": 0
},
{
"$limit": 10
},
{
"$project": {
"_id": 1,
"bill_no": 1,
"cust_name": 1,
"goods": {
"$filter": {
"input": "$goods",
"as": "gs",
"cond": {
"$eq": [
"$gs.goods_code",
"100009464821"
]
}
}
}
}
}
])
2】查询商品的数量在指定区间的数据:
db.bill.aggregate([
{
"$match": {
"bill_no": "D20210623173",
"goods.amount": {
"$gte": 1, "$lte": 5
}
}
},
{
"$sort": {
"bill_no": -1
}
},
{
"$skip": 0
},
{
"$limit": 10
},
{
"$project": {
"_id": 1,
"bill_no": 1,
"cust_name": 1,
"goods": {
"$filter": {
"input": "$goods",
"as": "gs",
"cond": {
"$and": [
{
"$gte": [
"$gs.amount",
1
]
},
{
"$lte": [
"$gs.amount",
5
]
}
]
}
}
}
}
}
])
下面是一个测试代码,说明了在springBoot中怎样去实现上面的查询SQL:
package com.ztest;
import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import org.bson.Document;
import org.springframework.beans.factory.annotation.Autowired;
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.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.*;
@RestController
@RequestMapping("/release")
public class Test {
private static final Logger LOG = LoggerFactory.getLogger(Test.class);
@Autowired
private MongoTemplate mongoTemplate;
@RequestMapping("")
private Object filterSearch() {
//封装对象列表查询条件
List<AggregationOperation> commonOperations = new ArrayList<>();
String sortBy = "bill_no";
int currentPage = 1;
int pageSize = 10;
//设置查询条件
Criteria criteria = Criteria.where("bill_no").is("D20210623173").and("goods").elemMatch( Criteria.where("goods_code").is("100009464821") );
commonOperations.add(Aggregation.match(criteria));
//字段排序
commonOperations.add(Aggregation.sort(new Sort(Sort.Direction.DESC, sortBy)));
//数据截取
commonOperations.add(Aggregation.skip(Long.valueOf((currentPage-1)*pageSize)));
commonOperations.add(Aggregation.limit(pageSize));
AggregationExpression aggregationExpression = new AggregationExpression() {
@Override
public Document toDocument(AggregationOperationContext aggregationOperationContext) {
DBObject filterExpression = new BasicDBObject();
filterExpression.put("input", "$goods");
filterExpression.put("as", "gs");
filterExpression.put("cond", new BasicDBObject("$eq", Arrays.<Object> asList("$gs.goods_code", "100009464821")));
return new Document("$filter", filterExpression);
}
};
ProjectionOperation projectionOperation = Aggregation.project("_id", "bill_no", "cust_name").and(aggregationExpression).as("goods");
//加入挑选字段
commonOperations.add( projectionOperation );
//查询数据
AggregationResults<Map> ans = mongoTemplate.aggregate(Aggregation.newAggregation(commonOperations), "bill", Map.class);
return ans.getMappedResults();
}
}