SpringBoot中使用MongoDB的$filter操作符

訂單示例資料：

{ 
    "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();
    }
}