使用聚合的MongoDB查询

nom7f22z  于 2022-12-18  发布在  Go
关注(0)|答案(1)|浏览(133)

我需要一个mongo数据库查询以及相应的Java代码查询使用聚合框架为下面提到的场景,

Scenario is :

I need to search an array for "seqNo": 4 based on  "aC","aI","aN","aT","bID","pD" from collection A.

Please find the collection mentioned below,

Collection A:

/*1*/
{
    "_id" : ObjectId("6398b904aa0c28d6193bb853"),
    "aC" : "AB",
    "aI" : "ABCD",
    "aN" : "040000000002",
    "aT" : "CA",
    "bID" : NumberLong(0),
    "pD" : "2019-04-19",
    "timeToLive" : ISODate("2019-04-19T00:00:00.000Z"),
    "transactions" : [ 
        {
            
            "amt" : NumberDecimal("-12.340"),
            "seqNo" : 2,
            "valDt" : "2022-10-04"
        }, 
        {
            "amt" : NumberDecimal("-6.800"),     
            "seqNo" : 5,
            "valDt" : "2022-10-04"
        }
    ]
}

/*2*/

{
    "_id" : ObjectId("5d42daa7decf182710080d46"),
    "aC" : "AB",
    "aI" : "ABCD",
    "aN" : "040000000002",
    "aT" : "CA",
    "bID" : NumberLong(1),
    "pD" : "2019-04-19",
    "timeToLive" : ISODate("2019-04-19T00:00:00.000Z"),
    "transactions" : [ 
        {
            "seqNo" : 4,
            "amt" : NumberDecimal("26074.000"),
            "valDt" : "2019-04-19"
        },
        {
            "seqNo" : 3,
            "amt" : NumberDecimal("26074.000"),
            "valDt" : "2019-04-19"
        }
    ]
}

请帮助我的查询,这将是非常有帮助的,如果详细解释。提前感谢。

jm81lzqq

jm81lzqq1#

Java总是比python或javascript更冗长一些,但我是这样做的:因为我的编辑器匹配大括号和方括号,所以我发现将查询构造为JSON,然后将其转换为所需的Document对象管道更容易。

import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;

import com.mongodb.client.ClientSession;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.AggregateIterable;

import org.bson.*;

import java.util.Arrays;

import java.util.ArrayList;
import java.util.List;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.util.Map;
import java.util.HashMap;

import java.math.BigDecimal;

public class agg4 {

    private MongoClient mongoClient;
    private MongoDatabase db;
    private MongoCollection<Document> coll;

    private static class StageHelper {
        private StringBuilder txt;
        public StageHelper() {
            this.txt = new StringBuilder();
        }
        public void add(String expr, Object ... subs) {
            expr.replace("'", "\""); // This is the helpful part.
            if(subs.length > 0) {
                expr = String.format(expr, subs);  // this too
            }
            txt.append(expr);
        }       
        public Document fetch() {
            return Document.parse(txt.toString());
        }
    }
        
    private List<Document> makePipeline() {
        List<Document> pipeline = new ArrayList<Document>();    

        StageHelper s = new StageHelper();

        // It is left as an exercise to add all the other individual fields
        // that need to be matched and properly pass them in, etc.
        String val_aC = "AB";
        String val_aI = "ABCD";
        s.add(" {'$match': {'aC':'%s','aI':'%s'}}", val_aC, val_aI );
        pipeline.add(s.fetch());
        
        s = new StageHelper();
        // This is the meat.  Find seqNo = 4.  Since I suspect seqNo is
        // unique, it does no extra good to filter the array to just an array
        // array of one; changing the array of 1 (if found of course) to a
        // *single* object has more utility, hence use of $arrayElemAt:
        s.add(" {'$project': {'transaction': {'$arrayElemAt': [ ");
        s.add("   {'$filter': {'input': '$transactions', ");
        s.add("                'cond': {'$eq':['$$this.seqNo', 4]} ");
        s.add("   }}, 0]} ");
        s.add(" }}");

        pipeline.add(s.fetch());
        
        s = new StageHelper();
        // If seqNo = 4 could not be found, transaction will be unset so
        // use the following to exclude that doc.
        s.add(" {'$match': {'transaction': {'$exists': true}}} ");
        pipeline.add(s.fetch());
        
        return pipeline;
    }
        
    private void doAgg() {
        try {
            List<Document> pipeline = makePipeline();
            AggregateIterable<Document> output = coll.aggregate(pipeline);
            MongoCursor<Document> iterator = output.iterator();
            while (iterator.hasNext()) {
                    Document doc = iterator.next();
            }
            
        } catch(Exception e) {
            System.out.println("some fail: " + e);
        }
    }

    public static void main(String[] args) {
        (new agg4()).go(args);
    }

    public void go(String[] args) {
        try {
            Map params = new HashMap();

            String host = "mongodb://localhost:37017/?replicaSet=rs0";
            String dbname = "testX";
            String collname = "foo";

            mongoClient = MongoClients.create(host);
            db = mongoClient.getDatabase(dbname);
            coll = db.getCollection(collname, Document.class);

            doAgg();

        } catch(Exception e) {
            System.out.println("epic fail: " + e);
            e.printStackTrace();
        }
    }

如果您使用的是Java 13或更高版本,则String的文本块会使操作更加简单:

String s = """
        {'$project': {'transaction': {'$arrayElemAt': [ 
            {'$filter': {'input': '$transactions',
                         'cond': {'$eq':['$$this.seqNo', 4]}
            }}, 0]}
        }}
    """;
    pipeline.add(s.fetch());

相关问题